Share via

SQL Server: Sub Queries Overview

  • Article

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 parenthesis 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 passed 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 emp
WHERE 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 emp
WHERE AGE IN  (SELECT  AGE FROM  emp WHERE  AGE > 58 );