SQL for QA: Tutorial#10 – Sub Queries

Supriya

5 years ago

Sub Query is nothing but a nested query or we can say query within a query.

Frankly speaking, I have not used sub queries that often in my 10 years of career in Testing / QA. But yes, I have been always asked sub queries in job interviews, even for manual testing. And I was able to impress interviewer every time 🙂

The sub query is written in the form of Inner query and outer query; Inner query should be in parenthesis.

To execute such kind of query, database system first execute inner query, its result is used by outer query for its execution.

We can write sub queries by using following ways.

By using IN / NOT IN keywords
With ANY / ALL / SOME keywords
With EXISTS / NOT EXISTS KEYWORDS
Sub queries can also be used with the SELECT, INSERT, UPDATE, DELETE and comparison operators.

Few points to be remember with sub query

Most of the time sub queries are written with SELECT clause, however you can write it with INSERT,Â UPDATEÂ orÂ DELETE as well.
Subquery always be in parentheses. Like , Outer query ( Inner query).
Subquery should not be a UNION, only single SELECT statement is allowed.
Subquery must return only single column.
If you are using IN/ NOT IN keywords with sub queries, it returns more than one row.
ORDER BY command cannot be used in a Sub query.
GROUPBY command can be used to perform same function as ORDER BY command.

Â Consider following employee table for sub query examples:

SELECT * FROM Employee;

Employee Table:

E_ID |Â  E_NameÂ Â | E_Department| E_Salary
-----+----------+-------------+-------
 1   | Reena SÂ  | bcsÂ Â        | 10000
 2   | SupriyaÂ  | bcsÂ Â        | 11000
 3   | ShitalÂ Â  | bcsÂ Â        | 12000
 4   | NilimaÂ Â  | bcsÂ Â        |Â  2000
 5   | AartiÂ Â Â  | bcaÂ Â        | 22000
 6   | DeepaliÂ  | bcaÂ Â        | 14000
 7   | NitaÂ Â Â Â  | bcaÂ Â        | 18000
 8   | AlkaÂ Â Â Â  | mcaÂ Â        | 28000
 9   | Jayshree | mcaÂ Â        | 38000
 10  | Nilima   | bcaÂ Â        | 21000

By using IN / NOT IN keywords

The IN / NOT IN operators are logical operators which allows you to compare a value against a set of values.

The IN operator returns true if the value is present within the set of values. Otherwise, it returns false or unknown. IN operator is nothing but the shortcut of multiple OR operators.

The NOT IN operator is exactly opposite to IN.Â It returns true if the expression does not match any of the value in the list, otherwise, it returns false. NOT IN operator is nothing but the shortcut of multiple AND operators.

Example #1: Find out second Max salary from Employee table (using NOT IN)

SELECT MAX(E_Salary)FROM Employee WHERE E_Salary NOT IN(SELECT MAX(E_Salary)FROM Employee);

Output:

MAX
-------
28000

Example #2: Display all the employee from bcs department. (using IN)

SELECT E_Name FROM Employee 
WHERE E_Name IN(SELECT E_Name FROM Employee WHERE E_Department='bcs');

Output:

E_Name
---------
Reena S
Supriya
Shital
Nilima

Example #3: Display all employees belonging to both Departments xbcax and xbcsx (using IN)

SELECT E_Name FROM Employee where E_Department='bca' 
AND E_Name IN(select E_Name FROM Employee WHERE E_Department='bcs');

Output:

E_Name
--------
Nilima

With ANY / ALL /SOME keywords

ANY is a logical operator must be preceded by a comparison operator an followed by a sub query, it compares a value with all values returned by a sub query.

Example #1: using ALL keyword

In following query E_Salary get compared with ALL the values returned by sub query , in short result will display E_Salary which is less than ALL the values(biggest value amongst all ) returned by sub query.

SELECT E_Salary FROM Employee 
WHERE E_Salary xALL(SELECT E_Salary FROM Employee WHERE E_Department='bca');

Output:

