Subqueries

Contact Us or call 1-877-932-8228
Subqueries

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

Next