Module 3: Sub-Queries

Previous Tutorial

In SQL Server, many queries can be returned in more than one way, under different conditions, different choices may be preferable. Traditional SQL statements returned for other database products often use sub-queries. The sub-queries is a query that is nested inside a select statement or another sub-queries. You can use subqueries in select insert update and delete statements, whatever expressions are allowed. SQL Server is flexible enough to provide a different way of doing the same thing. So almost whatever you want to do with subquery can also be done using SQL join. It just a matter of choice.

Sub-query seems more intuitive to many users. But remember one thing that in terms of performance SQL joins are most efficient than a sub-query. This is the general syntax of subqueries. Here this subquery is also called an inner query or inner select. While the statement containing a sub-query is called an outer query or outer select statement. The select query of sub-query is always enclosed in parentheses. The Inner query executes first before its outer query. The result of an inner query can be passed to the outer query. A subquery is usually added within the WHERE clause, but you can define it in the SELECT clause, FROM clause, and WHERE clause. We will see this practice in a few minutes.

There are two basic types of subqueries available in SQL Server. The first one is the self-contained subquery and the second one is the correlated subquery. A self-contained subquery is a query that has no dependency on the outer query. It means if you want, then you can highlight this inner query in SQL Server Management Studio and you can run it independently. While in correlated subquery which is also known as repeating subquery is the one that depends on the outer query for specific values. It conditionally filtered rows based on the inner query. This is particularly important if your outer query returns multiple rows. They are trickier to work with compared with the self-contained subqueries because you can’t run this subquery independently as it has reference to a column from the table in the outer query.

The important thing to keep in mind with the correlated subquery is that the subquery is executed for each row returned by the outer query. The correlated subquery then uses a value supplied by the outer query to return its main result. A subquery can return different forms of results. It can return a single value, multiple values in a single column, or even a multi-column table result. You can use the comparison operator such as greater than less than or equal to operator to compare with the expression. You can also use a multiple row comparison operator such as IN operator, ALL operator, or ANY operator. Whenever you want to compare your column to a list of values rather than a single value, then in that case you can use IN operator or ALL operator, etc.

We will see this practice in a few minutes.

SQL Subqueries

Subqueries have some basic rules which we need to consider whenever we write the query by using the subquery. The first rule is subquery must enclose in parentheses. That means whenever you write a subquery, it must be under the parentheses. A subquery must include the SELECT clause and FROM clause. The third one, subquery includes an optional WHERE clause, GROUP by clause, and HAVING clause. It means as per the business requirement, you can use the group by clause and having a clause in the subqueries.

You can also use an order by clause in the subquery but for that, you must have to use the top clause. The fifth one is a subquery that can nest up to thirty-two levels. It means you can write query within a query up to thirty-two levels. Now let’s understand each type of subqueries practically in SQL server management studio.

Here I have defined the two queries. The first query will return the product information such as the product name, color, and weight from the product table, and the next query will give me the highest list price value from the product table. As I said earlier, you can use a comparison operator such as greater than less than or equal to operator to compare our column value to a value return by the subquery. Over here I’m going to use the equal to operator to compare the list price value in the product table to the value returned by a subquery. Now redefine this query as I want to compare the highest list price value in this query.

So in the WHERE clause, I need to define list price equal to, P must be independent. Let’s query in parentheses to make it subquery. When I execute this query, then it will give the product information whose list price is the highest value in the whole product table. This subquery returns the Max list price from the product table and then that value from the subquery is then compared to the list price value in the product table. If the two values are equal, the rows are returned in the output. This final output contains the information about products of the highest list price value.

SQL Subquery

Let’s move on to the next requirement. I want to compare one column to a list of values rather than a single value. To see that demo we will use the IN operator. In the IN operator the column value is compared to the list and the WHERE expression evaluates to true. If any of this subquery value matches the column value and the row is included in the outer query result.

Now see how we can use the IN operator to compare the list. I have defined the two queries. This time my requirement is I want to list out those employees whose job title is marketing specialist. But this person table and employee table are related to each other by this business entity ID column. So when we define the IN operator, I have to use this business entity ID column.

Redefine this query to use the IN operator. First, define the WHERE clause in the bracket, we will use the subquery then we complete the bracket. In this subquery, I have to only use the single column, remove the JobTitle column. Now, if I execute this query, it will give those employees whose job title is marketing specialist. In this query, the business entity ID column value from the outer query is compared to the list of ID values returned by the subquery. This is how we can use this IN operator over here. To use the IN operator, it is not necessary to define this type of subquery every time. I can also use the single scalar value or multiple scalar values in the IN operator.

For example, in this query, I want to find out the number of employees whose job title is finance manager and accountant. Let’s see how we can use the IN operator over here to get that information. For that, let me define a WHERE clause, Job title IN (‘Finance Manager’, ‘Accountant’)

Subqueries

A subquery can also be used in the FROM clause to return multiple rows and columns. For that, let us move to the next query. A subquery can also be used in the FROM clause to return multiple rows and columns. The results returned by such a subquery are referred to as a derived table. A derived table is useful when you want to work with a subset of data from one or more tables without needing to create a view or temporary tables. Derived tables can be used in joins and subqueries also. Here I have defined one simple query. If I execute this query, then it will give the two rows, first name, and last name.

Now, to convert this query into a derived table here I need to define another SELECT statement and then I will define parentheses then I will complete parentheses, and then define an alias. The query defined within these parentheses is called derived tables. Now this derived table I can use with the join and another subquery. For that, let me define another query.

This time I want to compare this FastName column value with this derived table, first name column value. For that in and make it subquery, in the subquery, I cannot use star. Let’s define this column name and execute this query. Any of this name here is not present in this person table, so I have not got the data. But instead of Ron, let’s see if I write and execute the query then I will get the data because this name is present in this person table. This is a simple example of the derived table. Let’s understand the concrete example from this adventure work database.

Subqueries SQL

Move to another query. I have defined the two queries. The first query will give the product information with its subcategory ID. My requirement is this subquery will retrieve the product subcategory information from this product-subcategory table, but only for those products that include the word helmet. This time, I am not going to use the IN operator over here, but I’m going to join these two queries using the inner join on this two-column subcategory product-subcategory ID. For that, let me use the inner join, and to make this query as a derived table, I have to use the parentheses. Define the parentheses and let us give the alias. Here I have to define the ON clause product category ID equal to product-subcategory ID.

I cannot execute this query because the same column name is defined in the outer query as well as in the derived table query. To overcome this problem, I have to use the alias name and execute this query. This is how you can use this derived table with the inner join to combine these two queries. Let’s understand the example of correlated subqueries. These correlated subqueries depend on the outer query for a specific value. It conditionally filters rows based on the inner query.

I have defined the two queries. My requirement is I want to find all the sales orders that were returned by those salespeople whose last year’s sales is greater than 20 lac. I’m going to use the IN operator over here. But before that, the sales order header table and salesperson table are related to each other by this business entity ID column and the sales order header contains the salesperson ID column. In the WHERE clause salesperson ID column in the IN operator. The correlated subquery depends on the outer query for specific values and it conditionally filters rows based on the inner query.

Now, if I execute this query, then it will give all these sales orders that were returned by those salespeople whose last year’s sales is greater than 20 lac. This is how the correlated subquery works. That’s it from the subqueries and with this, we have completed module three.

Web API for developers Free Trial Offline SDK

Here’s SQL video tutorial:

Other useful tutorials: