Selecting Data from Multiple Tables

Because well designed databases are normalized following certain designing rules, not all of the data you need may be located in the same table. You may therefore need to use more than one table in your query to obtain the desired data. Suppose you need to obtain the Product Name from an order, but the order information is stored in two different tables: the OrderDetails table that contains the Product Id and the Product table that contains the name . The statement below demonstrates one way to retrieve information from multiple tables. The SQL Server statements in this section can be accessed from \Ch07\Samples02.sql in the sample files.

 SELECT Sales.SalesOrderDetail.SalesOrderId,        Sales.SalesOrderDetail.SalesOrderDetailId,        Production.Product.ProductId,        Production.Product.Name,        Sales.SalesOrderDetail.UnitPrice,        Sales.SalesOrderDetail.UnitPriceDiscount,        Sales.SalesOrderDetail.LineTotal FROM   Sales.SalesOrderDetail,        Production.Product WHERE  Sales.SalesOrderDetail.ProductID=Production.Product.ProductId   AND  Sales.SalesOrderDetail.SalesOrderId=43659 

Using Aliases

When using multiple tables on the FROM clause, you should specify the schema, table name, and field name in the SELECT clause to avoid using ambiguous names that may be found in several tables. The schema, table name, and column name were specified in the statement above to obtain the Product Id (Production.Product.ProductId). By using this notation, you avoid the problem that could arise because the ProductId field is also the name of a field in the Sales.SalesOrderDetails table. If you do not specify the entire name, you should at least specify enough information to avoid ambiguity, that is: Product.ProductId and SalesOrderDetails.ProductId.

Avoiding ambiguity often makes names very long. While this is not a problem involving network traffic or server processing, it is a problem for those who code the statements. The length of a statement makes it easier to commit syntax errors, and long statements are more complicated to understand. You can use aliases to avoid this problem in both the FROM and SELECT clauses. Aliases allow you to replace long table names by shorter expressions. Below is the same example using aliases in the FROM clause.

 SELECT SAL_SOD.SalesOrderId,        SAL_SOD.SalesOrderDetailId,        PRO_P.ProductId,        PRO_P.Name,        SAL_SOD.UnitPrice,        SAL_SOD.UnitPriceDiscount,        SAL_SOD.LineTotal FROM   Sales.SalesOrderDetail SAL_SOD,        Production.Product PRO_P WHERE  SAL_SOD.ProductID=PRO_P.ProductId   AND  SAL_SOD.SalesOrderId=43659 

Caution 

Be careful when selecting aliases so that they are not confusing. Avoid the use of acronyms with confusing letters or any letters with similar appearance. You should also avoid confusing combinations, such as the use of two consecutive letters in different order on the same query (SD and DS). It would be easy to switch these two consecutive letters , and the switch could produce unexpected results.

Using the INNER JOIN Syntax

The syntax in the previous example is still confusing because you are combining different options on the WHERE clause. SAL_SOD.ProductId=PRO_P.ProductID is used for joining the two tables, and SAL_SOD.SalesOrderID=43659 applies a filter to the result set. ANSI introduced the INNER JOIN operator to the SQL Server standard in the SQL-92 specification. The INNER JOIN operator allows you to specify in the FROM clause how two tables are to be joined, as shown in the following example.

 SELECT SAL_SOD.SalesOrderId,        SAL_SOD.SalesOrderDetailId,        PRO_P.ProductId,        PRO_P.name,        SAL_SOD.UnitPrice,        SAL_SOD.UnitPriceDiscount,        SAL_SOD.LineTotal FROM   Sales.SalesOrderDetail SAL_SOD        INNER JOIN Production.Product PRO_P        ON SAL_SOD.ProductID=PRO_P.ProductId WHERE  SAL_SOD.SalesOrderId=43659 

This example demonstrates that the WHERE clause contains only the filter condition and that the JOIN condition is written by using the ON clause instead of the WHERE clause.

Of course, you can use more than one condition to join tables. Suppose that another table is used to store the primary photo of your product for the entire catalog. With the next sentence , you can obtain the Product Id, Product Name, and Modified Date for the primary photo.

 SELECT PR_P.ProductID,PR_P.Name,PR_PPP.[Primary],PR_PPP.ModifiedDate FROM Production.Product PR_P      INNER JOIN Production.ProductProductPhoto PR_PPP      ON PR_P.ProductID=PR_PPP.ProductId and PR_PPP.[Primary]=1 WHERE PR_P.ProductID<100 

Note 

Note that the word Primary is found inside brackets [] because Primary is a reserved word. It could also be used as the name of a field, although this is not recommended. When a reserved word is used as a field or table name, it is placed inside brackets [].

In the statement above, the AND operator is used to find only the primary photo. You could also use the OR operator and different comparison operators, such as >, >=,<, or <=, on JOIN clauses if necessary.

Using More than Two Tables

