facebook google plus twitter
Webucator's Free SQL Tutorial

Lesson: Subqueries, Joins and Unions

Welcome to our free SQL tutorial. This tutorial is based on Webucator's Introduction to SQL Training course.

Often the data you need will be stored in multiple tables. In this lesson, you'll learn to create reports from two or more tables based on data in one of those tables or even in a separate table altogether.

Lesson Goals

  • Learn to write queries with subqueries.
  • Learn to select columns from multiple tables with joins.
  • Learn to select records from multiple tables with unions.

Subqueries

Subqueries are queries embedded in queries. They are used to retrieve data from one table based on data in another table. They generally are used when tables have some kind of relationship. For example, in the Northwind database, the Orders table has a CustomerID field, which references a customer in the Customers table. Retrieving the CustomerID for a specific order is pretty straightforward.

Code Sample:

SubqueriesJoinsUnions/Demos/Subquery-SelectCustomerID.sql
/*
Find the CustomerID of the company that placed order 10290.
*/

SELECT CustomerID
FROM Orders
WHERE OrderID = 10290;

This will return COMMI, which is very likely meaningless to the people reading the report. The next query uses a subquery to return a meaningful result.

Code Sample:

SubqueriesJoinsUnions/Demos/Subquery-SelectCompanyName.sql
-- Find the name of the company that placed order 10290.

SELECT CompanyName
FROM Customers
WHERE CustomerID = (SELECT CustomerID
			FROM Orders
			WHERE OrderID = 10290);

The above code returns Comércio Mineiro, which is a lot more useful than COMMI.

The subquery can contain any valid SELECT statement, but it must return a single column with the expected number of results. For example, if the subquery returns only one result, then the main query can check for equality, inequality, greater than, less than, etc. On the other hand, if the subquery returns more than one record, the main query must check to see if a field value is (or is NOT) IN the set of values returned.

Code Sample:

SubqueriesJoinsUnions/Demos/Subquery-IN.sql
-- Find the Companies that placed orders in 1997

/******************************
Both of the queries below will work in SQL Server

Oracle
******************************/
SELECT CompanyName
FROM Customers
WHERE CustomerID IN (SELECT CustomerID
			FROM Orders
			WHERE OrderDate BETWEEN '1-Jan-1997' AND '31-Dec-1997');

/******************************
MySQL
******************************/
SELECT CompanyName
FROM Customers
WHERE CustomerID IN (SELECT CustomerID
			FROM Orders
			WHERE OrderDate BETWEEN '1997-01-01' AND '1997-12-31');

The above SELECT statement will return the following results:Subquery In Results

Subqueries

Duration: 20 to 30 minutes.

In this exercise, you will practice writing subqueries.

  1. Create a report that shows the product name and supplier id for all products supplied by Exotic Liquids, Grandma Kelly's Homestead, and Tokyo Traders.
    • You will need to escape the apostrophe in "Grandma Kelly's Homestead." To do so, place another apostrophe in front of it. For example,
      SELECT *
      FROM Suppliers
      WHERE CompanyName='Grandma Kelly''s Homestead';
  2. Create a report that shows all products by name that are in the Seafood category.
  3. Create a report that shows all companies by name that sell products in CategoryID 8.
  4. Create a report that shows all companies by name that sell products in the Seafood category.

Solution:

SubqueriesJoinsUnions/Solutions/Subqueries.sql
SELECT ProductName, SupplierID
FROM Products
WHERE SupplierID IN (SELECT SupplierID
			FROM Suppliers
			WHERE CompanyName IN 
				('Exotic Liquids', 'Grandma Kelly''s Homestead', 'Tokyo Traders'));

SELECT ProductName
FROM Products
WHERE CategoryID = (SELECT CategoryID
			FROM Categories
			WHERE CategoryName = 'Seafood');

SELECT CompanyName
FROM Suppliers
WHERE SupplierID IN (SELECT SupplierID
			FROM Products
			WHERE CategoryID = 8); 

SELECT CompanyName
FROM Suppliers
WHERE SupplierID IN (SELECT SupplierID
			FROM Products
			WHERE CategoryID = (SELECT CategoryID
						FROM Categories
						WHERE CategoryName = 'Seafood'));

Joins

How can we find out

  • Which products are provided by which suppliers?
  • Which customers placed which orders?
  • Which customers are buying which products?

