Sub-Queries in SQL Server

A subquery is a SQL query that runs within another query. They are nested inquiries that offer data to the query that comes before it. Individual values or a list of records can be returned via subqueries. In SQL, a subquery is a select expression surrounded by parentheses and used as a nested query block in a query statement.

There are a few rules to be noted:
1) These subqueries can only be used on the right side of an expression.
2) Subqueries using IN/NOT IN operators can only choose a single column.
3) EXISTS/DOES NOT EXIST must be followed by one or more correlated predicates.
4) References to the parent query are only permitted in the subquery’s WHERE clause.

Please remember that RDBMSes were never very good at sub-queries; instead, I advise utilizing JOINS whenever it is practical.

Here you will get the popular “EMP” and “DEPT” table definitions and data to practice the subqueries.

Using Exists:
SELECT * FROM dept WHERE EXISTS (
SELECT emp.deptno FROM emp WHERE emp.deptno=dept.deptno );

This returns the following output:
DeptNo   DName              Loc
10             ACCOUNTING    NEW YORK
20             RESEARCH          DALLAS
30             SALES                  CHICAGO

Using NOT Exists:
SELECT * FROM dept WHERE NOT EXISTS(
SELECT emp.deptno FROM emp WHERE emp.deptno=dept.deptno);

This returns the following output:
DeptNo   DName              Loc
40              OPERATIONS    BOSTON

Using IN:
SELECT * FROM emp WHERE emp.deptno IN(
SELECT deptno FROM Dept WHERE loc=’DALLAS’);

This returns the following output:
EmpNo   EName      Job                  Mgr       HireDate           Sal     Comm   DeptNo
7566        JONES        MANAGER      7839      04/Feb/1981     2975            0    20
7788        SCOTT       ANALYST         7566      13/Jul/87           3000            0    20
7902        FORD         ANALYST         7566      03/Dec/1981    3000            0    20
7369        SMITH       CLERK              7902      17/Dec/1980      800            0    20
7876        ADAMS     CLERK              7788      13/Jul/87           1100            0    20

Using NOT IN:
SELECT * FROM emp WHERE emp.deptno NOT IN(
SELECT deptno FROM Dept WHERE loc=’DALLAS’);

This returns the following output:
EmpNo   EName      Job                  Mgr       HireDate           Sal     Comm   DeptNo
7839        KING          PRESIDENT           0      17/Nov/1981     5000           0      10
7698        BLAKE        MANAGER     7839       01/May/1981    2850           0      30
7782        CLARK        MANAGER     7839       06/Sep/1981     2450           0      10
7499        ALLEN        SALESMAN    7698       20/Feb/1981     1600       300      30
7521        WARD        SALESMAN    7698       22/Feb/1981      1250       500     30
7654        MARTIN     SALESMAN    7698       28/Sep/1981      1250     1400     30
7844        TURNER    SALESMAN     7698       09/Aug/1981     1500            0     30
7900        JAMES       CLERK              7698       03/Dec/1981       950            0     30
7934       MILLER      CLERK              7782       23/Jan/1982      1300            0     10

Hope you find this article helpful.

Please subscribe for more interesting updates.