Lesson 1: Combining Data from Multiple Tables | Microsoft SQL Server 7 Data Warehousing Technical Support Training Kit

A join is an operation that allows you to query two or more tables to produce a single result set that incorporates rows and columns from each table. You join tables based on columns in each table that contain common data values. Joins allow you to put back together the data that you break apart during normalization.

There are three types of joins: inner joins, outer joins, and cross joins. Additionally, you can join more than two tables by using a series of joins within a SELECT statement, or you can join a table to itself by using a self-join.

After this lesson, you will be able to:

Combine data from two tables using joins

Describe and use inner joins, outer joins, and cross joins

Estimated lesson time: 60 minutes

Introduction to Joins

You join tables to produce a single result set that incorporates rows and columns from two or more tables. When you join tables, Microsoft SQL Server version 7.0 uses the values in the join column(s) from one table to select rows from another table. Rows with matching join column values are combined into new rows, which make up the result set.

For example, in a properly normalized database, only a product identifier will be stored in an orders table. If you need to retrieve a list of orders that includes product descriptions, you will join the orders table and the products table on the product identifier. The product description and the other columns from the product table may then be used in the select list of the query. In this case, the join performs a lookup function to retrieve the product description for each order.

The partial syntax for joining tables in a SELECT statement is as follows:

SELECT {column_name} [, n] FROM {table_or_view_name}  [  [ INNER | {{LEFT | RIGHT | FULL} [OUTER] }]  JOIN  table_or_view_name ON search_conditions]  ] [ n]

Selects Specific Columns from Multiple Tables

A join allows you to select columns from multiple tables by expanding on the FROM clause of the SELECT statement. Two additional keywords, JOIN and ON, are included in the FROM clause:

The JOIN keyword and its options specify which tables are to be joined and how to join them.

The ON keyword specifies the columns that the tables have in common.

Queries Two or More Tables to Produce a Result Set

A join allows you to query two or more tables to produce a single result set. When you implement joins, consider the following facts and guidelines:

Most join conditions are based on the primary key of one table and the foreign key of another table.

If a table has a composite primary key, you must reference the entire key in the ON clause when you join tables.

Use columns common to the specified tables to join the tables. These columns should have the same or similar data types, but they do not have to have the same names.

Qualify each column name using the table_name.column_name format if the column names are the same.

Try to limit the number of tables in a join, because queries that join a large number of tables are slower than queries that join fewer tables.

Using Inner Joins

Inner joins combine tables by comparing values in columns that are common to both tables. SQL Server returns only rows that match the join conditions.

When to Use Inner Joins

Use inner joins to obtain information from two separate tables and combine that information into one result set. When you use inner joins, consider the following facts and guidelines:

Inner joins are the SQL Server default. You can abbreviate the INNER JOIN clause to JOIN.

Specify the columns that you want to display in your result set by including the column names in the select list.

Include a WHERE clause to restrict the rows that are returned in the result set.

Do not use a null value as a join condition, because null values do not evaluate equally with one another.

SQL Server does not guarantee an order in the result set unless one is specified with an ORDER BY clause.

You can use multiple JOIN clauses and join more than two tables in the same query.

NOTE
Some of the examples in this appendix use the joindb database a database created specifically for teaching the various types of joins. You will find a script for creating the joindb database in C:\SQLDW\Exercise\AppD\joindb.SQL.

Example 1

This example returns the buyer_name, buyer_id, and qty values for the buyers who purchased products. Buyers who purchased no products are not included in the result set. Buyers who bought more than one product are listed for each purchase. Figure D.1 shows how rows from the buyers and sales tables are combined to produce the result set in this example.

The buyer_id column from either table can be specified in the select list.

USE joindb SELECT buyer_name, sales.buyer_id, qty FROM buyers INNER JOIN sales ON buyers.buyer_id = sales.buyer_id

click to view at full size

Figure D.1 Using inner joins

Result

buyer_name                buyer_id    qty ------------------------- ----------- ----------- Adam Barr                 1           15 Adam Barr                 1            5 Erin O'Melia              4           37 Eva Corets                3           11 Erin O'Melia              4           1003  (5 row(s) affected)

Example 2

This example returns the names of products and the companies that supply the products. Products without listed suppliers, and suppliers without current products, are not included in the result set.

USE Northwind SELECT ProductName, CompanyName FROM Products INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID

Result

ProductName                     CompanyName ------------------------------------    ------------------------------- Chai                            Exotic Liquids Chang                           Exotic Liquids Aniseed Syrup                   Exotic Liquids Chef Anton's Cajun Seasoning    New Orleans Cajun Delights Lakkalikööri                    Karkki Oy Original Frankfurter grüne Soße     Plutzer Lebensmittelgroßmärkte AG  (77 row(s) affected)

Example 3

