SQL - Structured Query Language

SQL Subquery

Subquery is a query that is nested inside a SELECT, INSERT, UPDATE or DELETE SQL Statement, or inside another subquery. Subquery can define within SELECT Column(s) expression or else WHERE / HAVING clause expression. Each subquery must be declares with open and close parentheses operators.
Format of Subquery

-- Format of Subquery --

SELECT (subquery) FROM Table

SELECT Column(s) FROM Table WHERE expression (subquery)

SELECT Column(s) FROM Table WHERE expression ALL (subquery)

SELECT Column(s) FROM Table WHERE expression ANY (subquery)

SELECT Column(s) FROM Table WHERE expression SOME (subquery)

SELECT Column(s) FROM Table WHERE [NOT] EXISTS (subquery)

SELECT Column(s) FROM Table WHERE expression [NOT] IN (subquery)

If a subquery declared within SELECT statement is called INNER subquery or else a subquery declared in WHERE clause expression is called OUTER subquery. The following INNER subquery is used to find out Department wise maximum salary information.

-- Example 50 --

USE EBusiness

SELECT DEPT.Dept_Code, DEPT.Dept_Name,

�� (SELECT MAX(EMP.Salary) FROM EMP

�� WHERE DEPT.Dept_Code = EMP.Dept_Code) AS 'Max Salary'

FROM DEPT

Query Output Screen

Basic Rules of Subquery
The select list of a subquery introduced with a comparison operator can include only one expression or column name (except that EXISTS and IN operator on SELECT * or a list, respectively).
If the WHERE clause of an outer query includes a column name, it must be join compatible with the column in the subquery select list.
The ntext, text, and image data types cannot be used in the select list of subqueries.
Because they must return a single value, subqueries introduced by an unmodified comparison operator (one not followed by the keyword ANY or ALL) cannot include GROUP BY and HAVING clauses.
The DISTINCT keyword cannot be used with subqueries that include GROUP BY.
The COMPUTE and INTO clauses cannot be specified.
ORDER BY can only be specified when TOP is also specified.
A view created by using a subquery cannot be updated.
The select list of a subquery introduced with EXISTS, by convention, has an asterisk (*) instead of a single column name. The rules for a subquery introduced with EXISTS are the same as those for a standard select list, because a subquery introduced with EXISTS creates an existence test and returns TRUE or FALSE, instead of data.
More Reference URL: http://technet.microsoft.com/en-us/library/ms189543%28v=sql.105%29.aspx

* * * * *

Your Comment To AUTHOR