Such reports require data from multiple tables. Enter joins.

Syntax

SELECT table1.column, table2.column
FROM table1 JOIN table2
	ON (table1.column=table2.column)
WHERE conditions

Creating a report that returns the employee id and order id from the Orders table is not difficult.

Code Sample:

SubqueriesJoinsUnions/Demos/Joins-NoJoin.sql
-- Find the EmployeeID and OrderID for all orders

SELECT EmployeeID, OrderID
FROM Orders;

But this is not very useful as we cannot tell who the employee is that got this order. The next sample shows how we can use a join to make the report more useful.

Code Sample:

SubqueriesJoinsUnions/Demos/Joins-EmployeeOrders.sql
-- Create a report showing employee orders.

SELECT Employees.EmployeeID, Employees.FirstName,
	Employees.LastName, Orders.OrderID, Orders.OrderDate
FROM Employees JOIN Orders ON
	(Employees.EmployeeID = Orders.EmployeeID)
ORDER BY Orders.OrderDate;

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

Table names are used as prefixes of the column names to identify the table in which to find the column. Although this is only required when the column name exists in both tables, it is always a good idea to include the prefixes as it makes the code more efficient and easier to read.

Table Aliases

Using full table names as prefixes can make SQL queries unnecessarily wordy. Table aliases can make the code a little more concise. The example below, which is identical in functionality to the query above, illustrates the use of table aliases.

An alias can be called whatever you want. Though typically it's the first letter(s) of the table name, it can be whatever makes sense to you as the Developer. For example, the alias for a table called Courses can be c, or crs, or debbie, etc.

Code Sample:

SubqueriesJoinsUnions/Demos/Joins-Aliases.sql
-- Create a report showing employee orders using Aliases.

SELECT e.EmployeeID, e.FirstName, e.LastName,
	o.OrderID, o.OrderDate
FROM Employees e JOIN Orders o ON
	(e.EmployeeID = o.EmployeeID)
ORDER BY o.OrderDate;

Multi-table Joins

Multi-table joins can get very complex and may also take a long time to process, but the syntax is relatively straightforward.

Syntax

SELECT table1.column, table2.column, table3.column
FROM table1
	JOIN table2	ON (table1.column=table2.column)
	JOIN table3	ON (table2.column=table3.column)
WHERE conditions

Note that, to join with a table, that table must be in the FROM clause or must already be joined with the table in the FROM clause. Consider the following.

SELECT table1.column, table2.column, table3.column
FROM table1
	JOIN table3 ON (table2.column=table3.column)
	JOIN table2 ON (table1.column=table2.column)
WHERE conditions

The above code would break because it attempts to join table3 with table2 before table2 has been joined with table1.

Code Sample:

SubqueriesJoinsUnions/Demos/Joins-MultiTable.sql
/*
Create a report showing the Order ID, the name of the company that placed the order,
and the first and last name of the associated employee.
Only show orders placed after January 1, 1998 that shipped after they were required.
Sort by Company Name.
*/

/******************************
Both of the queries below will work in SQL Server

Oracle
******************************/
SELECT o.OrderID, c.CompanyName, e.FirstName, e.LastName
FROM Orders o
	JOIN Employees e ON (e.EmployeeID = o.EmployeeID)
	JOIN Customers c ON (c.CustomerID = o.CustomerID)
WHERE o.ShippedDate > o.RequiredDate AND o.OrderDate > '1-Jan-1998'
ORDER BY c.CompanyName;

/******************************
MySQL
******************************/
SELECT o.OrderID, c.CompanyName, e.FirstName, e.LastName
FROM Orders o
	JOIN Employees e ON (e.EmployeeID = o.EmployeeID)
	JOIN Customers c ON (c.CustomerID = o.CustomerID)
WHERE o.ShippedDate > o.RequiredDate AND o.OrderDate > '1998-01-01'
ORDER BY c.CompanyName;

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

Using Joins

Duration: 25 to 40 minutes.

In this exercise, you will practice using joins.

  1. Create a report that shows the order ids and the associated employee names for orders that shipped after the required date. It should return the following. There should be 37 rows returned.Order IDs and Employee Name Report
  2. Create a report that shows the total quantity of products (from the Order_Details table) ordered. Only show records for products for which the quantity ordered is fewer than 200. The report should return the following 5 rows.Quantity Report
  3. Create a report that shows the total number of orders by Customer since December 31, 1996. The report should only return rows for which the NumOrders is greater than 15. The report should return the following 5 rows.NumOrders Greater Than 15 Report
  4. Create a report that shows the company name, order id, and total price of all products of which Northwind has sold more than $10,000 worth. There is no need for a GROUP BY clause in this report.Company Name and Order ID and Price Report

