Sub Queries in SQL Server

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 sub query first. A sub query is created on an existing select, insert, update or delete statement along with the operators. 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:
  • Sub-query must be enclosed in parenthesis.
  • Sub-query must be put in the right hand of the comparison operator.
  • 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.
  • You can write up to 32 sub queries in one SQL Statement.
  • The BETWEEN operator cannot be used with a sub query; however, the BETWEEN can be used within the sub query.
  • The column involved in the sub query cannot be of type varchar (max), nvarchar (max), or varbinary (max). There are three types of sub queries.
      1. Single Row
      2. Multiple Row
      3. Multiple columns

Single Row

This sub query returns only one row. Such as scalar sub query, this returns a single row with one column. Scalar sub queries 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 then sub query must be a single-row sub query

operator
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 )

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 sub queries will be evaluated once, before the parent query is run. Single- and multiple-row sub queries can be used in the WHERE and HAVING clauses of the parent query.
The operators in the following table can use multiple-row sub queries:


operator
Meaning
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

Multiple columns

This sub-query returns multiple columns. Now let us see how to use Sub queries with various SQL Statements. The sub queries are most commonly used with Select statements.

Using a select statement.

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) ;

Using 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 code FROM emp) 

Using update statement

SET column_name = new_value
[ WHERE OPERATOR [ VALUE ](SELECT COLUMN_NAME FROM TABLE_NAME)[ WHERE) ]
Example
UPDATE empSET SALARY = SALARY * 0.25 WHERE experience IN (SELECT experience FROM emp WHERE experience >= 3 )

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 );

Summary

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

2 comments

Post a Comment

www.CodeNirvana.in

Protected by Copyscape
Copyright © Compilemode