After this lesson, you will be able to:
- Combine data from more than two tables using joins
- Join a table to itself
- Combine multiple result sets into one result set by using the UNION operator
- Create tables by using the SELECT INTO statement
Estimated lesson time: 90 minutes
It is possible to join up to 256 tables in a single query. A table can be joined to many other tables within the same query. The joins can use either the same common column or different common columns.
When you use multiple joins, it is easier to consider each join independently if, for example, Tables A, B, and C are joined. The first join combines Tables A and B to produce a result set; this result set is combined with Table C in the second join to produce the final result set. Columns from any of the tables can be specified in the other clauses of the SELECT statement. If two tables have a column with the same name, qualify each column name using the table_name.column_name format.
NOTE
The query optimizer may produce a plan that processes the tables in a different order, but the result set is the same.
Figure D.4 shows a query joining three tables. The first join is between the buyers table and the sales table, and the join condition uses the common buyer_id column. The second join is between the sales table and the produce table, and the join condition uses the common prod_id column.
Figure D.4 Joining more than two tables
Example 1
This example returns the buyer_name, prod_name, and qty columns from the buyers, sales, and produce tables. The buyer_id column is common to the buyers and sales tables and is used to join these two tables. The prod_id column is common to the sales and produce tables and is used to join the produce table to the result of the join between the buyers and sales tables.
USE joindb SELECT buyer_name, prod_name, qty FROM buyers JOIN sales ON buyers.buyer_id = sales.buyer_id JOIN produce ON sales.prod_id = produce.prod_id |
Result
buyer_name prod_name qty ------------------------- ------------------------- ----------- Erin O'Melia Apples 37 Adam Barr Pears 15 Erin O'Melia Pears 1003 Adam Barr Oranges 5 Eva Corets Peaches 11 (5 row(s) affected) |
Example 2
This example displays information from the Orders and Products tables using the Order Details table as a link. For example, if you want a list of products that are ordered each day, you need information from the Orders and Products tables. An order can consist of many products, and a product can be ordered on many orders. Therefore, the Products and Orders tables have a many-to-many relationship.
To retrieve information from both the Orders and Products tables, you need to use an intermediate table and two joins. Although you do not need to retrieve any columns from the Order Details table, you must include this table in the query to relate the Orders table to the Products table. In this example, the OrderID column is common to the Orders and Order Details tables, and the ProductID column is common to the Order Details and Products tables.
USE Northwind SELECT OrderDate, ProductName FROM Orders O INNER JOIN [Order Details] OD ON O.OrderID = OD.OrderID JOIN Products P ON OD.ProductID = P.ProductID WHERE OrderDate = '6/16/97' |
Result
OrderDate ProductName --------------------------- ---------------------------------------- 1997-06-16 00:00:00.000 Gorgonzola Telino 1997-06-16 00:00:00.000 Lakkalikööri (2 row(s) affected) |
In this exercise, you will write and execute a query on the title, item, and copy tables that returns the isbn, copy_no, on_loan, title, translation, and cover values for rows in the copy table with an ISBN of 1, 500 (five hundred), or 1000 (one thousand). Order the results by the ISBN column. The C:\SQLDW\Exercise\AppD\join.SQL file contains a completed script for this exercise.
USE library SELECT co.isbn, co.copy_no, co.on_loan, ti.title, it.[translation], it.cover FROM copy co JOIN title ti ON co.title_no = ti.title_no JOIN item it ON co.isbn = it.isbn WHERE (co.isbn IN (1, 500, 1000)) ORDER BY co.isbn |
When joining more than two tables, especially if the tables are large, you should analyze the query using the SET SHOWPLAN_TEXT, SET SHOWPLAN_ALL, and SET STATISTICS IO options and the graphical execution plan tools. Small changes to the query can have a significant effect on the number of logical reads that are needed to process the query.
If you refer to columns that are common to two or more tables in the WHERE clause of these queries, the table prefix you choose will affect the performance of the query.
Example
In this example, a list of titles and the names of the members who have borrowed each title is retrieved by joining the title, loanhist, and member tables in the library database. A search condition on the member number is specified in the WHERE clause. The result set is the same whether the member_no column of the member table or the member_no column of the loanhist table is specified in the WHERE clause. However, if you turn on Show stats I/O and Show stats time in the Current Connection Options menu in SQL Server Query Analyzer, you will see that the query that specifies the member_no column of the member table executes about three times faster.
The following is a query specifying the member_no column of the member table in the WHERE clause:
USE library SELECT DISTINCT title, firstname + ' ' + lastname FROM title t JOIN loanhist l ON t.title_no = l.title_no JOIN member m ON m.member_no = l.member_no WHERE m.member_no < 100 |
Executing this query yielded the following Show stats I/O and stats time results:
Table 'title'. Scan count 624, logical reads 1248... Table 'loanhist'. Scan count 99, logical reads 2071... Table 'member'. Scan count 1, logical reads 101... SQL Server Execution Times: CPU time = 160 ms, elapsed time = 157 ms. |
The following is a query specifying the member_no column of the loanhist table in the WHERE clause:
USE library SELECT DISTINCT title, firstname + ' ' + lastname FROM title t JOIN loanhist l ON t.title_no = l.title_no JOIN member m ON m.member_no = l.member_no WHERE l.member_no < 100 |
Executing this query yielded the following Show stats I/O and stats time results:
Table 'title'. Scan count 624, logical reads 1248... Table 'member'. Scan count 624, logical reads 1872... Table 'loanhist'. Scan count 1, logical reads 511... SQL Server Execution Times: CPU time = 561 ms, elapsed time = 565 ms. |
If you want to find rows that have values in common with other rows in the same table, you can join a table to another instance of itself. This process is known as a self-join.
Self-joins are used to represent hierarchies or tree structures. For example, a company employment structure is typically hierarchical. Each employee has a manager who is also an employee. In an employee table, the primary key is the employee ID, and the manager ID column is the foreign key that relates the employee table to itself.
Self-joins are also useful for finding matching data in a table. For example, in the pubs database, you can create a query on the authors table that lists for each author the other authors who live in the same city.
When you use self-joins, consider the following guidelines:
The next three examples develop the same query to show how conditions in the WHERE clause are used to create a meaningful self-join query.
Example 1
This example displays a list of buyers who purchased the same products. The first, third, fourth, fifth, and seventh rows of the result set are simply rows that match themselves. The sixth row mirrors the second row. These rows should be eliminated from the result set. Figure D.5 shows how the rows of the buyer table are self-joined to produce the result set that is developed in Examples 1, 2, and 3.
USE joindb SELECT a.buyer_id AS buyer1, a.prod_id, b.buyer_id AS buyer2 FROM sales a JOIN sales b ON a.prod_id = b.prod_id |
Figure D.5 Joining a table to itself
Result
buyer1 prod_id buyer2 ----------- ----------- ----------- 1 2 1 4 2 1 1 3 1 4 1 4 3 5 3 1 2 4 4 2 4 (7 row(s) affected) |
Example 2
This example displays a list of buyers who all purchased the same products, and it eliminates some of the extra rows that occurred in Example 1. Compare the result sets of Examples 1 and 2. The WHERE clause with the not equal to (<>) operator eliminates rows in the result set that match themselves. However, duplicate rows that are mirror images of one another are still returned in the result set.
USE joindb SELECT a.buyer_id AS buyer1, a.prod_id, b.buyer_id AS buyer2 FROM sales a JOIN sales b ON a.prod_id = b.prod_id WHERE a.buyer_id <> b. buyer_id |
Result
buyer1 prod_id buyer2 ----------- ----------- ----------- 4 2 1 1 2 4 (2 row(s) affected) |
Example 3
This example displays a list of buyers who all purchased the same products. The WHERE clause with the greater than (>) operator eliminates the mirrored row in addition to the self-matched rows that occurred in Example 1.
USE joindb SELECT a.buyer_id AS buyer1, a.prod_id, b.buyer_id AS buyer2 FROM sales a JOIN sales b ON a.prod_id = b.prod_id WHERE a.buyer_id > b. buyer_id |
Result
buyer1 prod_id buyer2 ----------- ----------- ----------- 4 2 1 (1 row(s) affected) |
Example 4
This example lists the names of employees and their managers.
USE Northwind SELECT E.FirstName, E.LastName, M.FirstName, M.LastName FROM Employees E LEFT OUTER JOIN Employees M ON M.EmployeeID = E.ReportsTo |
Result
FirstName LastName FirstName LastName ---------- -------------------- ---------- -------------------- Nancy Davolio Andrew Fuller Andrew Fuller NULL NULL Janet Leverling Andrew Fuller Margaret Peacock Andrew Fuller Steven Buchanan Andrew Fuller Michael Suyama Steven Buchanan Robert King Steven Buchanan Laura Callahan Andrew Fuller Anne Dodsworth Steven Buchanan (9 row(s) affected) |
The UNION operator combines the result of two or more SELECT statements into a single result set. Unlike joins, which combine rows from the base tables into single rows, UNION appends rows from the result sets after each other.
Use the UNION operator when the data that you want to retrieve cannot be accessed with a single query. UNION is often used to recombine tables that have been partitioned. For example, old rows from an orders table can be moved to archive tables that each hold orders from a three-month period. To retrieve a single result set from more than one of these tables, create individual SELECT statements and combine them with the UNION operator.
When you use the UNION operator, consider the following facts and guidelines:
Syntax
select_statement UNION [ALL] select_statement [,...n] |
Example 1
This example combines two result sets. The first result set returns the name, city, and postal code of each employee from the Employees table. The second result set returns the name, city, and postal code of each customer from the Customers table. Notice that the customers were listed before the employees, although the Employees table was referenced first in the statement. This is because ordering is not guaranteed unless you specify an ORDER BY clause.
USE Northwind SELECT FirstName + ' ' + LastName AS [Name], City, PostalCode FROM Employees UNION SELECT CompanyName, City, PostalCode FROM Customers |
Result
Name City PostalCode ---------------------------------------- --------------- ---------- Alfreds Futterkiste Berlin 12209 Ana Trujillo Emparedados y helados México D.F. 05021 Antonio Moreno Taquería México D.F. 05023 Michael Suyama London EC2 7JR Anne Dodsworth London WG2 7LT (100 row(s) affected) |
Example 2
This example adds an ORDER BY clause to the statement from Example 1.
USE Northwind SELECT FirstName + ' ' + LastName AS [Name], City, PostalCode FROM Employees UNION SELECT CompanyName, City, PostalCode FROM Customers ORDER BY [Name] |
Result
Name City PostalCode ---------------------------------------- --------------- ---------- Alfreds Futterkiste Berlin 12209 Ana Trujillo Emparedados y helados México D.F. 05021 Andrew Fuller Tacoma 98401 Wilman Kala Helsinki 21240 Wolski Zajazd Warszawa 01-012 (100 row(s) affected) |
In this exercise, you will write a query to retrieve a single list of members, both adult and juvenile, who have reserved books with ISBN numbers 1, 43, or 288. The list must include the isbn, title, member_no, and name (lastname and firstname) of each member who has the reservation. Additionally, the list should indicate whether the member is an adult or a juvenile. The C:\SQLDW\Exercise\AppD\union.SQL file contains a completed script for this exercise.
USE library SELECT re.isbn, ti.title, me.member_no, me.lastname + ', ' + substring(firstname,1,1) AS [name], 'juvenile' AS age FROM reservation re JOIN item it ON re.isbn = it.isbn JOIN title ti ON it.title_no = ti.title_no JOIN member me ON re.member_no = me.member_no JOIN juvenile ju ON re.member_no = ju.member_no WHERE re.isbn in (1,43, 288) UNION SELECT re.isbn, ti.title, me.member_no, me.lastname + ', ' + substring(firstname,1,1) AS [name], 'adult' AS age FROM reservation re JOIN item it ON re.isbn = it.isbn JOIN title ti ON it.title_no = ti.title_no JOIN member me ON re.member_no = me.member_no JOIN adult ad ON re.member_no = ad.member_no WHERE re.isbn in (1,43,288) ORDER BY re.isbn |
You can place the result set of any query into a new table with the SELECT INTO statement.
Use the SELECT INTO statement to create and populate new tables. You can also use the SELECT INTO statement to create temporary tables and break down complex problems that require a data set from various sources. If you first create a temporary table, the queries that you execute on it may be simpler than those you would execute on multiple tables or databases.
When you use the SELECT INTO statement, consider the following facts and guidelines:
Ensure that the table name that is specified in the SELECT INTO statement is unique. If a table exists with the same name, the SELECT INTO statement fails.
You must create column aliases for calculated columns in the select list. You may use aliases to rename other columns; otherwise, the column name will be used.
Create a local temporary table by preceding the table name with a number sign (#) or create a global temporary table by preceding the table name with a double number sign (##). To decide which to use, consider the facts listed below:
Syntax
The syntax for the SELECT INTO statement is as follows:
SELECT select_list INTO new_table_name FROM table_source [WHERE search_condition] |
Example
This example creates a local temporary table based on a query made on the Products table.
USE Northwind SELECT ProductName, UnitPrice AS Price, (UnitPrice * 0.1) AS Tax INTO #pricetable FROM Products |
No results are returned by the SELECT INTO statement. Query the new table to see its data. For example:
SELECT * FROM #pricetable |
Result
ProductName Price Tax ------------------------------------- ---------------- ---------------- -- Chai 18.0000 1.80000 Chang 19.0000 1.90000 Aniseed Syrup 10.0000 1.00000 Lakkalikööri 18.0000 1.80000 Original Frankfurter grüne Soße 13.0000 1.30000 (77 row(s) affected) |
In this exercise, you will create and populate a temporary table named #overdue by using the INTO clause of the SELECT statement.
USE library SELECT lo.member_no, me.lastname, CONVERT(char(12),lo.out_date,101) AS out_date, CONVERT(char(12),lo.due_date,101) AS date_due, ti.title FROM loan lo JOIN member me ON lo.member_no = me.member_no JOIN title ti ON lo.title_no = ti.title_no WHERE (lo.due_date < GETDATE()) |
USE library SELECT member_no, COUNT(*) AS pastdue FROM #overdue GROUP BY member_no |
You can join up to 256 tables in a single query. When joining more than two tables, each successive join joins another table to the result set produced by the previous join. Self-joins are useful for analyzing the data within a table and for representing hierarchical structures. Result sets from SELECT queries that are column-compatible can be combined into a single result set with the UNION operator. The INTO clause of the SELECT statement allows you to create and populate a new permanent or temporary table with a result set from a query.