SQL - Subqueries

Subqueries are query statements tucked inside of query statements. Like the order of operations from your high school Algebra class, order of operations also come into play when you start to embed SQL commands inside of other SQL commands (subqueries). Let's take a look at a real world example involving the orders table and figure out how to select only the most recent order(s) in our orders table.

To accomplish this, we are first going to introduce a built-in SQL function, MAX(). This function wraps around a table column and quickly returns the current highest (max) value for the specified column. We are going to use this function to return the current "highest", aka most recent date value in the orders table.

SQL Subquery Preview:

USE mydatabase;

SELECT MAX(day_of_order)
FROM orders

SQL Results:

day_of_order

2008-08-16 00:00:00.000

Now we can throw this query into the WHERE clause of another SELECT query and obtain the results to our little dilemma.

SQL Select Subquery Code:

USE mydatabase;

SELECT *
FROM orders
WHERE day_of_order = (SELECT MAX(day_of_order) FROM orders)

:

id	customer	day_of_order	product	quantity
3	A+Maintenance	2008-08-16 00:00:00.000	Hanging Files	14

This query is a dynamic query as it pulls current information and will change if a new order is placed. Utilizing a subquery we were able to build a dynamic and robust solution for providing us with current order information.

Posted in: SQL