A subquery (an inner or nested query) is a query within another SQL query (a main or outer query). Subqueries can be nested within SELECT, UPDATE, INSERT, and DELETE statements.
Subqueries in SELECT statement
Subqueries nested in SELECT statements are the most common type of subqueries. A subquery may occur in the SELECT clause, in the FROM clause, and in the WHERE clause of the SELECT statement.
Table registered_users has two columns: username and sign_up_date :
Use a query with a subquery nested into the WHERE clause to select the first registered users:
sign_up_date = (
The result of the query will look as follows:
The subquery from the example above is a single-row subquery. In this case, it is safe to use simple SQL comparison operators (=, >, <=, etc.) because we can be sure that our query will return only one row with one column.
Using a subquery along with comparison operators and the result subquery returns more than one row, there will be an error. This doesn't mean you can't use the multiple-row subquery that will return one or more rows. Use a multiple-row comparison operator, such as IN and ANY.
Usea subquery to select one of the columns in the SELECT statement to add new columns to the result set.
Let's select all data from our table registered_users and add some more info from the table users_info given below:
To select all the information from the registered_users table and add all the corresponding information about the actual usernames from the users_infotable:
username = registered_users.username) AS name
The result will look as follows:
As was already mentioned, use a subquery in the FROM clause of the SELECT statement.In this case, a derived table (a table we obtain with subquery) must be aliased.
The subqueries can be correlated. Correlated subqueries use values from the outer query.
Take a look at the example of a correlated subquery. Here is the table new_orders with the columns idINT, product VARCHAR(40), product_category VARCHAR(40), quantityINT and unit_price INT:
Use a correlated subquery to select all the products with a unit price less than the average in the product category:
new_orders AS newor
WHERE unit_price < (
product_category = newor.product_category);
While query results are evaluated, the correlated subquery will be executed for each row in the table, so evaluating the query with subquery for large tables can take a lot of time.
Share this article
Get more lessons like this
Thank you! Your submission has been received!
Oops! Something went wrong.
Level up your tech skills and advance your career
• Wide range of SQL and Databases tracks
• Study at your own pace with your personal study plan
The subqueries can also be used with the UPDATE statement.
Let's update the table studentswith the columns name VARCHAR(40), scholarship INT, and exams_passed BOOLEAN:
As with the SELECT, use the UPDATE statement with a subquery nested into the WHERE clause. Use this type of query with a subquery to set exams_passed to TRUE if in the exam_resultstable, both exam marks are greater or equal to 18. The table exam_results looks as follows (name VARCHAR(40), math_exam_markINT, and english_exam_mark INT):
To update the table students use this query:
exams_passed = TRUE
name IN (
math_exam_mark >= 18
AND english_exam_mark >= 18
After the execution of the query above, the table students will look as follows:
Use the UPDATE query with a subquery to set the modified value. Use the query below to set scholarship column values equal to the minimal scholarship for all students who did not pass the exams. Suppose that the scholarship amounts are stored in the scholarships table.
scholarship = (
exam_passed = FALSE;
Now Tamara Fetch will have a scholarship equal to 200:
Subqueries nested in the INSERT statement
In the INSERT statement uses a subquery to insert the data returned from the subquery into another table.
In the example below, let's work with the table employees with the columns nameVARCHAR(20), salary INT and department_id INT:
All the data about departments is stored in the table departments with the columns id INT and department VARCHAR(20):
Let's add to the employees table the info about Tomas Hedwig, who works in the PR department and has a salary equal to Ann Reed's. To get the salary and department IDs, use subqueries as shown below:
INSERT INTO employees
(SELECT salary FROM employees WHERE name = 'Ann Reed'),
(SELECT id FROM departments WHERE department = 'PR')
After the query execution the employeestable will have two rows:
Subqueries nested in the DELETE statement
Use subqueries in the DELETE statement as a part of the condition in the WHERE clause.
Assume there's a table orders with columns order_id INT, customer_id INT, product VARCHAR(20), and cityVARCHAR(20):
And another table customers with columns customer_id INT and name VARCHAR(40):
To delete from the table orders all the orders made by Ann Smith, the query will be:
DELETE FROM orders
WHERE customer_id = (SELECT customer_id FROM customers WHERE name = 'Ann Smith'
After the query execution, the orders table will look as follows:
Same as with the UPDATE, you cannot use the same table in the query and subquery in the DELETE statement.
As data drives decisions in modern industries, understanding how to store, retrieve, and manage it is crucial. Our tracks cover the fundamentals of SQL, teaching you to interact with databases efficiently.