Custom Search

What is subquery

A subquery is a SELECT statement within another SQL statement. The SQL statement can be SELECT, WHERE clause, FROM clause, JOIN, INSERT, UPDATE, DELETE, SET, DO, or another subquery.

The query that contains the subquery is normally called outer query and the subquery itself is called inner query.

Advantages of using subquery

  • Subqueries structure a complex query into isolated parts so that a complex query can be broken down into a series of logical steps for easy understanding and code maintenance.

  • Subqueries allow you to use the results of another query in the outer query.

  • In some cases, subqueries can replace complex joins and unions and subqueries are easier to understand.

Disadvantages of using subquery

When subquery is used, the database server (actually the query optimizer) may need to perform additional steps, such as sorting, before the results from the subquery are used. If a query that contains subqueries can be rewritten as a join, you should use join rather than subqueries. This is because using join typically allows the query optimizer to retrieve data in the most efficient way. In other words, The optimizer is more mature for MySQL for joins than for subqueries, so in many cases a statement that uses a subquery can be executed more efficiently if you rewrite it as a join.

Rules that govern the use of subqueries

  • A subquery must always appear within parentheses.

  • You can embed a subquery inside another one. You can have as many level as you need.

  • If the outer query expects a single value or a list of values from the subquery, the subquery can only use one expression or column name in its select list.

  • When you use the result from a subquery to join a table in a JOIN operation, no index can be used on the join column(s). This is because subquery first generates result on the fly and then the result is used in the join.

Other tutorials in this category

1. Using subquery to return a single value (known as single-value subquery or scalar subquery)

2. Using subquery to return a list of values (known as column subquery)

3. Using subquery to return one ore more rows of values (known as row subquery)

4. Using correlated subqueries

5. Using EXISTS and NOT EXISTS in correlated subqueries in MySQL

6. Using subquery in SELECT statement in MySQL

7. Using subquery in FROM clause in MySQL

8. JOIN a table with a subquery

Back to Tutorial Index Page

Copyright © 2017 All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

Home | Feedback | Terms of Use | Privacy Policy