Sub Queries in SQL Server

                                                                 SUB-QUERIES

                                                                                                                                               

A select statement nested inside another select statement is commonly referred as subquerry. 

(or)

A query contains another query  is known as sub query or nested query .

In between the inner query and outer query, first inner query is going to execute  and based on the inner query result the outer query is going to execute. 

we can write query within a query upto 256. Sub-queries are classified into 2 types.

Single Row Sub-query:

If inner query returns only one value then those queries can be called as SRS.

The operators which are using in SRS are :=,>,<,>,<=,!=,EXIST and NOTEXIST those operators can be called as single row operators.

Multi Row Sub-queries:

If the inner query returns more than one value then those queries can be called as MRS the operators which are used in MRS are : IN,ANY,ALL and SOME.

ANY and SOME both are equal. Those operators can be called as MR operators.

Co-Related Sub Queries:

The co-related sub queries are special type of subqueries.in this queries first outer query is executed based on outer query result inner query will be executed

Ex:

WAQ to display employee details whose salary is less than 7566 employee salary?

SELECT *
FROM emp
WHERE sal < (
  SELECT sal
  FROM emp
  WHERE empno = 7566
  )

WAQ to display employee details whose salary is less than 7566 and name ends with 'N%'

SELECT *
FROM emp
WHERE sal < (
  SELECT sal
  FROM emp
  WHERE empno = 7566
  )
 AND ENAME LIKE '%N'

WAQ to display employee details who are working in CHICAGO?

SELECT *
FROM emp
WHERE deptno = (
  SELECT deptno
  FROM dept
  WHERE loc = 'CHICAGO'
  )

WAQ to display 2 nd highest salary from employee details?

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

WAQ to display employee details who are working in Account and Research department?

SELECT *
FROM emp
WHERE deptno IN (
  SELECT deptno
  FROM dept
  WHERE dname = 'Accounting'
   OR Dname = 'Research'
  )