SQL Outer Join Tutorial

Outer joins are a crucial concept in SQL, allowing you to retrieve data from multiple tables even when some of the data is missing. In this tutorial, we’ll explore outer joins using the PizzaDB schema, a simplified database designed for a fictional pizza shop. This tutorial will cover the basics of outer joins, provide syntax examples, and include practical exercises to reinforce your learning.

Introduction to Outer Joins

You can use an SQL outer joint to combine rows from two or more tables based on a related column between them. The main types of outer joins are:

• LEFT JOIN: Returns all rows from the left table and the matched rows from the right table. If no match is found, NULL values are returned for columns from the right table.
• RIGHT JOIN: Returns all rows from the right table and the matched rows from the left table. If no match is found, NULL values are returned for columns from the left table.
• FULL OUTER JOIN: Returns all rows when there is a match in either left or right table. Rows that do not match are returned with NULL values in place of the missing data from the other table.

All of the examples in this tutorial are based on PizzaDB. You can get the script to build the PizzaDB here. In this lesson you’ll be working with several tables, including Shop, Employee, EmployeeHistory, Customer, Coupon, Product, CustomerOrder, and CustomerOrderItem.

SELECT Table1.Column1, Table2.Column2, ...
FROM Table1
   LEFT|RIGHT|FULL OUTER JOIN Table2 
   ON Table1.CommonColumn = Table2.CommonColumn

In this example:

Table1 is called main or left table (we join from left to right).

• Table2 is the table we are joining to. It is called the right table.
• LEFT|RIGHT|FULL OUTER JOIN whether the join is a LEFT, RIGHT or FULL join.
• The ON clause “announces” the join condition. Here we match two columns.

Let’s now look at the overall syntax. Here is the syntax incorporating all the items we have learned so far.

SELECT Table1.Column1, Table2.Column2, AggregateFunction(Table1.Column3)
FROM Table1
   INNER JOIN Table2 ON Table1.CommonColumn = Table2.CommonColumn
   LEFT|RIGHT|FULL OUTER JOIN Table2
   ON Table1.CommonColumn = Table2.CommonColumn
WHERE condition
GROUP BY Table1.Column1
ORDER BY Table1.Column1

Examples of SQL Outer Joins

Let’s look at some practical examples using PizzaDB. This ERD diagram will help you navigate some of the relationships:

Example 1: Left Join

Find all employees and their associated shops, including employees who are not currently assigned to any shop.

/* Answer */
select e.employeeid, e.firstname, e.lastname, s.shopname
from employee e
    left outer join employeehistory eh on e.employeeid = eh.employeeid
    left outer join shop s on eh.shopid = s.shopid;

This query retrieves a list of all employees and their associated shop names. The left outer join ensures that every employee from the employee table is included in the result set, even if they do not have a corresponding entry in the employeehistory or shop tables. For those employees without an associated shop, the shopname will be null.

Example 2: Right Join

Find all shops and their associated employees, including shops that do not have any employees assigned.

select s.shopid, s.shopname, e.employeeid, e.firstname, e.lastname
from shop s
    right outer join employeehistory eh on s.shopid = eh.shopid
    right outer join employee e on eh.employeeid = e.employeeid;

This query lists all shops and their associated employees. The right outer join ensures that every shop from the shop table is included, even if there are no matching employees in the employeehistory or employee tables. If a shop has no associated employees, the employee details will be null.

Example 3: Full OutJoin

Retrieve all customers and their orders, including customers who have not placed any orders and orders that do not have a corresponding customer record.

/* Answer */
select c.customerid, c.lastname, o.customerorderid, o.orderdate
from customer c
    full outer join customerorder o on c.customerid = o.customerid;

This query fetches a list of all customers and their orders. The full outer join ensures that every customer and every order is included in the result set. If a customer has not placed any orders, the order details will be null. Conversely, if an order does not have a corresponding customer, the customer details will be null.

Exercises

Now it’s your turn to practice using outer joins. Try the following exercises.

Exercise 1: left outer join

Write a query to list all products and their associated order items, including products that have never been ordered.

/* Answer */
select p.productid, p.productname, oi.customerorderid, oi.quantity
from product p
    left outer join customerorderitem oi on p.productid = oi.productid;

Exercise 2: full outer join

Write a query to display all employees and their work history, including employees without any work history and work history records without a corresponding employee.

/* Answer */
select e.employeeid, e.firstname, e.lastname, eh.employeehistoryid, eh.startdate, eh.terminationdate
from employee e
    full outer join employeehistory eh on e.employeeid = eh.employeeid;

By practicing these queries, you’ll gain a better understanding of how outer joins work and how to use them effectively in your SQL tasks. Happy querying!