Subquery in SQL Server | Types of Subquery

Hi Programmers,welcome to new article of ozanecare. this article i’ll write the queries for subquery in Microsoft SQL Server. SUB-QUERIES : If we write select statement in where Clause that can be called it as sub queries or inner queries.
Four Types of SUB-QUERIES.
1.Single ROW SUB-QUERIES
2.Multi ROW SUB-QUERIES
3.Multiple Column SUB-QUERIES
4.Correlated SUB-QUERIES
, Let’s see the queries.

1.Single ROW SUB-QUERY
It returns zero or one row to the outer SQL statement.
we can use WHERE,HAVING clause with subquery.
SELECT * FROM EMP;
SELECT * FROM EMP
WHERE SAL = ( SELECT SAL FROM EMP
WHERE ENAME=’JONES’);
Single Row Subquery with <>, >, <, <= operators it can used with a single subquery SELECT * FROM EMP WHERE SAL > ( SELECT SAL FROM EMP
WHERE ENAME=’JONES’);

example 1
example 2
example 3
example 4

2.Multi ROW SUB-QUERIES
It returns one or more rows. we should include operations like any,all,in,not in,between etc.
ANY operator to compare a value with any value in list.
you must place an =, <>, >,<,<=,>= operators before any in your query.
SELECT * FROM EMP;
SELECT * FROM EMP
WHERE SAL ANY ( SELECT SAL FROM EMP
WHERE SAL BETWEEN 3000 AND 5000);
SELECT * FROM EMP
WHERE SAL IN ( SELECT SAL FROM EMP
WHERE SAL BETWEEN 3000 AND 5000);

3.Multiple Column SubQuery :The subqueries that return multiple columns is called
Multiple Column SubQuery.
SELECT * FROM EMP;
SELECT DEPTNO,AVG(SAL) SAL1 FROM EMP
GROUP BY DEPTNO;
SELECT * FROM EMP E1,
( SELECT DEPTNO,AVG(SAL) SAL1 FROM EMP
GROUP BY DEPTNO) E2
WHERE E1.DEPTNO = E2.DEPTNO
AND E1.SAL > E2.SAL1;

4.correlated subquery is a sub-query that uses values from the outer query.
In this case the inner query has to be executed for every row of outer query and
subquery depends on the outer query for its value.
SELECT * FROM EMP
WHERE SAL IN ( SELECT SAL FROM EMP
WHERE SAL BETWEEN 3000 AND 5000);
SELECT * FROM EMP E1
WHERE SAL > ( SELECT AVG(SAL) FROM EMP E2
WHERE E2.DEPTNO =E1.DEPTNO
GROUP BY E2.DEPTNO);
SELECT * FROM EMP E1,
( SELECT DEPTNO,AVG(SAL) SAL1 FROM EMP
GROUP BY DEPTNO) E2
WHERE E2.DEPTNO =E1.DEPTNO
AND E1.SAL > E2.SAL1;

example 1
example 2
example 3

Happy Coding…Thanks.

Post Author: adama