A Step-By-Step Guide To SQL Subquery

A SQL subquery as the term suggested is a query nested within another query. These subqueries can be present in the FROM clause, WHERE clause, or the SELECT clause. Subqueries are a powerful tool for combining data available in two tables into a single result. 

In this article, we will discuss - 

  • What is a SQL subquery?
  • General rules of subqueries
  • Subqueries with SELECT statement
  • Subqueries with FROM statement
  • Subqueries with an INSERT statement
  • Subqueries with an UPDATE statement
  • Subqueries with the DELETE statement

What is a SQL Subquery?

An SQL subquery is nothing but a query inside another query. We use a subquery to fetch data from two tables. A subquery is often also referred to as an inner query, while the statement containing a subquery is also called an outer query or outer select. We can implement subqueries with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.

General Rules of Subqueries

There are some rules which are followed while using subqueries. They are:

  • A subquery must be enclosed in parentheses. 
  • Subqueries that return over one row can only be used with multiple value operators such as the IN operator.
  • SQL Server allows you to nest subqueries up to 32 levels.

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

Subqueries With SELECT Statement

Subqueries are commonly used with the SELECT statement. The basic form is shown below - 

(SELECT [DISTINCT] subquery_select_argument

FROM {table_name | view_name}

{table_name | view_name} ...

[WHERE search_conditions]

[GROUP BY aggregate_expression [, aggregate_expression] ...]

[HAVING search_conditions])

Example:

Consider the ‘products’ table having the following records.

dataset-sql-subquery

Now, let us implement the following SQL subquery with SELECT statement.

sql-subquery-select

This will produce the following result.

output-select

Subqueries With FROM Statement

A FROM clause is used to specify the subquery statement in SQL.

Note: The result of the evaluation is stored in a temporary variable.

Example:

FROM-clause

The following SQL query fetches the result where quantity in stock from products_bkp table where the quantity in stock is greater than average quantity in stock

/output-from

Subqueries With an INSERT statement

Consider the table ‘products_bkp’ with a similar structure to the ‘products’ table.

Now, to copy the complete ‘products’ table into the ‘products_bkp’ table, you can use the SQL subquery with INSERT statement in the following way.

SQL-subquery-insert

This will copy the data into the ‘products_bkp’ table.

output-insert

Subqueries With an UPDATE Statement

You can set a new column value in an UPDATE statement equal to the result returned by a single row subquery.

Example:

If you want to update the unit price of products_bkp table where the unit price will be greater than the minimum unit price from the products table, we can use the following SQL query:

sql-subquery-update

This will produce the following result.

output-update.

Subqueries With the DELETE Statement

We can use the DELETE statement with subqueries to delete the records.

Example:

The following SQL query will delete the data from the products_bkp table where the unit price is less than the maximum unit price from the table products.

/delete

This will produce the following result.

output-delete

Gain expertise in the latest Business analytics tools and techniques with the Post Graduate Program in Business Analysis. Enroll now!

Conclusion

In this article, we learned about SQL subqueries and how to use subqueries to form flexible SQL statements. We worked on several examples to understand how we can combine two statements to get the best result from a subquery.

If you wish to learn more about MySQL, then check out our SQL certification course.

This SQL certification course provides you with a very thorough understanding of all you need to begin working with SQL databases in your applications. The course will enable you to correctly structure your database, author efficient, and error-free SQL statements and clauses, and manage SQL databases for scalable growth.

If you have any questions, ask them in our comments section of this article. Our experts will answer them for you promptly!

About the Author

Aryan GuptaAryan Gupta

Aryan is a tech enthusiast who likes to stay updated about trending technologies of today. He is passionate about all things technology, a keen researcher, and writes to inspire. Aside from technology, he is an active football player and a keen enthusiast of the game.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.