SUB QUERIES IN SQL SERVER

SUB QUERIES IN SQL SERVER

A statement that request data from database is called query.

As select statement is used to request the data from database, select statement is called query.

A select statement that was written within another select statement is called as sub query.

In, this there are two queries one query is called inner query and another query called outer query.

When it is executed, first inner query is executed then outer query.The result of inner query acts as input to outer query.

Types of sub query

Standard sub queries

Derived tables

Scalar sub queries

Standard sub queries

It follows where clause or standard sub queries

Outer query can be insert, update, delete, select

Inner query must be always select

Types of standard sub queries

1 single row sub query   ->[(

2 multi row sub query-> [((

3 nested queries->[((

4 co related sub queries->[((

Single row sub query

A sub query that returns a single row is called as single row sub query.

Operator must be

>,>=,<,<=,=,<>

Find the employees working in sales dept.

select * from emp where deptno=(select deptno from dept where dname='sales')

Display employee record whose job is equal to job of smith

select * from emp where job=(select job from emp where ename='smith')

Display employee names earning maxsalary

select ename from emp where sal=(select max(sal) from emp)

Display name of employees having max experience

select ename from emp where hiredate=(select min(hiredate) from emp)

Display employee records who’s job=job of smith and Sal must be >smith

select * from emp where job=(select job from emp where ename='smith')

and

sal>(selectsal from emp where ename='smith')

Multi row sub query

If the inner query returns more than one value then the sub query is called “multi-rowsubquery”.

Syntax:

Select <collist> from <table name>

Where column name op (select statement)

Here op must be in, not in, any, all, exists, not exists, etc.

Note:

Operator like equal and not equal, >,>=, <, <= cannot use with multi row sub query.

Because multi row sub query returns rows, but these operators are used to compare with a single value. There are situations where you have to use these operators with multi row sub query and for this situation sql server provides a predicates

1 any or some

2 all

The predicate any returns true when the given condition is true any one value returned by multi row sub query

and

The predicate All returns true when the given condition is true with all the values returned by multi row sub query.

Examples

Find the employees who are not working in dept no 30 and those salary is more than the salary of one employee working in dept no 30.

select * from emp where deptno !=30 and sal>any(select distinct sal from emp where deptno=30).

Or

select * from emp where deptno!=30 and sal>(select min(sal) from emp where deptno=30)-- but this is not multi row subquery this is single row sub query.

Find the employees who are not working in department 30 and whose salary is more than the salary of all employees in deptno 30.

select * from emp where deptno!=30 and sal >All(select distinct sal from emp where deptno=30)

or using single subquery

select * from emp where deptno !=30 and sal>(select max(sal) from emp where deptno=30)

Display Employee Records who jobs =job ofsmith or job of blake

select * from emp where job in (select job from emp where ename='smith' or ename='blake')

Display department which is empty

select deptno from dept where DEPTNO not in (select deptno from emp)

Note

Any: used to compare any of the values in the given list

All: used to compare all values of given list

Display Employee Records who are earning more than All Clerks

select * from emp where sal >all(select sal from emp where job='clerk')

Nested Sub Queries

A sub query that was written within another sub-query is called Nested Sub Query

Get the names of employees earning 2 nd maximum salary

select ename from emp where sal =(select max(sal) from  emp where sal< (select max(sal) from emp))

Display names of department employees earning 2 nd maximum salary

select dname from dept where DEPTNO in (select deptno from emp where sal=(select max(Sal) from emp where sal=(select max(Sal) from emp)))

Find the employees whose salary is more than average salary of sales dept

select * from emp where sal>(Select avg(Sal) from emp where deptno=(select DEPTNO from DEPT where dname='sales'))

Sub Queries with Update Command

Update the employee to max salary where eno=1001

update empset sal=(select max(Sal) from emp) where EMPNO=1001

Update the employee salary to max salary of ‘sales’ department whose eno=1001

update empset sal=(Select max(sal) from emp where deptno =(select deptno from dept where dname='sales')) where empno=1001

Sub Queries with Delete Command

Delete all Employee Records whose job=job of smith

delete from emp where job=(select job from emp where ename='smith')

Correlated Sub Queries

When a sub query refers to a column of Main Query within the condition in “where clause” than it is called as CO RELATED sub queries

 In co related sub queries execution starts from outer query for every record of outer query table inner query is executed once.

Display employee records earning more than avg salary of their Dept.

select * from emp a where a.sal>(select avg(Sal) from emp where DEPTNO=a.DEPTNO)

Find the employees whose salary is more than average salary of same department in which that department is working

select * from emp a where sal>(select avg(sal) from emp s where s.DEPTNO=a.deptno)

In co-Related Sub Queries we use Two options

Exists

Non Exists

These are the predicates that are generally used with co-related sub query and they are used to determine whether or not the sub query returns at least one-Row

The predicate Exists will return true, when the sub query Return any Rows and otherwise it Returns False

NonExists will return true, when the sub query does not return any Rows and otherwise it returns False

Find the employees to whom atleast one of employee is reporting

select * from emp m where exists(select * from emp e where e.mgr=m.EMPNO)

Display the details of departments in which they are no employees

select * from DEPT d where not exists(select * from emp e where e.DEPTNO=d.DEPTNO)

Display the details of departments in which are not empty

select * from DEPT d where exists(select * from emp e where e.DEPTNO=d.DEPTNO)

Find the 3 highest salaried employee

select * from emp e where 2=(select count(distinct sal) from emp s where s.sal>e.sal)

Fnd the n th highest salary

select * from emp e where n-1=(select count(distinct sal) from emp s where s.sal>e.sal)

Find the employees drawing top 3 highest salaries

select * from emp e where 2>=(select count(distinct sal) from emp s where s.sal>e.sal)

Find the employee drawing 3rd lowest salary

select * from emp e where 2=(select count(distinct sal) from emp s where s.sal<e.sal)

Find the employee drawing top 3 lowest salaries

select * from emp e where 2>=(select count(distinct sal) from emp s where s.sal<e.sal)

Display the list of manager

select * from emp e where exists(select * from emp where MGR=e.EMPNO)

Note: sql server recommends use Exists, Not Exists instead of IN AND NOT IN OPERATOR

Difference between Join and Sub query

Use sub queries we get data from one table and condition based on another table

Use join operation to get data from multiple tables.

Derived Tables

This is introduced from sql server 2005

Sub queries follows from clause are called as Derived Tables

Syntax:

Select * from (select statement) as alias

Use Derived Tables to simplify the complex Processing

To use result of one query in another query

select * from (select empno,ename ,sal*12 annasal from emp ) as e where annasal>60000

Display top maximum  3 salaries in emp table

select distinct sal from (select ename, sal , DENSE_RANK() over (order by sal desc) rank from emp) as e where rank<=3

Scalar Queries

Sub queries follows select clause are called as “Scalar Queries”

Syntax:

Select (select statement)

(select statement) from <tablename>

select

(select max(sal) from emp where DEPTNO=10) D10,

(select MAX(sal) from emp where DEPTNO=20)D20,

(select max(sal)  from emp where deptno=30) D30

Sub query examples

Write a query to  find the details of employee earning the highest salary 

select *from EMP whereSAL=(select MAX(sal) from EMP)

Write a quey to find the details of employee earning the second highest salary

select * from EMP where SAL=(select MAX(sal) from EMP

where sal<(select MAX(Sal) from EMP))

Write a query to find the details of employees working in sales dept

select * from EMP where DEPTNO=(select DEPTNO fromDEPT where DNAME='sales')

Write a query to find the details of employee working in Mumbai

select * from EMP where DEPTNO=(select DEPTNO fromDEPT where loc='mumbai')

Write query to find the details of employee  who are earning more than the highest salary of deptno 30

select *from EMP whereSAL>(select MAX(Sal) from EMP where DEPTNO=30)

Write a query to find the details of employee who are earning less than the lowest salary of deptno 20

select *from EMP whereSAL< (select min(Sal) from EMP where DEPTNO=20)