SQL - Subquery

What is Subquery

A subquery is a SELECT statement within another SQL statement. The SQL statement can be SELECT, WHERE clause, FROM clause, JOIN, INSERT, UPDATE, DELETE, SET, DO, or another subquery. The query that contains the subquery is normally called outer query and the subquery itself is called inner query.

Using Subquery to Return A Single Value (Known as Single-Value Subquery or Scalar Subquery)

Practice #1-1: Use subquery in WHERE clause with an aggregate function.

Select Max(ShippedDate) From orders

Select OrderID, CustomerID

From Orders

Where ShippedDate = '1998-05-06 00:00:00'

--An aggregate may not appear in the WHERE clause unless

--it is in a subquery contained in a HAVING clause or a select list,

--and the column being aggregated is an outer reference.

Select OrderID, CustomerID

From Orders

Where ShippedDate = (Select max(ShippedDate) From Orders);