18 - SQL NESTED or SUB Queries

Subqueries or NESTED queries basically mean query within a query. Sub query is used to return data to the main query so as to give more refinement to the data retrieval. They are embedded within the WHERE clause.

Sub queries can be used in SELECT, INSERT, UPDATE and DELETE statements.  The operators like <,>,<>,= can be used in the statements.

Basic Syntax:

SELECT column_name

FROM table_name

WHERE column_name [operator]

(SELECT column_name

            FROM table_name1, [table_name2]..

            WHERE condition)

Please note: Sub queries should be enclosed in parenthesis.

Example1:

Consider there is a STUDENT table, and you want to know the details of oldest students in the college. The following query would return the details.

SELECT FirstName, LastName, AGE, GRADE

FROM STUDENT

WHERE AGE = (SELECT MAX(AGE) FROM STUDENT);

Let us see how this query works. First the subquery is executed, hence it would give the maximum age of the students from the STUDENT table. Then the main query would be executed. Hence, if for example, 23 was the max age of the students then details of all the students with age = 23 would be retrieved.

Example 2:

Consider tables:

+-------------------+-------------------+----------+------------------ 
| EMP_ID     | EMMP_NAME | AGEG | PHONE_NUM     | DEPT_ID | SALARY |
+-------------------+-------------------+----------+------------------ 
|      1     | john      |   35 | 100233023     |       2 |  10000 |
|      2     | Linda     |   30 | 100234565     |       1 |  15000 |
|      3     | Max       |   40 | 122222344     |       3 |  22000 |
|      4     | Will      |   40 |  12323424     |       3 |  31000 |
|      5     | Michal    |   45 |  12323434     |       3 |   5000 |
+-------------------+-------------------+----------+------------------- 

DEPARTMENTS:

+------------+-----------------------+
| DEPT_ID | DEPT_NAME    |
+------------+-----------------------+
|       1 | Accounts     |
|       2 | HR           |
|       3 | Production   |
+------------+-----------------------+

Consider the example where you know details of employees under Accounts department.

 

SELECT * FROM EMPLOYEE          

 WHERE DEPT_ID = (SELECT DEPT_ID FROM DEPARTMENTS   WHERE DEPT_NAME = "ACCOUNTS");  

 

This would give below result:

+-----------+------------------------+---------+--------------------+---------------+-----------------+
| EMP_ID | EMMP_NAME     | AGEG | PHONE_NUM | DEPT_ID     | SALARY     |
+-----------+------------------------+---------+--------------------+---------------+-----------------+
|      2 | Linda         |   30 | 100234565 |       1     |  15000     |
+-----------+------------------------+---------+--------------------+---------------+-----------------+
1 row in set (0.06 sec)

Keyword IN

In the above example, the subquery returned only 1 value, but what if we have to use queries which give multiple values and conditions to handle the multiple values? One of the ways is to use IN keyword. When a sub query returns multiple values, then IN keyword is used to compare if the value for checking is present in multiple values from subquery.

Question:

What would be output of below query:

SELECT * FROM EMPLOYEE

 WHERE DEPT_ID =

 (SELECT DEPT_ID FROM DEPARTMENTS );

Solution:

ERROR 1242 (21000): Subquery returns more than 1 row

This can be solved by using IN keyword.

SELECT * FROM EMPLOYEE

WHERE DEPT_ID IN

(SELECT DEPT_ID FROM DEPARTMENTS); 

Example 2:

Consider the example of employee and departments.

Consider below query:

select * from employee where emp_id in

(select emp_id from employee

where salary > 10000);

 

The output of this query will be all the employees with salary > 10000. This will give same result as the query

SELECT * FROM EMPLOYEE WHERE SALARY > 10000;

The subquery will return the multiple EMP_IDs which have salary > 10000 and then main query would check if emp_id of each row is present in the values returned from subquery. If present then details would be given. 

Using EXISTS clause.

Whenever a query is run, it either returns a value or multiple values or none. EXISTS and NOT EXISTS clauses are used to tell whether any of the rows in the table mentioned in FROM clause meet any of the conditions in the WHERE clause. If the sub query returns at least one row, then EXISTS condition is considered to be TRUE. The clauses can be used in SELECT, INSERT, UPDATE or DELETE statements.

The working of EXIST clause is different than the other subquery, here the rows of main query are retrieved and then for each row the sub query is checked. Hence it is more time consuming.

Syntax:

Query1

Where EXISTS

(SUBQUERY);

Point to remember: Performance wise, the EXISTS Clause is inefficient as sub query is re-run for each row of the main query.

Example:

Consider the movie app example,

The movie_details contains the details of the movie, while movie_tran contains the transaction details. Below query should give all the records of the movie details, for all the movies that has been ordered.

SELECT * FROM MOVIE_DETAILS

WHERE EXISTS  (SELECT * FROM MOVIE_TRAN

         WHERE MOVIE_DETAILS.MOVIE_ID = MOVIE_TRAN.MOVIE_ID);

NOT EXISTS clause

This clause works in the similar way just that it gives the row when no rows are returned for the value from the main query. 

Consider the above example itself, if we use NOT EXIST then query output should be movie details which have not been ordered and no transaction entered in MOVIE_TRAN table.

SELECT * FROM MOVIE_DETAILS

WHERE NOT EXISTS  (SELECT * FROM MOVIE_TRAN

WHERE MOVIE_TRAN.MOVIE_ID = MOVIE_DETAILS.MOVIE_ID);

Sub Queries in UPDATE

The sub queries can be added to UPDATE, INSERT or DELETE statements in the same way as SELECT statement.

Example:

Consider the example of an accounts payable system, where the details of the vendors and purchase orders are maintained and the goods are provided on 60 day credit. Suppose there has been a change to the credit amount to 20%  for a vendor (here COCO) from existing 10%. Then update the transaction table with the changed amount.

UPDATE PURCH_TRAN

SET CRED_AMT = AMT * 0.20

WHERE VENDOR_ID = (SELECT VENDOR_ID FROM VENDOR_DETAILS

                                                WHERE VENDOR_NAME = ‘COCO’);

Like us on Facebook