Writing Subqueries

A subquery can be defined as a query within a query. In other words, any query results that we reuse in another query. Subquery is known as nestee queries or subselects also. Subqueries don’t include any new functionality but the queries are more readable with using subqueries rather than of joins.

We will describe you the subqueries with the help of following tables :   

mysql> SELECT * FROM Client;
+------+---------------+----------+
| C_ID | Name          | City     |
+------+---------------+----------+
| 1    | A K Ltd       | Delhi    |
| 2    | V K Associate | Mumbai   |
| 3    | R K India     | Banglore |
| 4    | R S P Ltd     | Kolkata  |
| 5    | A T Ltd       | Delhi    |
| 6    | D T Info      | Delhi    |
+------+---------------+----------+
6 rows in set (0.08 sec)

mysql> SELECT * FROM Products;
+---------+-------------+------+----------+
| Prod_ID | Prod_Detail | C_ID | price    |
+---------+-------------+------+----------+
| 111     | Monitor     | 1    | 7000.00  |
| 112     | Processor   | 2    | 11000.00 |
| 113     | Keyboard    | 2    | 1200.00  |
| 114     | Mouse       | 3    | 500.00   |
| 115     | CPU         | 5    | 15500.00 |
+---------+-------------+------+----------+
5 rows in set (0.00 sec)

There are 3 basic types of subqueries in SQL: 

• Predicate Subqueries - extended logical constructs in the WHERE (and HAVING) clause.
• Scalar Subqueries - standalone queries that return a single value; they can be used anywhere a scalar value is used.
• Table Subqueries - queries nested in the FROM clause.

All subqueries must be enclosed in parentheses. 

Predicate Subqueries

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

• IN Subquery
      
  The IN subquery tests if a scalar values match with the single query column value in any subquery result row. The general syntax is :
          Value_1 [NOT] IN (query_1)

  In the following example we are getting the list of clients that are available in Products table also. Example : 

  mysql> SELECT * FROM Client WHERE C_ID IN -> (SELECT C_ID FROM Products); +------+---------------+----------+ | C_ID | Name | City | +------+---------------+----------+ | 1 | A K Ltd | Delhi | | 2 | V K Associate | Mumbai | | 3 | R K India | Banglore | | 5 | A T Ltd | Delhi | +------+---------------+----------+ 4 rows in set (0.00 sec)

  
  In the following example we are getting the list of clients that are not available in Products table also. Example : 
       

  mysql> SELECT * FROM Client WHERE C_ID NOT IN -> (SELECT C_ID FROM Products); +------+-----------+---------+ | C_ID | Name | City | +------+-----------+---------+ | 4 | R S P Ltd | Kolkata | | 6 | D T Info | Delhi | +------+-----------+---------+ 2 rows in set (0.01 sec)

        
• Quantified Subqueries 
            
  A quantified subquery can use the all comparison operators for several types of tests. The general syntax is :
          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 : 
       

  mysql> SELECT * FROM Client WHERE C_ID= ANY(SELECT C_ID FROM Products); +------+---------------+----------+ | C_ID | Name | City | +------+---------------+----------+ | 1 | A K Ltd | Delhi | | 2 | V K Associate | Mumbai | | 3 | R K India | Banglore | | 5 | A T Ltd | Delhi | +------+---------------+----------+ 4 rows in set (0.00 sec)

     

• Exists Subqueries
       
  The EXISTS subquery is used to tests whether a subquery returns at least one row or a qualifying row exists. The general syntax is :
          Exists (query_1)
       
  Any EXISTS subquery should contain an outer reference. It must be a correlated subquery. Example : 

  mysql> SELECT * FROM Client -> WHERE EXISTS -> (SELECT * FROM Products WHERE Client.C_ID=Products.C_ID); +------+---------------+----------+ | C_ID | Name | City | +------+---------------+----------+ | 1 | A K Ltd | Delhi | | 2 | V K Associate | Mumbai | | 3 | R K India | Banglore | | 5 | A T Ltd | Delhi | +------+---------------+----------+ 4 rows in set (0.00 sec)

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 : 

mysql> SELECT (SELECT Name FROM Client WHERE C_ID=1);
+----------------------------------------+
| (SELECT Name FROM Client WHERE C_ID=1) |
+----------------------------------------+
| A K Ltd                                |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT (SELECT C_ID FROM Products WHERE C_ID=2) FROM Client;
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> SELECT (SELECT C_ID FROM Products WHERE C_ID=1) FROM Client;
+------------------------------------------+
| (SELECT C_ID FROM Products WHERE C_ID=1) |
+------------------------------------------+
| 1                                        |
| 1                                        |
| 1                                        |
| 1                                        |
| 1                                        |
| 1                                        |
+------------------------------------------+
6 rows in set (0.01 sec)

Table Subqueries

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

mysql> SELECT Client.*,Price
    -> FROM Client, Products
    -> WHERE Client.C_ID=Products.C_ID
    -> AND Price>1000;
+------+---------------+--------+----------+
| C_ID | Name          | City   | Price    |
+------+---------------+--------+----------+
| 1    | A K Ltd       | Delhi  | 7000.00  |
| 2    | V K Associate | Mumbai | 11000.00 |
| 2    | V K Associate | Mumbai | 1200.00  |
| 5    | A T Ltd       | Delhi  | 15500.00 |
+------+---------------+--------+----------+
4 rows in set (0.06 sec)

Using Single Value Subqueries

Firstly we will start with a simple query : 

mysql> SELECT MAX(Price) FROM Products;
+------------+
| MAX(Price) |
+------------+
| 15500.00   |
+------------+
1 row in set (0.60 sec)

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 :

mysql> SELECT * FROM Client c,Products p WHERE c.C_ID=p.C_ID
    -> AND p.Price=(SELECT MAX(Price) FROM Products);
+------+---------+-------+---------+-------------+------+----------+
| C_ID | Name    | City  | Prod_ID | Prod_Detail | C_ID | price    |
+------+---------+-------+---------+-------------+------+----------+
| 5    | A T Ltd | Delhi | 115     | CPU         | 5    | 15500.00 |
+------+---------+-------+---------+-------------+------+----------+
1 row in set (0.02 sec)

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