SQL Joins

The information from one table is not always sufficient, so there must be a reference to a record in another table. To use this data record, the tables must be joined together.

There are different ways to link tables with each other.

• [INNER] JOIN: Returns records that have matching values in both tables.
• LEFT [OUTER] JOIN: Return all records from the left table, and the matched records from the right table.
• RIGHT [OUTER] JOIN: Return all records from the right table, and the matched records from the left table.
• FULL [OUTER] JOIN: Return all records when there is a match in either left or right table.

(Source: http://www.w3schools.com/sql/sql_join.asp)

 

Initial table - Persons

P_ID	LastName	Name	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Hinnavn 2	Hinna

Initial table - Orders

O_ID	OrderNo	P_ID
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

 

INNER JOIN

LastName	Name	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678

The SQL only returns the data records in which the column P_ID (Table Persons) matches the column P_ID (Table Orders).

 

LEFT JOIN

LastName	Name	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678
Svendson	Tove

The SQL provides all data records of the table Persons and joins the data records from Orders in which the column P_ID matches.

 

RIGHT JOIN

LastName	Name	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678
		34764

Inverted to LEFT JOIN: All data records from the right-hand table (Orders) and the data records from the left-hand table (Persons) in which the P_ID column has the same value.

 

FULL JOIN

LastName	Name	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678
Svendson	Tove
		34764

Returns all records from both tables. For the data records in which column P_ID matches, the values of both tables are in one line and are therefore joined together.