login

Subscribe to SQL Tutorial websiteRSS Feed
 » SQL Tutorial » SQL Subqueries

SQL Subqueries

SQL subquery is a SQL query which is nested in another query. A SQL subquery is usually nested inside a SELECT, INSERT, UPDATE and DELETE statement. A SQL subquery can be used anywhere in a query where an expression is allowed. A SQL subquery is somtimes refered as inner select or inner query. The SQL statement contains a subquery is also called outer select or outer query.

SQL Subqueries

Here is an example of using an SQL subquery as an expression in SELECT statement as MaxDiscount column:

SELECT orderid, 
       orderdate, 
	(SELECT MAX(discount) 
	 FROM order_details 
	 WHERE orderid = orderid) AS maxdiscount
FROM orders

A SQL subquery nested in an outer SELECT statement has several components as follows:

  • A  selected field in a regular SELECT statement to use as a data source for the outer SELECT statement.
  • A regular FROM clause to specify the table where a selected field is chose from.
  • Optional clauses such as WHERE, GROUP BY or HAVING. 

A SELECT statement of a SQL subquery must be enclosed in parenthesises.

A SQL subquery can include one or more subquery. A number of subquery can include in a subquery depends on the implementation of each RDBMS. It is not recommended to have a high number of leve of nesting subquery in a SQL statement. The following SQL query is used to find the total quantity product bought by customers from USA.

SELECT o.productid, p.productname, SUM(o.quantity) quantity
FROM order_details o
INNER JOIN products p ON p.productid = o.productid
WHERE o.orderid IN ( SELECT orderid FROM orders
		    WHERE customerid IN (SELECT customerid 
					FROM customers 
					WHERE country = 'USA' ) )	
					
GROUP BY productid	
ORDER BY quantity DESC

The inner most query returns all the customer number from USA. The query at the next higher level is evaluated with these customer numbers and returns all sale orders numbers from those customers. Finally outer query uses the order numbers to find all product number from order details table. Product name is selected by join order details table with product tables. The total of quantity is calculated by using aggregate function SUM and GROUP BY clause.

Subqueries Examples

Subqueries with Aliases

In SQL statements if the subquery and outer query refers to the same table. In this case we have to use SQL Alias for both outer query and subquery. The following query finds all managers of the company by refering table employees twice in outer query and subquery.

SELECT e1.employeeid, e1.firstname, e1.lastname
FROM employees e1
WHERE e1.employeeid IN (
	SELECT e2.reportsto
	FROM employees e2 )

Subqueries with Comparison Operators

A subquery can be used with comparison operators such as (=, < >, >, > =, <, ! >, ! <, or < =). For example, we can use subquery with comparison operator to find all products which has prices greater than average prices as follows:

SELECT productName
FROM products
WHERE unitprice > (SELECT AVG(unitprice) 
		   FROM products)

Subqueries with UPDATE, DELETE and INSERT statements.

A subquery can nested in the DML statements such as UPDATE, DELETE and INSERT statement. Here is a subquery to update the unit price of all products supplied by vendor number 15.

UPDATE products
SET unitprice = unitprice * 0.5
WHERE productid IN (SELECT productid 
		    FROM suppliers
		    WHERE supplierid = 15)

Subqueries as expression

A subquery can be used as an expression in anywhere where expression is allowed in the SQL statement. Here is an example of using subquery as an expression to list all product average price and difference between product's price and the average price in product category 1 (Beverages).

SELECT productid, productname,
  (SELECT AVG(unitprice) FROM products) average,
   unitprice - (SELECT AVG(unitprice) FROM products)
FROM products
WHERE categoryid = 1

Correlated subquery

Correlated subquery is a special kind of subquery which the subquery uses the values from outerquery in its where clause. The subquery is evaluated for every rows selected by the outerquery.

The following query is a typical example of correlated query for finding products which have price greater than average unit price in its category.

SELECT productid, productname,unitprice
FROM products p1
WHERE unitprice > (SELECT AVG(unitprice) 
                   FROM products 
                   WHERE categoryid = p1.categoryid)