#nested query

New Post has been published on http://www.softwaretestingclass.com/sql-sub-queries/

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 is totally depends on the result of sub query. First the INNER query gets executed & the result of 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:

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 sub query 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 above query is calculated:

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:

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

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 managements wants to announce the awards to highest paying employee member, so here is the query to get the name of the highest paying 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:

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 sub queries run independently and result of the sub query used in the outer query (other than correlated sub query) 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 sub query without having a column name we can execute the query.

If we think in terms of the performance prospective, then the Joins are faster than the Sub queries. Using Joins, it approximately boosts the performance of query by 500 times as compare to Sub queries. So Joins are more popular than the Sub queries & 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 OUTER query. The result of 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 Sub queries are used in conjunction with SELECT, INSERT, UPDATE & DELETE commands.

In this article we have learnt about three types of SQL supb queries: scalar, row and table sub queries.

In SQL server, The Nested query can be used up to 32 levels.

As compare with Joins, the performance of Sub query is low. Joins are 500 times faster than Sub queries.

For performance issues, when it comes to getting data from multiple tables, it is strongly recommended to use JOINs instead of sub queries. Sub queries should only be used with 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 forgot to subscribe here to get software testing articles in your inbox. Click here to subscribe with your email address or enter you email id below.

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