MySQL Subquery

MySQL Subquery is also called the queries within queries or nested queries. This concept helps in filtering data from tables depending upon the values returned by a subquery from same or different table.

MySQL allows to add subquery in DML statements ( SELECT, INSERT, UPDATE and DELETE) after the WHERE clause. The results returned by the query written after the comparison operator in WHERE clause is the condition for outer DML statement.

 The outer DML statement is the main query whose results depend on the inner SELECT statement. The Subquery can also further have a WHERE clause.

Subqueries are used to write complex queries which may be otherwise written with UNION or JOIN clauses.

 Syntax of MySQL Subquery

SELECT/ INSERT/ UPDATE/ DELETE *|column-name[s] FROM table1

WHERE filter-column-name COMPARISON-OPERATOR (SELECT *|column-name(s) FROM table2)

  • SELECT, INSERT, DELETE, UPDATE, FROM, WHERE, are the keywords
  • Table1, Table2 are the table names
  • “SELECT column-name[s] FROM table” is the outer query and “SELECT column-name FROM table2” is the inner query or the subquery.
  • COMPARISON-OPERATOR is any one from these  =, !=, >,>=,<,<=, ANY, ALL, SOME or IN. 

Values Returned by MySQL Subquery

  • A subquery may return only one value is called scalar subquery and it uses  =, !=, >,>=,<,<= for comparison in outer query.
  • A subquery returning a column is compared by the outer query using ANY, ALL, SOME or IN comparison operators. Such subquery is called column subquery.
  • A subquery returning a row is compared by the outer query using ANY, ALL, SOME or IN comparison operators. Such subquery is called a row subquery.
  • Subqueries can also return a table as set of values to be compared. Such queries are called Table subqueries

Rules to Write MySQL Subqueries

  • Nested Subqueries can be further written as nested queries. Multiple levels of nesting in subqueries is permitted
  • Subquery is always written as SELECT statement
  • The SELECT statement keywords can be used in a MySQL Subquery. These keywords are DISTINCT, GROUP BY, ORDER BY, JOIN, UNION and MySQL functions.