Subquery is a query statement written within an other query such as SELECT, INSERT, UPDATE or DELETE.

In MySQL query can be nested inside another query up to considerable depth. The MySQL subquery is called inner query, while the query that contains subquery is called outer query. Subqueries allows queries to be structured so that each part of statement can be isolated. They provide alternative way to complex operations of JOIN and UNION.

The outer statement of subquery can be any of SELECT, INSERT, UPDATE, DELETE, SET, DO. Subquery can contain any of the keywords such as DISTINCT, GROUP BY, ORDER BY, LIMIT, joins, index, hints, UNION, comments, functions, etc. Subquery can return any of these: a scalar (single value), single column, single row, or a table. We cannot modify table and return from a subquery.

Example of complex subquery:

SELECT name, 

(SELECT dob FROM table1 

WHERE service > 30) AS date_of_birth, gender,

(SELECT pension FROM table1

WHERE pension > 10000) AS pension

FROM main_table;

Where,

  • Subquery are enclosed within parenthesis.

WHERE clause

A subquery can be written using WHERE clause and any of these comparison operators: = > < >= <= <> != <=>. This is used if subquery returns one value. For example:

SELECT * FROM tblSchool1

WHERE marks <= (SELECT MAX(marks) FROM tblSchool2);

IN and NOT IN operators

When subquery returns more than one value, we use IN and NOT IN operators in WHERE clause. For example:

SELECT presidentCandidates FROM tblCandidates WHERE presidentCandidates NOT IN (SELECT criminalNames FROM tblCops);

EXISTS and NOT EXISTS operators

When subquery returns Boolean value operators are used. For example:

SELECT state FROM tblCandidates WHERE EXISTS (SELECT * FROM tblAddress);

FROM clause

When subquery returns a table, the FROM clause is used.

SELECT col1, col2, col3

FROM (SELECT firstName AS col1, lastName AS col2, age AS col3 FROM tblCandidates) AS col

WHERE age > 18;

Correlated subquery

When a subquery is dependent on the data of the outer query, such query are called correlated subquery. The correlated subquery is evaluated once for each row of the outer query.

SELECT * FROM tblpatients

WHERE disease = ANY (SELECT disease FROM tblprescriptions

WHERE tblprescriptions.medicine = tblpatients. medicine);