Outer Joins

Contact Us or call 1-877-932-8228
Outer Joins

Outer Joins

So far, all the joins we have worked with are inner joins, meaning that rows are only returned that have matches in both tables. For example, when doing an inner join between the Employees table and the Orders table, only employees that have matching orders and orders that have matching employees will be returned.

As a point of comparison, let's first look at another inner join.

Code Sample:

SubqueriesJoinsUnions/Demos/OuterJoins-Inner.sql
/*
	Create a report that shows the number of
	employees and customers from each city that has employees in it.
*/

SELECT COUNT(DISTINCT e.EmployeeID) AS numEmployees,
	COUNT(DISTINCT c.CustomerID) AS numCompanies,
	e.City, c.City
FROM Employees e JOIN Customers c ON
	(e.City = c.City)
GROUP BY e.City, c.City
ORDER BY numEmployees DESC;

The above SELECT statement will return the following results:Inner Join Results

Left Joins

A LEFT JOIN (also called a LEFT OUTER JOIN) returns all the records from the first table even if there are no matches in the second table.

Syntax

SELECT table1.column, table2.column
FROM table1
	LEFT [OUTER] JOIN table2 ON (table1.column=table2.column)
WHERE conditions

All rows in table1 will be returned even if they do not have matches in table2.

Code Sample:

SubqueriesJoinsUnions/Demos/OuterJoins-Left.sql
/*
	Create a report that shows the number of
	employees and customers from each city that has employees in it.
*/

SELECT COUNT(DISTINCT e.EmployeeID) AS numEmployees,
	COUNT(DISTINCT c.CustomerID) AS numCompanies,
	e.City, c.City
FROM Employees e LEFT JOIN Customers c ON
	(e.City = c.City)
GROUP BY e.City, c.City
ORDER BY numEmployees DESC;

All records in the Employees table will be counted whether or not there are matching cities in the Customers table. The results are shown below:Join Left Results

Right Joins

A RIGHT JOIN (also called a RIGHT OUTER JOIN) returns all the records from the second table even if there are no matches in the first table.

Syntax

SELECT table1.column, table2.column
FROM table1
 RIGHT [OUTER] JOIN table2 ON (table1.column=table2.column)
WHERE conditions

All rows in table2 will be returned even if they do not have matches in table1.

Code Sample:

SubqueriesJoinsUnions/Demos/OuterJoins-Right.sql
/*
	Create a report that shows the number of
	employees and customers from each city that has customers in it.
*/

SELECT COUNT(DISTINCT e.EmployeeID) AS numEmployees,
	COUNT(DISTINCT c.CustomerID) AS numCompanies,
	e.City, c.City
FROM Employees e RIGHT JOIN Customers c ON
	(e.City = c.City)
GROUP BY e.City, c.City
ORDER BY numEmployees DESC;

All records in the Customers table will be counted whether or not there are matching cities in the Employees table. The results are shown below (not all records shown):Join Right Results

Full Outer Joins

A FULL JOIN (also called a FULL OUTER JOIN) returns all the records from each table even if there are no matches in the joined table.

Full outer joins are not supported in MySQL 5.x and earlier.

Syntax

SELECT table1.column, table2.column
FROM table1
	FULL [OUTER] JOIN table2 ON (table1.column=table2.column)
WHERE conditions

All rows in table1 and table2 will be returned.

Code Sample:

SubqueriesJoinsUnions/Demos/OuterJoins-Full.sql
/*
	Create a report that shows the number of
	employees and customers from each city.
	
	Note that MySQL 5.x does NOT support full outer joins.
*/

SELECT COUNT(DISTINCT e.EmployeeID) AS numEmployees,
	COUNT(DISTINCT c.CustomerID) AS numCompanies,
	e.City, c.City
FROM Employees e FULL JOIN Customers c ON
	(e.City = c.City)
GROUP BY e.City, c.City
ORDER BY numEmployees DESC;

All records in each table will be counted whether or not there are matching cities in the other table. The results are shown below (not all records shown):Join Full Results

Next