dcsimg

Getting ANSI About Joins - Page 2

May 20, 2003

by Jim Czuprynski

JOIN ... USING. When you need to join tables that share more than one column naturally, the JOIN ... USING syntax needs to be used. A NATURAL join between the Employees and Departments tables, for example, could yield unexpected results because the tables share both the DEPARTMENT_ID and MANAGER_ID columns, so the JOIN...USING syntax can be used to alleviate this issue. These queries will display Department and Employee information for all employees hired after December 31, 1999:

Example: JOIN...USING

Traditional Syntax

SELECT
   d.department_id || ' - ' || d.department_name "Department",
   e.employee_id, 
   e.last_name || ', ' || e.first_name "Name",
   e.hire_date
  FROM 
   employees e,
   departments d
 WHERE e.department_id = d.department_id
   AND e.hire_date > TO_DATE('12/31/1999', 'mm/dd/yyyy');;

ANSI SYNTAX

SELECT
   department_id || ' - ' || d.department_name "Department",
   employee_id, 
   e.last_name || ', ' || e.first_name "Name",
   e.hire_date
  FROM  employees e 
  JOIN departments d USING (department_id)
 WHERE e.hire_date > TO_DATE('12/31/1999', 'mm/dd/yyyy');

Again, note that as with the NATURAL JOIN syntax, it's improper to use the table prefix for the columns specified in the JOIN...USING statement (in this case, DEPARTMENT_ID).

JOIN ... ON. When you need to describe exactly how two or more tables should be joined together, the ANSI JOIN...ON syntax is ideal. Notice that these queries are joining three tables together to return employee, job, and job history information for all employees hired after December 31, 1999:

Example: JOIN...ON

Traditional Syntax

ANSI SYNTAX

SELECT
   employee_id, 
   e.last_name || ', ' || e.first_name "Name",
   job_id,
   j.job_title,
   jh.start_date,
   jh.end_date
  FROM
   employees e,
   jobs j,
   job_history jh
WHERE e.job_id = j.job_id
   AND e.job_id = jh.job_id
   AND e.hire_date > TO_DATE('12/31/1999', 'mm/dd/yyyy');
SELECT
   employee_id, 
   e.last_name || ', ' || e.first_name "Name",
   job_id,
   j.job_title,
   jh.start_date,
   jh.end_date
  FROM  employees e 
  JOIN jobs j ON (j.job_id = e.job_id)
  JOIN job_history jh ON (jh.job_id = j.job_id)
 WHERE e.hire_date > TO_DATE('12/31/1999', 'mm/dd/yyyy'); 

Outer Joins. Outer joins are usually needed when all rows of one side of the join equation must be retrieved regardless of how many matches are found between the other side of the equation.

A left outer join is constructed whenever all rows on the left side of the join equation need to be returned regardless of whether or not any rows exist on the right side of the join. The traditional syntax uses a plus sign in parentheses - (+) - to indicate which side of the join may not contain any corresponding rows.

In the following example, the query needs to return a total count of all Employees for all Departments regardless of whether some Departments have no employees:

Example: LEFT OUTER Join

Traditional Syntax

ANSI SYNTAX

SELECT
   d.department_id,
   d.department_name,
   COUNT(e.employee_id) "Employees"
  FROM
   departments d,
   employees e
 WHERE d.department_id = e.department_id (+) 
 GROUP BY d.department_id, d.department_name
 ORDER BY d.department_id, d.department_name;
SELECT
   d.department_id,
   d.department_name,
   COUNT(e.employee_id) "Employees"
  FROM
   departments d LEFT OUTER JOIN employees e 
   ON d.department_id = e.department_id
 GROUP BY d.department_id, d.department_name
 ORDER BY d.department_id, d.department_name;

Likewise, a right outer join is invoked whenever all rows on the right side of the join equation need to be returned regardless of whether or not any rows exist on the left side of the join. Here are examples satisfying the opposite of the prior requirement: Return a count of total Employees in each Department regardless of whether some Employees have been assigned a Department that does not yet exist:

Example: RIGHT OUTER Join

Traditional Syntax

ANSI SYNTAX

SELECT
   d.department_id,
   d.department_name,
   COUNT(e.employee_id) "Employees"
  FROM
   departments d,
   employees e
 WHERE d.department_id (+) = e.department_id
 GROUP BY d.department_id, d.department_name
 ORDER BY d.department_id, d.department_name;
SELECT
   d.department_id,
   d.department_name,
   COUNT(e.employee_id) "Employees"
  FROM
   departments d RIGHT OUTER JOIN employees e 
   ON d.department_id = e.department_id
 GROUP BY d.department_id, d.department_name
 ORDER BY d.department_id, d.department_name;

Finally, there is the full outer join. As its name implies, a full outer join returns results from both sides of the join equation. The only way to perform a full outer join in traditional syntax is via the UNION or UNION ALL operators. However, the ANSI syntax provides the FULL OUTER JOIN to accomplish this instead. As you can see, the FULL OUTER JOIN syntax results in a much more compact query:

Example: FULL OUTER Join

Traditional Syntax

ANSI SYNTAX

   SELECT
      d.department_id,
      d.department_name,
      COUNT(e.employee_id) "Employees"
     FROM
      departments d,
      employees e
    WHERE d.department_id = e.department_id (+) 
    GROUP BY d.department_id, d.department_name
UNION
   SELECT
      d.department_id,
      d.department_name,
      COUNT(e.employee_id) "Employees"
     FROM
      departments d,
      employees e
    WHERE d.department_id (+) = e.department_id
    GROUP BY d.department_id, d.department_name;
SELECT
   d.department_id,
   d.department_name,
   COUNT(e.employee_id) "Employees"
  FROM
   employees e FULL OUTER JOIN departments d 
   ON d.department_id = e.department_id
 GROUP BY d.department_id, d.department_name
 ORDER BY d.department_id, d.department_name;

Self-Joins. The recursive join that results when a set of data is joined back to itself is also known as a self-join. This is typically encountered when a parent-child relationship in a hierarchy needs to be traversed. When using the ANSI syntax to accomplish this, only the JOIN...ON syntax will work, and the keyword INNER can be used for clarity. For example, these queries will return a list of Employees hired after December 31, 1999 and their current Managers:

Example: INNER JOIN

Traditional Syntax

ANSI SYNTAX

SELECT
   m.last_name || ', ' || m.first_name "Manager",
   e.last_name || ', ' || e.first_name "Name",
   e.hire_date
  FROM
   employees e,
   employees m
 WHERE e.manager_id = m.employee_id        
   AND e.hire_date > TO_DATE('12/31/1999', 'mm/dd/yyyy');
SELECT
   m.last_name || ', ' || m.first_name "Manager",
   e.last_name || ', ' || e.first_name "Name",
   e.hire_date
  FROM employees e 
   INNER JOIN employees m
   ON (e.manager_id = m.employee_id)        
 WHERE e.hire_date > TO_DATE('12/31/1999', 'mm/dd/yyyy');







The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers