An SQL join is a query that combines data from two tables using one or more common columns to create a relationship between both tables.
The four main join types produce the following results (T1 is the first table and T2 the second):
INNER JOIN: Rows from T1 and T2 that satisfy the join condition (matched).
LEFT OUTER JOIN: Rows from T1 and T2 which statisfy the join condition (matched) and rows from T1 that do not satisfy the join condition matched with a null row from T2.
RIGHT OUTER JOIN: Rows from T1 and T2 which statisfy the join condition (matched) and rows from T2 that do not satisfy the join condition matched with a null row from T1.
FULL OUTER JOIN: Rows from T1 and T2 which statisfy the join condition (matched); rows from T1 that do not satisfy the join condition matched with a null row from T2; and rows from T2 which do not satisfy the join condition matched with a null row from T1.
Examples
Given the following two tables:
COMPANIES
| COMP_CODE | COMPANY |
| 1 | S.F. SOFTWARE |
| 2 | GARCIA FRUITS |
| 3 | SMITH ALUMINIUM |
| 5 | M.S. IMPRESSIONS |
EMPLOYEES
| EMPL_CODE | COMP_CODE | EMPLOYEE |
| 1 | 2 | JOHN SMITH |
| 2 | 2 | MARY JONES |
| 3 | 4 | RICHARD ROSSI |
| 4 | 5 | STEVE RAMOS |
INNER JOIN
SELECT EMPLOYEE, COMPANY
FROM EMPLOYES INNER JOIN COMPANIES
ON EMPLOYEES.EMPL_CODE = COMPANIES.COMP_CODE
or simply
SELECT EMPLOYEE, COMPANY
FROM EMPLOYES, COMPANIES
WHERE EMPLOYEES.EMPL_CODE = COMPANIES.COMP_CODE
We get the employee’s names and the company in which they work. If the company code of an employee doesn´t exist in the COMPANIES table, no row will be produced for that employee in the result set.
| EMPLOYEE | COMPANY |
| MARY JONES | GARCIA FRUITS |
| STEVE RAMOS | M. S. IMPRESSIONS |
| JOHN SMITH | GARCIA FRUITS |
LEFT JOIN
SELECT EMPLOYEE, COMPANY
FROM EMPLOYEES LEFT JOIN COMPANIES
ON COMPANIES.COMP_CODE = EMPLOYEES.EMPL_CODE
We obtain the employee’s names and the company in which they work. Employees whose company doesn´t exist in the COMPANIES table also appear in the result set, but with a null value in the column belonging to the COMPANIES table.
| EMPLOYEE | COMPANY |
| MARY JONES | GARCIA FRUITS |
| RICHARD ROSSI | (null) |
| STEVE RAMOS | M. S IMPRESSIONS |
| JOHN SMITH | GARCIA FRUITS |
RIGHT JOIN
SELECT EMPLOYEE, COMPANY
FROM EMPLOYESS RIGHT JOIN COMPANIES
ON COMPANIESS.COMP_CODE = EMPLOYESS.EMPL_CODE
We obtain the employee’s names and the company in which they work. Companies without workers in the EMLOYEES table also appear in the result set, but with a null value in the column belonging to the EMPLOYEES table.
| EMPLOYEE | COMPANY |
| (null) | S. F. SOFTWARE |
| JOHN SMITH | GARCIA FRUITS |
| MARY JONES | GARCIA FRUITS |
| (null) | SMITH ALUMINIUM |
| STEVE RAMOS | M.S. IMPRESSIONS |
FULL OUTER JOIN
SELECT EMPLOYEE, COMPANY
FROM EMPLOYEES FULL OUTER JOIN COMPANIES
ON COMPANIES.COMP_CODE = EMPLOYEES.EMP_CODE
We obtain the employee’s names and the company in which they work. Companies without workers in the EMPLOYEES table and employees whose company doesn´t exist in the COMPANIES table also appear in the result set, but with corresponding null values.
| EMPLOYEE | COMPANY |
| MARY JONES | GARCIA FRUITS |
| RICHARD ROSSI | (null) |
| STEVE RAMOS | M.S. IMPRESSIONS |
| JOHN SMITH | GARCIA FRUITS |
| (null) | S. F. SOFTWARE |
| (null) | SMITH ALUMINIUM |