SUBQUERIES IN SQL SERVER

• A QUERY IN THE WHERE CLASS IS A KNOWN AS SUBQUERY OR NESTED QUERY OR INNER QUERY
• SUB QUERY WOULD BE EXECUTED FIRST ,THEN THE RESULT PASSED TO OUTER QUERY ,FINALLY OUTER QUERY WILL BE EXECUTED.
• MAX 32 LEVELS OF NESTING IS ALLOWED IN SUBQUERY

SYNTAX:

                    SELECT COLUMN_NAME [, COLUMN_NAME 
                    FROM   TABLE1 [, TABLE2 ]

                    WHERE  COLUMN_NAME  [COPARISION_OPERATOR]

                    (SELECT COLUMN_NAME [, COLUMN_NAME ]

                     FROM TABLE1 [, TABLE2 ]

                     [WHERE])

---ABOVE BLUE COLOR CODE IS SUB QUERY OR "INNER QUERY"

----PURPLE  COLOR CODE IS 'OUTER QUERY'

• SUBQUERIES CAN BE USED WITH THE SELECT, INSERT, UPDATE, AND DELETE STATEMENTS ALONG WITH THE OPERATORS LIKE =, <, >, >=, <=,  BETWEEN ETC.

COMPARISION OPERATOR:

"=,<,>,<=,>="      WHEN SUBQUERY RETURNS A SINGLE VALUE.(SINGLE COLUMN)
"="  ------ EQUALS
"<"---------LESS THAN
">"--------GREATER THAN
"<="------LESS THAN OR EQUAL TO
">="------GREATER THAN OR EQUAL TO
"< >"-----NOT EQUAL TO
"!="-----NOT EQUAL TO
"!<"----NOT LESS THAN
"!>"---NOT LESS THAN

THE RESULT OF A COMPARISON OPERATOR HAS THE BOOLEAN DATA TYPE. THIS HAS THREE VALUES: TRUE, FALSE, AND UNKNOWN. EXPRESSIONS THAT RETURN A BOOLEAN DATA TYPE ARE KNOWN AS BOOLEAN EXPRESSIONS.

EXAMPLES:

SELECT * FROM EMP WHERE SAL= (SELECT MAX(SAL) FROM EMP)

SELECT * FROM EMP WHERE SAL= (SELECT MIN(SAL) FROM EMP)

SELECT * FROM EMP WHERE SAL>(SELECT MIN(SAL) FROM EMP)

SELECT * FROM EMP WHERE SAL<=(SELECT AVG(SAL) FROM EMP )

SELECT * FROM EMP WHERE SAL>=(SELECT AVG(SAL) FROM EMP)

TOP2 SAL:

SELECT * FROM EMP WHERE SAL=(

SELECT  MAX(SAL) FROM EMP  WHERE SAL<(SELECT MAX(SAL) FROM EMP))

TOP3 SAL

SELECT * FROM EMP WHERE SAL=(

SELECT MAX(SAL) FROM EMP  WHERE SAL<(SELECT MAX(SAL) FROM EMP

WHERE SAL<(SELECT MAX(SAL) FROM EMP)))

IN & NOT IN:  WHEN SUBQUERY RETURNS MULTIPLE VALUES.(SINGLE COLUMN)

TOP 2 SAL:

SELECT  * FROM EMP WHERE  SAL=(   SELECT MIN(SAL) FROM EMP
                                       WHERE SAL IN(SELECT DISTINCT TOP 2 SAL FROM EMP))

TOP 3 SAL:

SELECT  * FROM EMP WHERE  SAL=(   SELECT MIN(SAL) FROM EMP
                                       WHERE SAL IN(SELECT DISTINCT TOP 3 SAL FROM EMP))

• IF THE VALUE OF TEST_EXPRESSION IS EQUAL TO ANY VALUE RETURNED BY SUBQUERY OR IS EQUAL TO ANY EXPRESSION FROM THE COMMA-SEPARATED LIST, THE RESULT VALUE IS TRUE;OTHERWISE, THE RESULT VALUE IS FALSE.

• USING NOT IN NEGATES THE SUBQUERY VALUE OR EXPRESSION.

RISTRICTIONS IN SUB QUERIES:

• SUBQUERIES MUST BE ENCLOSED WITHIN PARENTHESES.
• A SUBQUERY MUST INCLUDE A SELECT CLAUSE AND A FROM CLAUSE
• A SUBQUERY CAN INCLUDE OPTIONAL WHERE, GROUP BY, AND HAVING CLAUSES.
• YOU CAN INCLUDE AN ORDER BY CLAUSE ONLY WHEN A TOP CLAUSE IS INCLUDED.
• THE BETWEEN OPERATOR CANNOT BE USED WITH A SUBQUERY; HOWEVER, THE BETWEEN OPERATOR CAN BE USED WITHIN THE SUBQUERY.