Using Joins - Oracle SQL quick ref 2

NATURAL JOIN is a type of join that relies on both tables involved on the join to have a common column name. For example if an employee table has a foreign key called department_id and the departments table has a primary key called department_id, then it is possible to do a natural join because in both sides there is a column with the same name.

SELECT employee_id, last_name, department_id, department_name
FROM employees NATURAL JOIN departments;

Here another example, where both the locations and departments table, have location_id:

SELECT department_id, department_name, location_id, city
FROM departments NATURAL JOIN locations
WHERE department_id IN (20, 50);

There is a type of join, that uses the USING keyword. This Join, requires that the column that is used for the join is explicitly mentioned in the USING clause.

SELECT employee_id, last_name, location_id, department_id
FROM employees JOIN departments
USING (department_id);

When we use the USING keyword, we are allowed to use aliases if we want but the only limitation is that the WHERE clause cannot use aliases. For example, WHERE d.location_id... would not be valid.

SELECT l.city, d.department_name
FROM locations l JOIN departments d
USING (location_id)
WHERE location_id = 1400;


SELECT first_name, d.department_name, d.manager_id
FROM employees e JOIN departments d
USING (department_id)
WHERE department_id = 50;


The ON keyword is the most commonly used way of doing joins in this type of does not mandate that the columns in both sides have the same name, also there are no limitations in the usage of aliases in the WHERE clause.

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e
JOIN departments d ON (e.department_id = d.department_id);


The JOIN clause has an AND part, which is equivalent to the WHERE clause, so WHERE is not needed but can also be used along side.

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
AND e.manager_id=149;


SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
WHERE e.manager_id=149;

It is possible to have as many JOIN clauses as desired in a query.

SELECT e.last_name, e.job_id, e.department_id, d_depatment_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
WHERE l.city = 'London';

Sometimes its necessary to perform self joins. In this example a non normalized table of employees, has a row to represent that the worker is a manager. So if we wanted to see who is the manager of who, we would have to do something like this.

SELECT worker.last_name emp, manager.last_name mgr
FROM employees worker
JOIN employees manager ON (worker.manager_id = manager.employee_id);

Also in SQL, there are other types of joins called OUTER and INNER  that are useful when we want to widen or narrow the result set. INNER JOIN  will only return rows, if both tables in the join have associated data in the other side of the join. OUTER JOIN is not that strict, they will still return rows even if they have no associated data in the other side of the join. While there is one type of INNER JOIN, the OUTER JOIN cam come in 3 flavours(LEFT,RIGHT, FULL).

The LEFT OUTER JOIN will include all employees and their department name even if they don't have an associated department.

SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);

The RIGHT OUTER JOIN will include all the departments that are associated with employees, but also all the departments that are not associated with employees at all.

SELECT e.last_name, e.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);


The FULL OUTER JOIN will return all the departments even if they are not associated with employees and also all the employees even if they are not associated with departments.

SELECT e.last_name, e.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);


The INNER JOIN will return all results that have employees with departments and all departments with employees.

SELECT e.last_name, e.department_id, d.department_name
FROM employees e  INNER JOIN departments d
ON (e.department_id = d.department_id);