This example returns the customer name and order date for each order placed after 1/1/95. The WHERE clause is used to restrict the rows that are returned in the result set. The DISTINCT option of the SELECT clause removes any rows from the result set that are complete duplicates of another row. In this example, the DISTINCT option ensures that only one row is returned per customer, per day, even if the customer placed several orders on the same day.

USE Northwind SELECT DISTINCT CompanyName, OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID WHERE OrderDate > '1/1/95'

Result

CompanyName                      OrderDate -------------------------------- --------------------------- Alfreds Futterkiste              1997-08-25 00:00:00.000 Alfreds Futterkiste              1997-10-03 00:00:00.000 Alfreds Futterkiste              1997-10-13 00:00:00.000 Alfreds Futterkiste              1998-01-15 00:00:00.000 Wolski  Zajazd                   1998-04-03 00:00:00.000 Wolski  Zajazd                   1998-04-23 00:00:00.000  (823 row(s) affected)

Example 4

This example returns the title number and the member number of the borrower of each book that is currently on loan in the library database. Both the copy and loan tables have a composite primary key consisting of the isbn and copy_no columns. When joining these tables, you must specify both columns in the join condition, because together they uniquely identify a particular copy of a book. The AND operator is used to specify multiple columns in the join condition.

USE library SELECT copy.title_no, loan.member_no FROM copy INNER JOIN loan ON copy.isbn = loan.isbn AND copy.copy_no = loan.copy_no WHERE copy.on_loan = 'Y'

Result

title_no    member_no ----------- --------- 1           338 1           364 1           3588 1           3614 50          9490 50          9529  (2000 row(s) affected)

TIP
In version 7.0 of SQL Server, it is still possible to create joins without specifying the JOIN and ON clauses by listing the tables in a comma-separated list in the FROM clause and specifying the join condition in the WHERE clause. However, you should use the JOIN and ON clauses because they are standard ANSI SQL-92 syntax and because they separate join conditions from other search conditions and make queries easier to read.

Exercise 1: Using an Inner Join

In this exercise, you will create a mailing list of library members that includes the members full names and complete address information. The C:\SQLDW\Exercise\AppD\eqjoin.SQL file contains a completed script for this exercise.

To write a SELECT statement that uses an inner join to create a mailing list

Open SQL Server Query Analyzer.

Make sure that library is the current database.

Write and execute a query on the member and adult tables that returns the firstname, middleinitial, lastname, street, city, state, and zip columns. Concatenate the firstname, middleinitial, and lastname columns into one string and alias the column as [name].

USE library SELECT firstname + ' ' + middleinitial + ' ' + lastname AS [name], street, city, state, zip FROM member INNER JOIN adult ON member.member_no = adult.member_no

Using Outer Joins

Like inner joins, outer joins return combined rows that match the join condition. However, in addition to the rows that match the join condition, left and right outer joins return unmatched rows from one of the tables, and full outer joins return unmatched rows from both tables. Columns from the source table of the unmatched rows contain data, but columns from the other table contain NULL values.

When to Use Left or Right Outer Joins

Use left or right outer joins when you require a complete list of data from one of the joined tables for example, if you generate a query of product sales by joining a products table and a sales table. An inner join will return rows only for products that were sold. If you use a left outer join and specify the products table as the left table, rows will be returned for every product. Sales columns will contain data for products that were sold and NULL for products that were not sold.

When you use left or right outer joins, consider the following facts and guidelines:

Use a left outer join to display all rows from the first-named table (the table on the left of the JOIN clause).

Use a right outer join to display all rows from the second-named table (the table on the right of the JOIN clause).

Do not use a null value as a join condition, because null values do not evaluate equally with one another.

You can abbreviate the LEFT OUTER JOIN clause or the RIGHT OUTER JOIN clause as LEFT JOIN or RIGHT JOIN, respectively.

Example 1

This example returns the buyer_name, buyer_id, and qty values for all buyers and their purchases. Notice that the buyers who purchased no products are listed in the result set, but NULL values appear in the buyer_id and qty columns, which are columns from the right-hand table (sales). Figure D.2 shows how the rows from the buyers table and the sales table are combined to produce the result set in this example.

USE joindb SELECT buyer_name, sales.buyer_id, qty FROM buyers LEFT OUTER JOIN sales ON buyers.buyer_id = sales.buyer_id

click to view at full size

Figure D.2 Using outer joins

Result

buyer_name              buyer_id    qty ------------------------- ----------- ----------- Adam Barr                 1           15 Adam Barr                 1           5 Sean Chai                 NULL        NULL Eva Corets                3           11 Erin O'Melia              4           37 Erin O'Melia              4           1003  (6 row(s) affected)

To change this example query to yield the same result with a RIGHT OUTER JOIN clause, reverse the order of the tables in the FROM clause and use the RIGHT OUTER JOIN clause as follows:

