Sub Queries

Sub Queries
  • A query contains another query is known as nested query or sub query. We can write query into a query up to 256 queries.
  • In between the outer query and inner query, inner query executed first then it will execute the outer query .
  • Outer query is always executed descending on inner query result.

Sub queries are classified into two types.

  1. Single row sub queries.
  2. Multi row sub queries.
Single row sub query
  • If the inner query returns only one value then those queries can be called as single row sub query are equal to >,<,>=,<=,!=.  
  • These operators can be called as single row sub queries.
  • Inner query returns only one value in two classes.

Case 1: If we are using any group or aggregate functions.

Case 2: If we are using any group or aggregate functions any group or aggregate functions.

Multiple row sub queries
  • If the inner query returns more than one value then those queries can be called as multiple row sub queries. 
  • The operators which are using in multiple row sub queries are IN, ANY, ALL, SOME.

Note

  • Here ANY and SOME both are same.
  • These two operators can be called as multiple row operators.

E.g

select * from emp where sal<(select sal from emp where empno=7788);

select * from emp where sal > (select max(sal) from emp);
SET Operators

There are 4 set operators in SQL those are UNION, UNION ALL, INTERSECT and MINUS.

UNION

This operator returns all the values from all the tables excluding duplicate values.

E.g

select deptno from emp union select deptno from dept;
UNION ALL

This operator returns all the values from all the tables including duplicate values.

E.g

select deptno from emp union all select deptno from dept;
INTERSECT

The SQL INTERSECT clause/operator is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement. This means INTERSECT returns only common rows returned by the two SELECT statements.

E.g

select deptno from emp intersect select  deptno from dept;
MINUS

The SQL MINUS operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a data set. The MINUS operator will retrieve all records from the first data set and then remove from the results all records from the second data set.

E.g

select deptno from emp minus select deptno from dept;
Sub Queries
Scroll to top