Using a Table Twice in One Query

Article
2012-08-13

You can use the same table two (or more) times within a single query. There are several situations in which you do this.

Creating a self-join with a reflexive relationship You can join a table to itself using a reflexive relationship — a relationship in which the referring foreign-key columns and the referred-to primary-key columns are in the same table. For example, suppose the employee table contains an additional column, employee.manager_emp_id, and that a foreign key exists from manager_emp_id to employee.emp_id. Within each row of the employee table, the manager_emp_id column indicates the employee's boss. More precisely, it indicates the employee's boss's emp_id.

By joining the table to itself using this reflexive relationship, you can establish a result set in which each row contains a boss's name and the name of one of that boss's employees. The resulting SQL might look like this:
```
SELECT 
    boss.lname, 
    boss.fname, 
    employee.lname, 
    employee.fname
FROM 
    employee
        INNER JOIN 
        employee boss 
        ON employee.manager_emp_id 
        =  boss.emp_id
```
For more information about creating joins using reflexive relationships, see How to: Create Self-Joins Automatically.
Creating a self-join without a reflexive relationship You can join a table to itself without using a reflexive relationship. For example, you can establish a result set in which each row describes an employee and a potential mentor for that employee. (A potential mentor is an employee with a higher job level.) The resulting SQL might look like this:
```
SELECT 
    employee.fname, 
    employee.lname, 
    mentor.fname, 
    mentor.lname 
FROM 
    employee 
        INNER JOIN 
        employee mentor 
        ON employee.job_lvl 
        <  mentor.job_lvl
```
Notice that the join uses a condition other than equality. For more information about joining tables using conditions other than equality, see Join Comparison Operators.

For more information about creating self-joins using unrelated columns, see How to: Create Self-Joins Manually.

Using a table twice without a self-join Even without a self-join, you can use the same table twice (or more) in a query. For example, you can establish a result set containing the other books by the author or authors of your favorite book. In this case, you use the titleauthors table twice — once to find the authors of your favorite book (Is Anger the Enemy?), and once to find the other books by those authors. The resulting SQL might look like this:

SELECT 
    other_title.title
FROM 
    titles favorite_title 
        INNER JOIN 
        titleauthor favorite_titleauthor 
        ON favorite_title.title_id 
        =  favorite_titleauthor.title_id 
            INNER JOIN 
            authors 
            ON favorite_titleauthor.au_id 
            =  authors.au_id 
                INNER JOIN 
                titleauthor other_titleauthor 
                ON authors.au_id 
                =  other_titleauthor.au_id 
                    INNER JOIN 
                    titles other_title 
                    ON other_titleauthor.title_id 
                    =  other_title.title_id 
WHERE 
    favorite_title.title 
    = 'Is Anger the Enemy?' 
  AND 
    favorite_title.title 
    <> other_title.title

Note

To distinguish between the multiple uses of any one table, the preceding query uses the following aliases: favorite_title, favorite_titleauthor, other_titleauthor, and other_title. For more information about aliases, see How to: Create Table Aliases.

Share via

Using a Table Twice in One Query

See Also

Concepts

Other Resources

Additional resources