This documentation is archived and is not being maintained.

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

Show: