SQL Sub-Queries

What are Sub queries?

SQL Sub queries are the queries which are embedded inside another query. The embedded queries are called as INNER query & container query is called as OUTER query.

The subqueries are the queries which are executed inside of another query. The result SQL query totally depends on the result of a subquery. First, the INNER query gets executed & the result of an INNER query is passed as input to the outer query.

SQL Sub-Query Syntax:

Let’s look at the basic syntax of the SQL Sub query command:

Learn SQL Sub-Queries

Three types of sub queries are supported in SQL are – Scalar, Row and Table sub queries.

  • The Scalar subquery result returns only a single row and single column.
  • The Row subquery result returns only a single row with single/multiple column(s).
  • The Table subquery result returns can be return single/multiple row(s) or column(s).

In the Sub query you may use the different operators to filter out the result like [=, >, =, <=, !=, ]. These Sub queries can be used conjunction with INSERT, UPDATE and DELETE queries.

Suppose you want to find the name of the department in which employee_id = 100 is currently working on.

Let’s see how this subquery is constructed & executed inside of another query:

SELECT department_name FROM department

WHERE department_id =

(SELECT department_id FROM employee WHERE employee_id = 100);

Following is the result upon executing the above SQL Sub query:

Learn SQL - Sub-Query result

So let’s discuss how the result of above query is calculated:

Learn SQL Sub-Queries

In above Row Sub-Queries, the result of INNER query can is returned only one value.

Let’s take a look at the other Sub query type who returns can be return single/multiple row(s) or column(s) i.e. Table sub-query:

Suppose you want get list of employee’s Name and Phone number who’s working in other than Quality department & date of birth is not registered in Employee tracking system.

SELECT Full_name,Phone FROM Employee

WHERE date_of_birth is NULL and department_id IN

(SELECT department_id FROM department WHERE department_name  <> ‘Quality’)

Following is the result upon executing the above SQL Sub query:

Learn SQL - Sub-Query result

So let’s discuss how the result of above query is calculated:

Learn SQL Sub-Queries

You can use multiple INNER queries inside INNER queries, the SQL supports INNER queries up to 32 levels.

In above examples we have seen INNER queries up to two levels; here we are seeing three level INNER query:

In the company higher management wants to announce the awards to highest paying employee member, so here is the query to get the name of the highest paid employee:

Select Full_name From employee WHERE Employee_id =

(SELECT Employee_id FROM payments WHERE salary =

(SELECT MAX(salary) FROM payments))

Following is the result upon executing the above SQL triple Sub query:

Learn SQL - Sub-Query result

Sub-Queries Vs Joins!

The Subqueries are simpler to write & easy to understand. As a result, Sub queries are more frequently used in the beginner’s level. The Joins are complicated but more powerful than Sub queries.

Majorly subqueries run independently and result of the subquery used in the outer query (other than correlated subquery) and in case of JOIN’s, a query only give the result when the joining condition gets satisfied.

In JOIN both the tables should have a common column name but in the subquery, without having a column name we can execute the query.

If we think in terms of the performance perspective, then the Joins are faster than the Subqueries. Using Joins, it approximately boosts the performance of the query by 500 times as compared to Subqueries. So Joins are more popular than the Subqueries & most of the SQL experts are preferred to use Joins instead of SubQueries.

Conclusion on SQL Sub-Queries:

  • Sub queries contain two parts, one is INNER query & other is an OUTER query. The result of an INNER query is passed to OUTER query as input.
  • Sub queries are simple & easy to understand. It can be easily broken down into logical steps, so it offers more flexibility.
  • The Subqueries are used in conjunction with SELECT, INSERT, UPDATE & DELETE commands.
  • In this article we have learned about three types of SQL subqueries: scalar, row and table subqueries.
  • In SQL server, The Nested query can be used up to 32 levels.
  • As compare with Joins, the performance of Subquery is low. Joins are 500 times faster than Subqueries.

For performance issues, when it comes to getting data from multiple tables, it is strongly recommended to use JOINs instead of subqueries. Subqueries should only be used for good reason. So in the next article I am covering basics of Joins & what all types of Joins offered in the SQL server.

Over to you on SQL SubQueries:

Have you worked on Database testing? If yes, I would like all of you to please join this discussion and add more valuable points to it. If not please ask questions in comment below, also don’t forget to subscribe here to get software testing articles in your inbox. Click here to subscribe to your email address or enter your email id below.

Enter your email address:Check email in your inbox for confirmation to get latest updates Software Testing for free.

Happy Testing!!!

This Post Has One Comment

  1. Awesome..
    Could you please post information about CORRELATED SUB-QUERIES

Comments are closed.

Close Menu