Solution:

SubqueriesJoinsUnions/Solutions/Joins.sql
SELECT e.FirstName, e.LastName, o.OrderID
FROM Employees e JOIN Orders o ON
	(e.EmployeeID = o.EmployeeID)
WHERE o.RequiredDate < o.ShippedDate
ORDER BY e.LastName, e.FirstName;

SELECT p.ProductName, SUM(od.Quantity) AS TotalUnits
FROM Order_Details od JOIN Products p ON
	(p.ProductID = od.ProductID)
GROUP BY p.ProductName
HAVING SUM(Quantity) < 200;

/******************************
For the third problem, both of the solutions below will work in SQL Server

Oracle Solution
******************************/
SELECT c.CompanyName, COUNT(o.OrderID) AS NumOrders
FROM Customers c JOIN Orders o ON
	(c.CustomerID = o.CustomerID)
WHERE OrderDate > '31-Dec-1996'
GROUP BY c.CompanyName
HAVING COUNT(o.OrderID) > 15
ORDER BY NumOrders DESC;

/******************************
MySQL
******************************/
SELECT c.CompanyName, COUNT(o.OrderID) AS NumOrders
FROM Customers c JOIN Orders o ON
	(c.CustomerID = o.CustomerID)
WHERE OrderDate > '1996-12-31'
GROUP BY c.CompanyName
HAVING COUNT(o.OrderID) > 15
ORDER BY NumOrders DESC;

SELECT c.CompanyName, o.OrderID,
	od.UnitPrice * od.Quantity * (1-od.Discount) AS TotalPrice
FROM Order_Details od
	JOIN Orders o ON (o.OrderID = od.OrderID)
	JOIN Customers c ON (c.CustomerID = o.CustomerID)
WHERE od.UnitPrice * od.Quantity * (1-od.Discount) > 10000
ORDER BY TotalPrice DESC;

/*
	SQL Server users will replace Order_Details with "Order Details"
*/

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

Unions

Unions are used to retrieve records from multiple tables or to get multiple record sets from a single table.

Code Sample:

SubqueriesJoinsUnions/Demos/Unions.sql
/*
Get the phone numbers of all shippers, customers, and suppliers
*/

SELECT CompanyName, Phone
FROM Shippers
	UNION
SELECT CompanyName, Phone
FROM Customers
	UNION
SELECT CompanyName, Phone
FROM Suppliers
ORDER BY CompanyName;

This query will return the company name and phone number of all shippers, customers and suppliers.

UNION ALL

By default, all duplicates are removed in UNIONs. To include duplicates, use UNION ALL in place of UNION.

UNION Rules

  • Each query must return the same number of columns.
  • The columns must be in the same order.
  • Column datatypes must be compatible.
  • In Oracle, you can only ORDER BY columns that have the same name in every SELECT clause in the UNION.

Working with Unions

Duration: 10 to 20 minutes.

In this exercise, you will practice using UNION.

  1. Create a report showing the contact name and phone numbers for all employees, customers, and suppliers.

Solution:

SubqueriesJoinsUnions/Solutions/Unions.sql
/******************************
SQL Server Solution
******************************/
SELECT FirstName + ' ' + LastName AS Contact, HomePhone As Phone
FROM Employees
	UNION
SELECT ContactName, Phone
FROM Customers
	UNION
SELECT ContactName, Phone
FROM Suppliers
ORDER BY Contact;

/******************************
Oracle Solution
******************************/
SELECT FirstName || ' ' || LastName AS Contact, HomePhone As Phone
FROM Employees
	UNION
SELECT ContactName, Phone
FROM Customers
	UNION
SELECT ContactName, Phone
FROM Suppliers
ORDER BY Contact;

/******************************
MySQL Solution
******************************/
SELECT CONCAT(FirstName, ' ', LastName) AS Contact, HomePhone As Phone
FROM Employees
	UNION
SELECT ContactName, Phone
FROM Customers
	UNION
SELECT ContactName, Phone
FROM Suppliers
ORDER BY Contact;