Wednesday 26 June 2013

How To Use Subqueries Statement In SQL

sql - subqueries


In previous chapter i have explained How To Use JOIN Statement In SQL

now i am gonna eplain you How To Use Subqueries Statement In SQL


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 theorders 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 SELECTquery 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 Files14
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.

No comments: