About Combining Tables

A query result can include data from multiple tables. To combine data from tables, you use the Join operation from SQL.

The basic join operation

The Join operation matches rows of one table with rows of another table, based on values in those rows. For example, you can join the table titles with the table publishers. Each row in the result set will describe a title, including information about that titleÆs publisher, as shown in the following illustration:

Types of joins

When you join tables, the type of join that you create affects the rows that appear in the result set. You can create the following types of joins:

Inner join A join that displays only the rows that have a match in both joined tables. (This is the default type of join in the Query Designer.) For example, you can join the titles and publishers tables to create a result set that shows the publisher name for each title. In an inner join, titles for which you do not have publisher information are not included in the result set, nor are publishers with no titles. The resulting SQL for such a join might look like this:
```
SELECT title, pub_name
FROM   titles INNER JOIN publishers
              ON titles.pub_id = publishers.pub_id
```
Note Columns containing NULL do not match any values when you are creating an inner join and are therefore excluded from the result set. Null values do not match other null values.
Outer join A join that includes rows even if they do not have related rows in the joined table. You can create three variations of an outer join to specify the unmatched rows to be included:
- Left outer join All rows from the first-named table (the "left" table, which appears leftmost in the JOIN clause) are included. Unmatched rows in the right table do not appear. For example, the following SQL statement illustrates a left outer join between the titles and publishers tables to include all titles, even those you do not have publisher information for:
```
SELECT titles.title_id, 
       titles.title, 
       publishers.pub_name
FROM titles LEFT OUTER JOIN publishers 
            ON titles.pub_id 
             = publishers.pub_id
```
- Right outer join All rows in the second-named table (the "right" table, which appears rightmost in the JOIN clause) are included. Unmatched rows in the left table are not included. For example, a right outer join between the titles and publishers tables will include all publishers, even those who have no titles in the titles table. The resulting SQL might look like this:
```
SELECT titles.title_id, 
       titles.title, 
       publishers.pub_name
FROM titles RIGHT OUTER JOIN publishers 
            ON titles.pub_id 
             = publishers.pub_id
```
- Full outer join All rows in all joined tables are included, whether they are matched or not. For example, a full outer join between titles and publishers shows all titles and all publishers, even those that have no match in the other table.
```
SELECT titles.title_id, 
       titles.title, 
       publishers.pub_name
FROM titles FULL OUTER JOIN publishers 
            ON titles.pub_id 
             = publishers.pub_id
```
Cross join A join whose result set includes one row for each possible pairing of rows from the two tables. For example, authors CROSS JOIN publishers yields a result set with one row for each possible author/publisher combination. The resulting SQL might look like this:
```
SELECT *
FROM authors CROSS JOIN publishers 
```

Joining columns

The JOIN operator matches rows by comparing values in one table with values in another. You decide which columns from each table should be matched. You have several choices:

Related Columns Typically, you join tables by matching values in columns for which a foreign-key relationship exists. For example, you can join discounts to stores by matching the values of stor_id in the respective tables. The resulting SQL might look like this:
```
SELECT *
FROM discounts INNER JOIN stores 
     ON stores.stor_id = discounts.stor_id
```
Unrelated Columns You can also join tables by matching values in columns for which no foreign-key relationship exists. For example, you can join publishers to authors by matching the values of state in the respective tables. Such a join yields a result set in which each row describes an author-publisher pair located in the same state.
```
SELECT au_lname, 
       au_fname, 
       pub_name, 
       authors.state
FROM   authors INNER JOIN publishers 
               ON authors.state 
                = publishers.state
```

Note also that you use multiple columns to match rows from the joined tables. For example, to find the author-publisher pairs in which the author and publisher are located in the same city, you use a join operation matching the respective state columns and the respective city columns of the two tables. You need to match both city and state because it is possible that different states could have like-named cities (e.g., Springfield, Illinois and Springfield, Massachusetts).

Join comparison operators

The JOIN operator matches rows by comparing values in one table with values in another. You can decide exactly what constitutes a match. Your choices fall into two broad categories:

Match on Equality Typically, you match rows when the respective column values are equal. For example, to create a result set in which each row contains a full description of each publisher (that is, with columns from the publishers table and the pub_info table), you use a join matching rows where the values of pub_id in the respective tables are equal. The resulting SQL might look like this:
```
SELECT *
    FROM publishers INNER JOIN pub_info 
      ON publishers.pub_id 
      =  pub_info.pub_id
```
Other You can match rows using some test other than equality. For example, to find the employees and the jobs for which they are underqualified, you can join employee with jobs, matching rows in which the jobÆs minimum required level exceeds the employeeÆs job level. The resulting SQL might look like this:
```
SELECT
fname, minit, lname,
job_desc, job_lvl, min_lvl
FROM employee INNER JOIN jobs
ON employee.job_lvl
< jobs.min_lvl
```

Joining tables

When combining data from multiple tables, you must decide what tables to use. There are several noteworthy considerations:

Combining Three or More Tables Each JOIN operation combines two tables. However, you can use multiple JOIN operations within one query to assemble data from any number of tables. Because the result of each JOIN operation is effectively a table, you can use that result as an operand in a subsequent join operation. For example, to create a result set in which each row contains a book title, an author, and the percentage of that bookÆs royalties the author receives, you must combine data from three tables: authors, titles, and titleauthor. The resulting SQL might look like this:
```
SELECT 
    title, 
    au_fname, 
    au_lname, 
    royaltyper
FROM 
    authors 
        INNER JOIN 
        titleauthor 
        ON authors.au_id 
        =  titleauthor.au_id
   INNER JOIN
   titles 
         ON titleauthor.title_id 
         =  titles.title_id
```
Using a Table merely to join others You can include a table in a join even if you do not want to include any of that tableÆs columns in a result set. For example, to establish a result set in which each row describes a title-store pair in which that store sells that title, you include columns from two tables: titles, and stores. But you must use a third table, sales, to determine which stores have sold which titles. The resulting SQL might look like this:
```
SELECT title, stor_name 
FROM titles 
         INNER JOIN 
         sales 
         ON titles.title_id = sales.title_id 
             INNER JOIN 
             stores 
             ON 
             sales.stor_id = stores.stor_id
```
Notice that the sales table contributes no columns to the result set.
Using a table twice in one query You can use the same table two (or more) times within a single query.
Using something else in place of a table In place of a table, you can use a query, a view, or a user-defined function that returns a table.