E_Salary
-------
10000
11000
12000
2000

SELECT E_Salary FROM Employee 
WHERE E_Salary xALL(SELECT E_Salary FROM Employee WHERE E_Department='bca');

Output:

E_Salary
-------
28000
38000

Example #2: using SOME keyword

SOME is a logical operator must be preceded by a comparison operator and followed by a sub query, it compares a value with single column set of values returned by sub query and it should match at least one value in sub query.( smallest value amongst all)

SELECT E_Salary FROM Employee WHERE E_Salary xSOME(SELECT E_Salary FROM Employee WHERE E_Department='bca');

Output:

E_Salary
-------
22000
18000
28000
38000
21000

Example #3: using ANY keyword

ANY is a logical operator must be preceded by a comparison operator and followed by a sub query, ANY operator returns true if any of the sub query values meet the condition.

SELECT E_Salary FROM Employee WHERE E_Salary xANY(SELECT E_Salary FROM Employee WHERE E_Department='bca');

Output:

E_Salary
-------
10000
11000
12000
2000
14000
18000
21000

With EXISTS / NOT EXISTS KEYWORDS

Use of EXITS keyword

The EXISTS operator allows you to write a sub query to test for the existence of rows.

The EXISTS operator returns true if the sub query contains any rows, if not it returns false.

The EXISTS operator terminates the query processing immediately after it finds a row, so we can use EXISTS operator to improve the query performance.

Example #1:

InÂ below query, sub query is checking for the existence of E_Name= xNilimax, this record is exists in Employee table there for outer query will get executed.

SELECTÂ E_Name FROM Employee 
WHERE E_Department='mca' AND exists (SELECT * FROM Employee WHERE E_Name='Nilima');

Output:

E_Name
----------
Alka
Jayshree

Example #2:

InÂ below query, sub query is checking for the existence of E_Name= xRenux, this record is not presentÂ in Employee table there for outer query will notÂ get executed.

SELECTÂ E_Name from Employee where E_Department='mca' and exists (select * from Employee where E_Name='Renu');

Output:

E_Name
-------
(0 rows)

Use of NOT EXITS keyword

The NOT EXISTS operator allows you to write a sub query to test for the NON existence of rows.

The NOT EXISTS operator returns true if the sub query doesnât match with any rows, if it matches it returns false.

Example #3:

SELECT E_Name FROM Employee WHERE E_Department='mca' and NOT EXISTS (SELECT * FROM Employee WHERE E_Name='Renu');

Output:

E_Name
----------
Alka
Jayshree

In above query, sub query is checking for the non existence of E_Name= xRenux, this record is not present in Employee table there for outer query will get executed.

Some different ways to write sub queries

Example #1 :Â

We can write own sub query inside SELECT clause as well

SELECT E_Department, (SELECT AVG(E_Salary)) FROM Employee GROUP BY E_Department;

Ouput:

E_DepartmentÂ | Avg
-------------+-------------
 mcaÂ Â Â Â Â Â Â Â Â | 33000.0000
 bcaÂ Â Â Â Â Â Â Â Â | 18750.0000
 bcsÂ Â Â Â Â Â Â Â Â |Â 8750.0000

Example #2 :

SELECT * FROM Employee 
WHERE E_Salary x (SELECT MIN(E_Salary) FROM Employee WHERE E_Department='bca');

Output:

E_ID |Â  E_NameÂ  | E_Department  | E_Salary
-----+----------+---------------+-------
 1   | Reena S  | bcsÂ         Â  | 10000
 2   | Supriya  | bcsÂ Â          | 11000
 3   | ShitalÂ   | bcsÂ Â          | 12000
 4   | NilimaÂ   | bcsÂ Â          |Â  2000

Example #3:

DELETE FROM emp112 WHERE E_ID IN (SELECT E_ID FROM Employee WHERE E_Department='mca');

Output:

For above query 2 rows will get deleted from mca dept.

Please let us know if you have any queries / comment regarding this article. We will surely reply you.