Post
Ask Question

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.


Similar Articles