SQL – Subquery Example

In some cases you can do things with a subquery that can't be done with join. However when using subqueries, there can be performance costs.

An SQL subquery is a SELECT statement inside another statement. Subqueries are useful alternates to JOIN and can, in some cases, do things that JOIN cannot. Here is an example of SQL subquery:

SELECT f_name, l_name FROM employee WHERE id IN (SELECT id FROM salary WHERE salary < 70000);

In this example, (SELECT id FROM salary WHERE salary < 70000) is a subquery, you can even nest subqueries within another subqueries.

However, subqueries are not as quick as joins when selecting big datasets and there can be surprising performance costs. Please note that the value that subquery returns must mach the value that the parent query expects. For example, if you use WHERE id = 52 then value must be a single value, not a list, otherwise you will encounter an error.

Lean More:
MySQL 5.0 Reference Manual :: 13.2.9 Subquery Syntax

Have your say