Bookmark and Share

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.

Advertise on Tizag.com

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)

:

idcustomerday_of_orderproductquantity
3A+Maintenance2008-08-16 00:00:00.000Hanging 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.

Bookmark and Share




Found Something Wrong in this Lesson?

Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time!

Advertise Here

More Tutorials!
Microsoft Office Tutorials Artist Tutorials