Introduction to Sub-Queries in SQL Server

Background

Most of the time in interviews, one question that might be asked is: What are Sub-Queries in SQL Server? In consideration of that requirement I have written this article by focusing on how to provide the answer of that specific question that might be asked about sub-queries.
 

So let us start with the basics.
 

What are Sub-Queries ?

A SQL query written within another query enclosed with paranthesis is called a Sub-Query or inner query. When you write a sub query, the SQL engine executes the subquery first.

 

A subquery is created in an existing SELECT, INSERT, UPDATE, or DELETE statement along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.. The parent query that contains the inner statement is also called an outer query.
 

When you write a Sub Query the following rules must used:

1. A sub-query must be enclosed in parenthesis.
2. A sub-query must be put in the right hand of the comparison operator.
3. A sub-query cannot contain an ORDER-BY clause but you can include an ORDER BY clause in a sub-query only when a TOP clause is included.
4. You can write up to 32 subqueries in one SQL Statement.
5. The BETWEEN operator cannot be used with a subquery; however, the BETWEEN can be used within the subquery.
6. The column involved in the subquery cannot be of type varchar(max), nvarchar(max), or varbinary(max).

Types of Sub-Query 

1. Single Row

 

This sub query returns only one row. Such as scalar subquery, which returns a single row with one column. Scalar subqueries are often very useful in any situation where you could use a literal value, a constant, or an expression.
 

If the comparison operator is any of the ones in the following the subquery must be a single-row subquery.
 

Symbol	Meaning
=	equal to
>	greater than
>=	greater than equal to
<	Less than
<=	Less than equal to
<>	not equal to

 

e.g.
 

select MAX (salary) as Salary from employee

where salary < 

(  select MAX (salary) as Salary from employee   )
 

The above subquery returns the single row value.
 

2. Multiple Rows
 

This is a sub query that returns multiple rows. These queries are commonly used to generate result sets that will be ed to a DML or SELECT statement for further processing. Both single-row and multiple-row subqueries will be evaluated once, before the parent query is run. Single- and multiple-row subqueries can be used in the WHERE and HAVING clauses of the parent query.

 

The operators in the following table can use multiple-row subqueries:
 

IN	equal to any member in a list
Not IN	not equal to any member in a list
ANY	returns rows that match any value on a list
ALL	returns rows that match all the values in a list

 

3. Multiple columns
 

This sub-query returns multiple columns.
 

Now let us see how to use Sub queries with various SQL Statements.
 

Sub queries using a Select Statement
 

The sub queries are most commonly used with Select statements.

 

Syntax 

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR(SELECT column_name [, column_name ]FROM table1 [, table2 ][WHERE condition])

Example

SELECT *   FROM emp WHERE ID IN (SELECT ID  FROM empWHERE SALARY > 10498) ;

Sub queries using an Insert Statement
 

The following is the syntax using the Insert Statement:

INSERT INTO table_name [ (column1 [, column2 ]) ] SELECT [ *|column1 [, column2 ]FROM table1 [, table2 ][ WHERE VALUE OPERATOR ]

Example

INSERT INTO #tmp SELECT * FROM emp  WHERE code IN (SELECT codeFROM emp) ;

Sub queries using Update Statement
 

The Sub queries can also be used with the Update Statements, the following is the syntax:

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ](SELECT COLUMN_NAME FROM TABLE_NAME)[ WHERE) ]

Example

UPDATE empSET SALARY = SALARY * 0.25 WHERE exeperience IN (SELECT experience FROM empWHERE experience >= 3 )

Sub queries using delete Statement

 

The most amazing task using a sub query is to use it with a delete statement. The following is the syntax used with a delete statement:

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ](SELECT COLUMN_NAME FROM TABLE_NAME)[ WHERE) ]

Example

DELETE FROM empWHERE AGE IN (SELECT AGE FROM emp  WHERE AGE > 58 );

Summary

I hope this small article is useful for beginners and job seekers, if you have any suggestion then please contact me.