Use Table Joins Rather than Multiple Queries

It is generally more efficient to perform table joins rather than using multiple queries when retrieving data from multiple related tables. In the following example, two queries are used to get the product name and the product type name for product #1 (using two queries is wasteful ). The first query gets the name and product_type_id column values from the products table for product #1. The second query then uses that product_type_id to get the name column from the product_types table.

 -- BAD (two separate queries when one would work)  SELECT name, product_type_id   FROM products   WHERE product_id = 1;  NAME PRODUCT_TYPE_ID ------------------------------ --------------- Modern Science 1  SELECT name   FROM product_types   WHERE product_type_id = 1;  NAME ---------- Book 

Rather than using the two queries just shown, you should write one query that uses a join between the products and product_types tables in order to retrieve the same information. The following query uses a join between the products and product_types tables using the product_type_id column:

 -- GOOD (one query with join rather than two queries)  SELECT p.name, pt.name   FROM products p, product_types pt   WHERE p.product_type_id = pt.product_type_id   AND p.product_id = 1;  NAME NAME ------------------------------ ---------- Modern Science Book 

This query results in the same product name and product type name being retrieved as in the first example, but the results are obtained using one query. Executing one query is generally more efficient than executing two.

You should choose the join order in your query so that you join fewer rows to tables later in the join order. For example, say you were joining three related tables named tab1 , tab2 , and tab3 . Also assume tab1 contains 1,000 rows, tab2 100 rows, and tab3 10 rows. You should join tab1 with tab2 first, followed by tab2 and tab3 .

Avoid joining complex views in your queries, because this results in the queries for the views being run first, followed by your actual query. Instead, write your query using the tables rather than the views.