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
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 readableA 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:
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 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) |
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) |
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.
Ads