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