SQL Tutorial

Subqueries, Joins and 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.

/*
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.

-- 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.

-- 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:

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.

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.

-- Find the EmployeeID and OrderID for all orders

SELECT EmployeeID, OrderID
FROM Orders;

But this isn’t 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.

-- 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:

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.

-- 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.

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.

/*
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:

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.

/*
 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:

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.

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.

/*
 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:

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.

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.

/*
 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):

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.

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.

/*
 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):

Unions

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

/*
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.

All material in this Subqueries, Joins and Unions is copyright 2010 Webucator. The purpose of this website is to help you learn SQL on your own and use of the website implies your agreement to our SQL Tutorial Terms of Service.