Reader Level:
Articles

Introduction to Sub-Queries in SQL Server

By Vithal Wadje on December 26, 2012
In this article i have explained detailed about the Sub-Queries in sql Server
  • 0
  • 0
  • 5494

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 SELECTINSERTUPDATE, 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 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.

Vithal Wadje

Vithal Wadje Currently working as Senior Software Engineer on Microsoft Technologies with BFSI Domain at one of the top MNCs in Mumbai.  He has experience in handling large financial projects involved Microsoft te... Read more

COMMENT USING

Trending up