Subqueries in MySQL

Definition :
Sub-query is a SELECT statement used within a WHERE clause or having CLAUSE of a SQL statement. Usually a sub-query is executed first then result return to the main query.Based on the returned value the main query is executed. Sub-queries 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
 

Writing Sub-queries : With the help of some examples we have to use sub-queries.

The sub-queries describe with the help of following tables :

 

There are 3 basic types of sub-queries in SQL: 

1. Predicate Sub-queries - It extended logical constructs in the WHERE (and HAVING) clause.
2. Scalar Sub-queries - In the standalone queries that return a single value; they can be used anywhere a scalar value is used.
3. Table Sub-queries - In this type of subqueries queries nested in the FROM clause.

All sub queries must be enclosed in parentheses. 

Predicate Subqueries :

These Subqueries can be used in the HAVING and WHERE clause only because both are special logical construct. These sub-queries must retrieve one column.

IN Sub query
  
The IN subquery tests if a scalar values match with the single query column value in any subquery result row.

syntax :  Value_1 [NOT] IN (query_1)

With the help of following example we are getting the list of clients that are available in Products table also.

Example :


This example we are getting the list of clients that are not available in Products table also.

Example :

 

Quantified Subqueries : A quantified subquery can use the all comparison operators for several types of tests.

syntax : Value_1 {=|>|<|>=|<=|<>} {ANY | ALL | SOME} (query_1)
  
The comparison operator is used to compare value_1 to the single query column value from each subquery result row. If we are using ALL clause then must match the all rows in subquery, or subquery must be empty. If we are using ANY or SOME clause then must match at least one row in the subquery.

 Example : SELECT * FROM Client WHERE C_ID= ANY(SELECT C_ID FROM Products);

Exists Sub-queries :The EXISTS subquery is used to tests whether a sub-query returns at least one row or a qualifying row exists.

syntax : Exists (query_1)
 
Any EXISTS sub-query should contain an outer reference. It must be a correlated sub-query.

Example :  SELECT * FROM Client
                WHERE EXISTS
                (SELECT * FROM Products WHERE Client.C_ID=Products.C_ID);

Scalar Subqueries:

The Scalar Subquery is a subquery which returns a single value. A Scalar subquery can be used almost anywhere a single column value can be used. The subquery have to reference only one column in the select list. It must not retrieve more than one row. When subquery retrieve one row then the value of select list column becomes the value of the Scalar Subquery.

Example :

Table Subqueries: These subqueries are used in the FROM Clause , replace the table name. These subqueries can have correlation name also.

Example :

Using Single Value Subqueries: There are two types we have to use this command.

Firstly we will start with a simple query : SELECT MAX(Price) FROM Products;

In the above example retrieve only a single value and its representing the maximum Price of the Product. In this example we used a MySQL Function MAX() that finds the greatest values in a specified column. 

Single ? value subqueries is used to return a single column value and then they are typically used for comparison.

For Example :

In the above example we are getting the detail of products that have the highest price and the client details also.