Oracle Joins

1. INNER JOINS

1.1. NATURAL JOIN (no column qualifier)

SELECT location_id, city, department_name

FROM locations NATURAL JOIN departments;

1.2. JOIN ... USING

SELECT location_id, city, department_name

FROM locations

JOIN departments USING (location_id)

JOIN countries USING (country_id);

1.3. JOIN ... ON

SELECT region_name, country_name, city

FROM regions r

JOIN countries c ON r.region_id = c.region_id

JOIN locations l ON c.country_id = l.country_id

WHERE country = 'US';

1.4. CARTESIAN JOINS

SELECT region_name, country_name

FROM regions, countries

WHERE countries.country_id LIKE 'I%';

or

SELECT region_name, country_name

FROM countries

CROSS JOIN regions

WHERE countries.country_id LIKE 'I%';

2. OUTER JOINS

2.1. LEFT, RIGHT or Oracle (+) OUTER JOINs

Enter (+) beside the column name of the table where there may not be a corresponding row. For example, there are no cities in the CITY column corresponding to COUNTRY_NAME Israel. Oracle creates a NULL value in the column with the (+).

COUNTRY_NAME        CITY

----------------------      -------------

Israel                               

India                                Bombay

Russia                               

Germany                         Berlin  

SELECT c.country_name, l.city

FROM countries c, locations l

WHERE c.country_id = l.country_id (+)

AND l.city (+) LIKE 'B%';

or

SELECT c.country_name, l.city

FROM countries RIGHT OUTER JOIN locations l

ON c.country_id = l.country_id

AND l.city (+) LIKE 'B%';

2.2. FULL OUTER JOINS

SELECT e.employee_id, e.last_name, d.department_id, d.department_name

FROM employees e FULL OUTER JOIN departments d

ON e.department_id = d.department_id;

3. SELF JOINS

SELECT e.last_name Employee, m.last_name Manager

FROM employees e, employees m

WHERE m.employee_id = e.manager_id;

or

SELECT e.last_name Employee, m.last_name Manager

FROM employees e INNER JOIN employees m

ON m.employee_id = e.manager_id;