SQL Tutorial : DRL / DQL - SUB-QUERIES

SQL Tutorial : DRL / DQL - SUB-QUERIES

Let's learn about next topic: SUB-QUERIES

SUB-QUERIES:   

**************

- TO EVALUATE UN-KNOW CONDITIONS

- SELECT STATEMENT THAT IS EMBEDDED IN THE

- CLAUSES OF A MAIN QUERY

* WHERE

* HAVING

* FROM

- SELECT LAST_NAME,SALARY,JOB_ID

FROM EMPLOYEES
WHERE JOB_ID =(SELECT JOB_ID

FROM EMPLOYEES WHERE EMPLOYEE_ID=121)

SINGLE ROW :

NORMAL COMPARISON OPERATORS

- SELECT LAST_NAME,SALARY

FROM EMPLOYEES

WHERE SALARY >(SELECT SALARY FROM EMPLOYEES

WHERE EMPLOYEE_ID=150)

MULTIPLE ROW :

IN ,>ANY,<ANY,=ANY , >ALL,<ALL

- SELECT LAST_NAME,SALARY,JOB_ID

FROM EMPLOYEES

WHERE SALARY IN (SELECT SALARY FROM EMPLOYEES

WHERE JOB_ID='IT_PROG')

9000

6000

4800

4200

- SELECT LAST_NAME,SALARY,JOB_ID

FROM EMPLOYEES

WHERE SALARY <ANY  (SELECT SALARY FROM EMPLOYEES

 WHERE JOB_ID='IT_PROG')

- SELECT LAST_NAME,SALARY,JOB_ID

FROM EMPLOYEES

WHERE SALARY <9000 OR SALARY <6000 OR SALARY <4800

<ANY => LESS THAN MAX

>ANY =>  GREATER THAN MIN

=ANY => SAME AS IN OPERATOR

- SELECT LAST_NAME,SALARY,JOB_ID

FROM EMPLOYEES

WHERE SALARY >ANY  (SELECT SALARY FROM EMPLOYEES

WHERE JOB_ID='IT_PROG')

- SELECT LAST_NAME,SALARY,JOB_ID

FROM EMPLOYEES

WHERE SALARY >9000 OR SALARY >6000 OR SALARY >4800

>ALL:

- SELECT LAST_NAME,SALARY,JOB_ID

FROM EMPLOYEES

WHERE SALARY >ALL  (SELECT SALARY FROM EMPLOYEES

WHERE JOB_ID='IT_PROG')

- SELECT LAST_NAME,SALARY,JOB_ID

FROM EMPLOYEES

WHERE SALARY >9000 AND SALARY >6000 AND  SALARY >4800

>ALL => GREATER THAN MAX

<ALL => LESS THAN MIN

- SELECT LAST_NAME,SALARY

FROM EMPLOYEES

WHERE EMPLOYEE_ID IN(SELECT MANAGER_ID

  FROM EMPLOYEES)

- THE FOLLOWING  QUERY WILL NOT RESULT ANY O/P

BECAUSE THE SUB-QUERY RETURNS AND NULL VALUE

AND A NOT IN OPERATOR IS USED

eg:

- SELECT LAST_NAME,SALARY

FROM EMPLOYEES

WHERE EMPLOYEE_ID NOT IN(SELECT MANAGER_ID

FROM EMPLOYEES)

- SELECT LAST_NAME,SALARY

FROM EMPLOYEES

WHERE EMPLOYEE_ID NOT  IN(SELECT MANAGER_ID

  FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL)