Recent Posts

Sunday, 3 July 2016

SQL 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 dataset. The MINUS operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.
E.g
select deptno from emp minus select deptno from dept;

Next Tutorial  Synonym Tutorial

Previous Tutorial  Data Control Language Tutorial

No comments:

Post a Comment