Joins in DB2 SQL

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



Deja un comentario