USE joindb SELECT buyer_name, sales.buyer_id, qty FROM sales RIGHT OUTER JOIN buyers ON buyers.buyer_id = sales.buyer_id

Example 2

This example displays the customer name and order date for each order in the Northwind database. By using a left outer join, even customers who have not placed orders are returned in the result set. NULL is returned in the OrderDate column for customers who have not placed an order.

USE Northwind SELECT CompanyName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

Result

CompanyName                           OrderDate ---------------------------------------- --------------------------- Vins et alcools Chevalier              1996-07-04 00:00:00.000 Toms Spezialitäten                       1996-07-05 00:00:00.000 Hanari Carnes                         1996-07-08 00:00:00.000 FISSA Fabrica Inter. Salchichas S.A.     NULL Paris spécialités            NULL  (832 row(s) affected)

NOTE
The *= and =* syntax used for outer joins in previous versions of SQL Server is supported in SQL Server 7.0 for backward compatibility, but you should use the LEFT and RIGHT OUTER JOIN syntax, which is the ANSI SQL-92 standard syntax.

Exercise 2: Using an Outer Join

In this exercise, you will write and execute a query to retrieve the full name and member_no from the member table as well as the isbn and log_date values from the reservation table, for member numbers 250, 341, and 1675. Order the results by member_no. You should show information for these members, even if they have no books on reserve. The C:\SQLDW\Exercise\AppD\out_join.SQL file contains a completed script for this exercise.

To write a SELECT statement that uses an outer join to query member reservations

Write the select list of the query.

Create the name column by concatenating the lastname, firstname, and middleinitial for each member.

Create the date column by converting the log_date to the char(8) data type.

Write a FROM clause that creates an outer join between the member and reservation tables on the member_no columns.

Compose a WHERE clause to retrieve member numbers 250, 341, and 1675 from the member table. Write the ORDER BY clause to sort the result by the member numbers.

Execute the script.

USE library SELECT   me.member_no,   me.lastname + ', ' + me.firstname + ' ' + me.middleinitial AS [name],   re.isbn,   CONVERT(char(8),re.log_date,1) AS [date] FROM member me LEFT OUTER JOIN reservation re ON me.member_no = re.member_no WHERE me.member_no IN (250,341,1675) ORDER BY me.member_no

Which members have no books on reserve?

Using Cross Joins

Cross joins display every combination of all rows in the joined tables. A common column is not required. Because all rows will be combined, the ON clause is not used for cross joins.

When to Use Cross Joins

Cross joins are rarely used on a normalized database, but you can use them to generate test data for a database or lists of all possible combinations for checklists or business templates.

When you use cross joins, SQL Server produces a Cartesian product in which the number of rows in the result set is equal to the number of rows in the first table, multiplied by the number of rows in the second table. For example, if there are 8 rows in one table and 9 rows in the other table, SQL Server returns a total of 72 rows.

Example 1

This example lists all possible combinations of the values in the Buyers.buyer_name and Sales.qty columns. Figure D.3 shows how the rows from the Buyers table and the Sales table are combined to produce the result set in this example.

The use of a cross join displays all possible row combinations between these two tables. The Buyers table has 4 rows and the Sales table has 5 rows; therefore, the result set contains 20 rows.

USE joindb SELECT buyer_name, qty FROM buyers CROSS JOIN sales

click to view at full size

Figure D.3 Using cross joins

Result

buyer_name                qty ------------------------- ----------- Adam Barr                 15 Adam Barr                 5 Adam Barr                 37 Erin O'Melia              11 Erin O'Melia              1003  (20 row(s) affected)

Example 2

This example displays a cross join between the Shippers and Suppliers tables that is useful for listing all the possible ways that suppliers can ship their products.

The use of a cross join displays all possible row combinations between these two tables. The Shippers table has 3 rows and the Suppliers table has 29 rows; therefore, the result set contains 87 rows.

USE Northwind SELECT Suppliers.CompanyName, Shippers.CompanyName FROM Suppliers CROSS JOIN Shippers

Result

CompanyName                              CompanyName ---------------------------------------- -------------------------------- Aux joyeux ecclésiastiques               Speedy Express Bigfoot Breweries                        Speedy Express Cooperativa de Quesos 'Las Cabras'       Speedy Express Tokyo Traders                            Federal Shipping Zaanse Snoepfabriek                      Federal Shipping  (87 row(s) affected)

Lesson Summary

Joins match rows from one table with rows in another table on a common column in order to produce a single result set. Inner joins return a row for each matching pair of rows from the two tables. Outer joins return a row for each matching pair of rows from the two tables, and they also return a row for each unmatched row from one of the tables. Cross joins do not use a common column to match rows; they return a row for every combination of rows from the two tables.