SQL for QA: Tutorial#10 – Sub Queries

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.

1. By using IN / NOT IN keywords
2. With ANY / ALL / SOME keywords
3. With EXISTS / NOT EXISTS KEYWORDS
4. 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:

Employee Table:

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)

Output:

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

Output:

Example #3: Display all employees belonging to both Departments ‘bca’ and ‘bcs’ (using IN)

Output:

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.

Output:

 

Output:

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)

Output:

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.

Output:

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= ‘Nilima’, this record is exists in Employee table there for outer query will get executed.

Output:

Example #2:

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

Output:

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:

Output:

In above query, sub query is checking for the non existence of E_Name= ‘Renu’, 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

Ouput:

Example #2 :

Output:

Example #3:

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.