It is sometimes necessary to use more than two tables. Although you can use as many tables as needed to obtain information, it is best to use the minimum number of tables on your joins to achieve better performance. Queries are more efficient when there are fewer tables on the join, so try not to use more than four to six tables on FROM clauses. The following example uses three tables on the join because the relationship between Production.Product and Production.ProductPhoto is a many-to-many relationship, as shown in the figure below.

 SELECT PR_Product.ProductID, PR_Product.Name,        PR_ProductPhoto.[Primary], PR_ProductPhoto.ModifiedDate,        PR_Photo.LargePhotoFileName FROM   Production.Product PR_Product        INNER JOIN Production.ProductProductPhoto PR_ProductPhoto           INNER JOIN Production.ProductPhoto PR_Photo           ON PR_ProductPhoto.ProductPhotoID=PR_Photo.ProductPhotoID        ON PR_Product.ProductID=PR_ProductPhoto.ProductId WHERE  PR_ProductPhoto.ModifiedDate<='20000101' 

Using LEFT JOIN

Sometimes the relationship between tables is optional, not mandatory. Vendors are typically (but not always) associated with specific products. Assume that you have acquired a new vendor who is not yet associated with any product. If you use the INNER JOIN syntax to join vendor and product information, only vendors with associated products will appear in the results. If you need to obtain information about all of your vendors , even those who are not associated with any product, then you must use the LEFT JOIN syntax as shown in the following example. When using LEFT JOINS , all records from the left table are shown plus only the needed rows from the right table. If a relationship does not exist for a particular row, then all columns on the right table return a NULL value.

 SELECT V.VendorID,V.AccountNumber,V.[Name],        P.ProductID,P.[Name] FROM   Purchasing.Vendor V        LEFT JOIN Purchasing.ProductVendor PV           INNER JOIN Production.Product P           ON PV.ProductID=P.ProductID        ON V.VendorID=PV.VendorID 

As shown on the following page, some vendors contain the NULL value as their Product ID because they do not have any associated products.

If you need to obtain only those vendors without associated products, you could add a WHERE clause to specify that the ProductID value must be NULL, as shown in the following example.

 SELECT V.VendorID,V.AccountNumber,V.[Name],        P.ProductID,P.[Name] FROM   Purchasing.Vendor V        LEFT JOIN Purchasing.ProductVendor PV           INNER JOIN Production.Product P           ON PV.ProductID=P.ProductID        ON V.VendorID=PV.VendorID WHERE  P.ProductID is null 

The figure below displays a database diagram representing the relationship between the Vendor and Product tables.

Note 

Note that a LEFT JOIN was used for the relationship between Purchasing.Vendor and Purchasing.ProductVendor. An INNER JOIN was used for the relationship between Purchasing.ProductVendor and Production.Product because all vendors with or without an associated product were needed. The LEFT JOIN guaranties that all vendors will be included in the results. The INNER JOIN clause will return rows only when a record exists on Purchasing.ProductVendor.

In the result window below, you see that only vendors without associated products are shown.

Using RIGHT JOIN

Not all products are associated with all vendors. Therefore, if you use a sentence like the one below to try to obtain all products, you will miss some rows.

 SELECT P.ProductID,P.[Name],PV.VendorID FROM Purchasing.ProductVendor PV      INNER JOIN Production.Product P      ON PV.ProductID=P.ProductID 

For instance, Product ID 3 (BB Ball Bearing) is not associated with any vendor, and therefore this product does not appear in the results, as shown in the following figure.

To obtain all products, you must use a RIGHT JOIN as shown in the following sentence.

 SELECT p.ProductID,P.[Name],PV.VendorID FROM Purchasing.ProductVendor PV      RIGHT JOIN Production.Product P      ON PV.ProductID=P.ProductID 

The result includes all products, even those that do not have any associated vendor.

In the figure below, you can see that Product ID 3 and Product ID 316 have a NULL value on the VendorID column.

The main difference between a LEFT JOIN and a RIGHT JOIN is the position in your sentence of the table that should return all rows. By using these statements, you can avoid missing data due to an absent relation.

More Info  

A RIGHT JOIN and LEFT JOIN can also be written as RIGHT OUTER JOIN and LEFT OUTER JOIN. Writing OUTER JOINS in this way is the clearestbut also wordiestmethod to use, which is why a shorter syntax was used in the example.

Using FULL JOIN

You can use a LEFT JOIN or RIGHT JOIN to obtain all of the records from either the left or right table, but you may need to obtain all of the records in both tables. In this situation, you can use the FULL JOIN syntax to obtain all records. Suppose that you need to obtain all vendors and all products with or without relations. To do so, you can use the following statement.

 SELECT V.VendorID,V.AccountNumber,V.[Name],        P.ProductID,P.[Name] FROM   Purchasing.Vendor V        FULL JOIN Purchasing.ProductVendor PV            FULL JOIN Production.Product P            ON PV.ProductID=P.ProductID        ON V.VendorID=PV.VendorID 

Note 

Note that two FULL JOIN clauses are used because the relationship between Vendors and Products is made between the Purchasing.ProductVendor table. It is therefore necessary to use three tables on your join.

The following figure demonstrates how all records of both tables (Purchasing.Vendor and Production.Product) are contained in the results of the query, with NULL values displayed when necessary.