About Using a Table Twice in One Query

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
```
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.

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.