Description

The SQL Subquery is a query within another query, which is usually embedded within the WHERE or HAVING clauses that contain conditions.

  • It is also known as a nested query or inner query.
  • It is usually embedded within WHERE or HAVING clauses of another SQL query, which is called an outer query.
  • It is mostly used within a SELECT statement, but it is allowed on other statements like INSERT, UPDATE, DELETE, or any other subquery.
  • The data returned by a subquery is used by the outer query as a value or a list of values.
  • The data returned by a subquery can be a single value, a single row, a single column, or a table containing one or more rows of one or more columns.

Subqueries provide an easy and effective way to handle queries that depend on the results from another query.

Subqueries are identical to a normal SELECT query but have some limitations listed below.

  • It must always appear within a parenthesis.
  • It must always return only one column. However, it may return multiple columns if the purpose is to compare rows.
  • If it returns multiple rows, then it must be used within the multiple value operators like IN, NOT IN, etc.,
  • Subqueries cannot be part of a UNION, as it must be a single SELECT statement.

Let's look at the subqueries in detail with examples.

Table Date

Before executing any queries, consider the below data on the tables.

Customers Table Data

customer_id customer_name address city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
3 Dominique Perrier 25, rue Lauriston Paris France 75016
4 Martin Blank Via Monte Bianco 34 Turin Italy 10100
5 Thomas Hardy 89 Chiaroscuro Rd. Portland USA 97219

Orders Table Data

order_id order_date order_value customer_id shipper_id
10250 2014-07-05 1807.5 1 3
10251 2014-07-15 1159 2 2
10252 2014-07-18 2822 3 3
10253 2014-07-19 2575.3 4 2
10254 2014-07-24 1256.25 5 3
10255 2014-07-31 9247.5 1 1
10256 2014-08-01 360 4 2
10257 2014-08-06 3151.65 3 1
10258 2014-08-08 2809 1 3
10259 2014-08-14 228 2 1

SELECT Statement With Subquery

The below SELECT statement returns the customers having at least one order of value of more than 2000.

  • The inner query or subquery SELECT statement returns the list of distinct Customer IDs having orders of a value of more than 2000.
  • The outer query SELECT statement uses the list of Customer IDs as the list of values for IN operator.

Run this on IDE

SELECT *
FROM customers
WHERE customer_id 
IN (SELECT DISTINCT customer_id FROM orders WHERE order_value > 2000);

After successful execution, the output contains the below data.

customer_id customer_name address city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
3 Dominique Perrier 25, rue Lauriston Paris France 75016
4 Martin Blank Via Monte Bianco 34 Turin Italy 10100

INSERT Statement With Subquery

The below INSERT statement inserts the records into the premium_customers table, with records from the table customers having orders of value more than 2000.

  • The inner query or subquery SELECT statement returns the list of distinct Customer IDs having orders of value more than 2000.
  • The outer query SELECT statement uses the list of Customer IDs as the list of values to IN operator and fetches all the records.
  • The INSERT statement inserts all the fetched customer records into the premium_customers table.

Run this on IDE

INSERT INTO premium_customers
SELECT * FROM customers
WHERE customer_id 
IN (SELECT DISTINCT customer_id FROM orders WHERE order_value > 2000);

After successful execution, the premium_customers table contains the inserted records as below.

customer_id customer_name address city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
3 Dominique Perrier 25, rue Lauriston Paris France 75016
4 Martin Blank Via Monte Bianco 34 Turin Italy 10100

UPDATE Statement With Subquery

The below UPDATE statement updates the order value to increase it by 20, for all the customers residing in Germany and France.

  • The inner query or subquery SELECT statement returns the list of Customer IDs, who reside in the country 'Germany' or 'France'.
  • The outer query UPDATE statement uses the list of Customer IDs as the list of values to IN operator.

Run this on IDE

UPDATE orders 
SET order_value = order_value + 20
WHERE customer_id
IN (SELECT customer_id FROM customers WHERE country IN ('Germany', 'France'));

After successful execution, the order table contains the updated data as below.

order_id order_date order_value customer_id shipper_id
10250 2014-07-05 1827.5 1 3
10251 2014-07-15 1159 2 2
10252 2014-07-18 2842 3 3
10253 2014-07-19 2575.3 4 2
10254 2014-07-24 1256.25 5 3
10255 2014-07-31 9267.5 1 1
10256 2014-08-01 360 4 2
10257 2014-08-06 3171.65 3 1
10258 2014-08-08 2829 1 3
10259 2014-08-14 228 2 1

DELETE Statement With Subquery

The below DELETE statement deletes all the orders from the customers, residing in 'Germany' or 'France'.

  • The inner SELECT query or subquery fetches all the customer IDs from table customers, who resides in 'Germany' or 'France'.
  • The outer DELETE query uses the IN operator to grab the list of customer IDs and deletes the respective orders from the orders table.

Run this on IDE

DELETE FROM orders 
WHERE customer_id 
IN (SELECT customer_id FROM customers WHERE country IN ('Germany', 'France'));

After successful execution, the orders table contains the below data, which doesn't include the deleted data.

order_id order_date order_value customer_id shipper_id
10251 2014-07-15 1159 2 2
10253 2014-07-19 2575.3 4 2
10254 2014-07-24 1256.25 5 3
10256 2014-08-01 360 4 2
10259 2014-08-14 228 2 1

Tips & Suggestions

The data returned by a subquery can be a single value, a single row, a single column, or a table containing one or more rows of one or more columns.

Overall

We now understood how to write subqueries and use them on other statements like SELECT, INSERT, UPDATE, and DELETE.

Related Links