The NorthWind Database Tutorial

Arizona State University
W. P. Carey School of Business
CIS 360
The NorthWind Database Tutorial

PART 3 – Subqueries & Joins
In this lesson of the NorthWind SQL tutorial, you will learn...
1. To write queries with subqueries.
2. To select columns from multiple tables with joins.
3. 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 1
/*
Find the CustomerID of the company that placed order 10290.
*/
SELECT CustomerID
FROM Orders
WHERE OrderID = 10290;

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

1

Code Sample 2
-- Find the name of the company that placed order 10290.
SELECT CompanyName
FROM Customers
WHERE CustomerID = (SELECT CustomerID
FROM Orders
WHERE OrderID = 10290);

Code Explanation
The above code returns Comercio 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 3
-- Find the Companies that placed orders in 1997
/******************************
The query below will work in SQL Server
******************************/
SELECT CompanyName
FROM Customers
WHERE CustomerID IN (SELECT CustomerID