SUB-QUERIES

SUB-QUERIES

 A Sub Query is also a query, which is defined under a main query .It always sends values to its nearest main query. Server first sub query first based on the sub query value main query will get executed.

If a sub query send single value to its nearest main query then that sub query is called

Single-Valued-Sub query

If a sub query sends multiple values to its nearest main query then that sub query is called

Multi-Valued-Sub query

Note: If a sub query sends multiple values to its nearest main query then we have to use IN operator between Main query and Sub query.

EMPNO       ENAME                SAL                   DEPTNO     

----------- -------------------- --------------------- -----------

11          RAM                       15000.0000            10

22          RAJ                         20000.0000            20

33         ABI                           10000.0000           10

44         DP                               5000.0000           20

55       NAAG                           5000.0000           10

Syntax:

SELECT * FROM TABLENAME

WHERE COLUMNNAME = (SELECT STATEMENT WHERE CONDITION)

E.g.: WAQ To display employee details, who are working under RAM dept.

SELECT * FROM EMP

WHERE DEPTNO= (SELECT DEPTNO FROM EMP WHERE ENAME=’RAM’)

E.g.: WAQ To display employee details, whose salary is greater than highest salary of 10^th department

SELECT * FROM EMP

WHERE SAL> (SELECT MAX (SAL) FROM EMP WHERE DEPTNO=10)

E.g.: WAQ To display employee details, whose salary is greater than average salary of RAM department

SELECT * FROM EMP

WHERE SAL> (SELECT AVG (SAL) FROM EMP WHERE DEPTNO=

                            SELECT DEPTNO FROM EMP WHERE ENAME=’RAM’)