What is SQL Subquery?

SQL Subquery is the queries that are embedded inside another query. The embedded queries are called as INNER query & the container query is called an OUTER query.

The subqueries are the queries that 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 Subquery Syntax:

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

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

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

In the Subquery you may use the different operators to filter out the result like [=, >, =, <=, !=, ]. These Sub queries can be used in 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:

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

In the above Row Subquery, the result of the INNER query can is returned to only one value.

Let’s take a look at the other Sub query type who returns can return single/multiple rows or columns i.e. Table subquery:

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 the 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:

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

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

In the 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:

SQL Subquery Vs Join!

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

Majorly subqueries run independently and result in the subquery used in the outer query (other than correlated subquery) and in the case of JOIN’s, a query only gives 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 SubQuery.

Conclusion on SQL Subquery:

• Subqueries contain two parts, one is INNER query & the other is an OUTER query. The result of an INNER query is passed to the OUTER query as input.
• Subqueries 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 the 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 in Subquery.

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 reasons. So in the next article, I am covering the basics of Joins & what all types of Joins offered in the SQL server.

Over to you on SQL SubQuery:

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.

---

⇓ Subscribe Us ⇓

---

If you are not regular reader of this website then highly recommends you to Sign up for our free email newsletter!! Sign up just providing your email address below:

Enter your email address:

 

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

  Happy Testing!!!