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');
» 
IE 7
Firefox 2.0
Comment and Contribute
