What is a Subquery?

A subquery is a query that is nested within another query. The outer query uses the result of the inner query to perform a more complex operation. Subqueries can be used in many ways, including as a part of the SELECT, UPDATE, and DELETE statements.

Using a Subquery in a SELECT Statement

One of the most common uses of subqueries is to retrieve data from a table based on a condition that involves data from another table. Here’s an example of how to use a subquery in a SELECT statement:

SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_total > 1000);

In this example, the outer query retrieves all the rows from the customers table where the customer_id is in the result set returned by the subquery. The subquery retrieves all the customer_ids from the orders table where the order_total is greater than 1000.

Using a Subquery in an UPDATE Statement

Subqueries can also be used in UPDATE statements to update rows in a table based on data from another table. Here’s an example of how to use a subquery in an UPDATE statement:

UPDATE customers
SET customer_discount = 10
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_total > 1000);

In this example, the UPDATE statement sets the customer_discount column to 10 for all the rows in the customers table where the customer_id is in the result set returned by the subquery. The subquery retrieves all the customer_ids from the orders table where the order_total is greater than 1000.

Using a Subquery in a DELETE Statement

Subqueries can also be used in DELETE statements to delete rows from a table based on data from another table. Here’s an example of how to use a subquery in a DELETE statement:

DELETE FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_total > 1000);

In this example, the DELETE statement deletes all the rows from the customers table where the customer_id is in the result set returned by the subquery. The subquery retrieves all the customer_ids from the orders table where the order_total is greater than 1000.

Subqueries are a powerful tool in MySQL that can be used in a variety of ways to retrieve and manipulate data from one or more tables. By nesting one query inside another, you can perform complex operations that would be difficult or impossible to do otherwise.