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.
- Single Row
- Multiple Row
- 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
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 );
2 comments
Good One!
Replythanks sir
ReplyPost a Comment