NATURAL JOIN
INNER JOINS
SELECT employee_id, last_name,
department_name
FROM
hr.employees JOIN hr.departments
USING (department_id) ;
SELECT l.city, d.department_name
FROM
locations l JOIN departments d USING (location_id)
WHERE location_id = 1400;
SELF JOIN
Sometimes you need to join a table to itself. To find the name of each employee’s manager,
you need to join the EMPLOYEES table to itself, or perform a self-join.
SELECT worker.last_name emp, manager.last_name mgr
FROM
employees worker JOIN employees manager
ON
(worker.manager_id = manager.employee_id);
NONEQUIJOINS
To return the unmatched rows, you can use
an OUTER join. An OUTER join returns all rows that satisfy the join condition and also returns
some or all of those rows from one table for which no rows from the other table satisfy the join
condition.
There are three types of OUTER joins:
•LEFT OUTER
•RIGHT OUTER
•FULL OUTER
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) ;
This query retrieves all the rows in the EMPLOYEES table, which is the left table, even if there
is no match in the DEPARTMENTS table.
SELECT e.last_name, d.department_id, d.department_name
FROM
employees e RIGHT OUTER JOIN departments d
ON
(e.department_id = d.department_id) ;
This query retrieves all the rows in the DEPARTMENTS table, which is the table at the right,
even if there is no match in the EMPLOYEES table.
SELECT e.last_name, d.department_id, d.department_name
FROM
employees e FULL OUTER JOIN departments d
ON
(e.department_id = d.department_id) ;
This query retrieves all rows in the EMPLOYEES table, even if there is no match in the
DEPARTMENTS table. It also retrieves all rows in the DEPARTMENTS table, even if there is no
match in the EMPLOYEES table.
——— CARTESIAN PRODUCT ————
SELECT last_name, department_name
FROM
employees CROSS JOIN departments ;