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);