Types of Joins

SELECT table1.column, table2.column
FROM table1
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON (table1.column_name = table2.column_name)]|
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)]|
[CROSS JOIN table2];

Joins that are compliant with the SQL:1999 standard include the following:

1. Cross joins
• The CROSS JOIN clause produces the cross-product of two tables.
• This is also called a Cartesian product between the two tables.

SELECT last_name, department_name FROM employees CROSS JOIN departments ;


2. Natural joins
• The NATURAL JOIN clause is based on all columns in the two tables that have the same name.
• It selects rows from the two tables that have equal values in all matched columns.
• If the columns having the same names have different data types, an error is returned.
Contoh:
SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations ;
3. USING clause
• If several columns have the same names but the data types do not match, the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an equijoin.
• Use the USING clause to match only one column when more than one column matches.
• Do not use a table name or alias in the referenced columns.
• The NATURAL JOIN and USING clauses are mutually exclusive.

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


4. Full (or two-sided) outer joins
• In SQL:1999, the join of two tables returning only matched rows is called an inner join.
• A join between two tables that returns the results of the inner join as well as the unmatched rows from the left (or right) tables is called a left (or right) outer join.
• A join between two tables that returns the results of an inner join as well as the results of a left and right join is a full outer join.


LEFT OUTER JOIN 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) ;

RIGHT OUTER JOIN 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) ;

FULL OUTER JOIN 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) ;


5. Arbitrary join conditions for outer joins