Using a Table Twice in One Query

Visual Database Tools

Visual Database Tools

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

For more information about creating joins using reflexive relationships, see Creating 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 Creating 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 Creating Table Aliases.

See Also

Structure of Retrieval Queries | Drawing a Reflexive Relationship