Joins

Joins allow database users to combine data from one table with data from one or more other tables (or views, or
synonyms). Tables are joined two at a time making a new table containing all possible combinations of rows from
the original two tables.

Inner joins: Chooses the join criteria using any column names that happen to match between the two tables. The
example below displays only the employees who are executives as well.
SELECT emp.firstname, exec.surname FROM employees emp, executives exec
WHERE emp.id = exec.id;

Left Outer joins: A problem with the inner join is that only rows that match between tables are returned. The
example below will show all the employees and fill the null data for the executives.
SELECT emp.firstname, exec.surname FROM employees emp left join executives exec
ON emp.id = exec.id;
On oracle
SELECT emp.firstname, exec.surname FROM employees emp, executives exec
where emp.id = exec.id(+);

Right Outer join: A problem with the inner join is that only rows that match between tables are returned. The
example below will show all the executives and fill the null data for the employees.
SELECT emp.firstname, exec.surname FROM employees emp right join executives exec
ON emp.id = exec.id;

On oracle
SELECT emp.firstname, exec.surname FROM employees emp, executives exec
WHERE emp.id(+) = exec.id;
Full outer join: To cause SQL to create both sides of the join
SELECT emp.firstname, exec.surname FROM employees emp full join executives exec
ON emp.id = exec.id;
On oracle
SELECT emp.firstname, exec.surname FROM employees emp, executives exec
WHERE emp.id = exec.id (+)
UNION
SELECT emp.firstname, exec.surname FROM employees emp, executives exec
WHERE emp.id(+) = exec.id