SUB QUERIES
===========
The query within another query is
known as subquery. subquery is also known
as nested query or inner query.
USES: to find out the unknown
value
example:
SELECT * FROM emp WHERE sal=3000;
SELECT * FROM emp WHERE
sal=(SELECT sal FROM emp WHERE ename='MARTIN');
SELECT * FROM emp WHERE
deptno=(SELECT deptno FROM emp WHERE ename='SCOTT')
AND ename<>'SCOTT';
Note: You can write subquery in
SELECT clause, FROM clause, WHERE clause,
HAVING clause, INSERT clause,
VALUES clause, SET clause and WHERE CLAUSE of UPDATE statement,
WHERE clause of DELETE statement
etc.
In WHERE clause we can write
maxmimum 255 subqueries.
In FROM clause we can write
unlimited subqueries.
When we write select statement in
the where clause of query then it is known as
subquery. when we write select
statement in the from clause of the query then it
is known as INLINE VIEW.
Then inner query will execute
first and then outer query executes.
Inner query output will be the
outer query input.
Subqueries are of two types:
Single Row Sub query(single
column, multiple column)
Multiple Row Sub query(single
column, multiple column)
select deptno from emp where
ename='SCOTT';
select deptno, job from emp where
ename='SCOTT';
select sal from emp where
job='CLERK';
select deptno, sal from emp where
job='CLERK';
Single Row Subquery: when
subquery returns only row such type of subquery
is known as single row subquery.
SINGLE - SINGLE ROW operators
(=,<>,!=,<,>,<=,>=)
SELECT * FROM emp;
SELECT * FROM dept;
-- single row single column
subquery
SELECT * FROM EMP
WHERE DEPTNO=(SELECT DEPTNO FROM
DEPT WHERE DNAME='SALES');
SELECT * FROM EMP
WHERE DEPTNO=30;
--Single Row Multi Column
subquery:
SELECT * FROM EMP WHERE (JOB,MGR)
=
(SELECT JOB, MGR FROM EMP WHERE
ENAME='ALLEN');
-- pairwise comparison
SELECT * FROM EMP WHERE (JOB,MGR)
=
(SELECT JOB, MGR FROM EMP WHERE
ENAME='ALLEN') AND ENAME!='ALLEN';
--non-pairwise comparison
SELECT * FROM EMP WHERE
JOB=(SELECT JOB FROM EMP WHERE ENAME='ALLEN')
AND MGR=(SELECT MGR FROM EMP
WHERE ENAME='ALLEN') AND ENAME<>'ALLEN';
MULTIPLE ROW: whenever any
subquery returns multiple rows, it is known as
multi row sub query.
With mulitrow subquery we should
use multirow operators (IN, NOT IN, ANY,ALL)
SELECT * FROM DEPT WHERE DEPTNO
IN (SELECT DISTINCT DEPTNO FROM EMP);
SELECT * FROM DEPT WHERE DEPTNO
IN (10,20,30);
Multirow Multicolumn subquery:
SELECT * FROM EMP WHERE
(DEPTNO,SAL) IN
(SELECT DEPTNO, MAX(SAL) FROM EMP
GROUP BY DEPTNO);
SELECT * FROM EMP WHERE
(DEPTNO,SAL) IN
((30,2850),(20,3000),(10,5000));
NOTE: when ever the subquery
returns more than one row, the output of the PAIRWISE comarison
and NON-PAIRWISE comparison can
be different.
SELECT first_name, last_name,
department_id, manager_id
FROM hr.employees WHERE
first_name='John';
FNAME LNAME DID MGR
John Chen 100 108
John Seo
50
123
John Russel 80 100
--pairwise comparison
SELECT EMPLOYEE_ID, LAST_NAME, MANAGER_ID, DEPARTMENT_ID FROM
HR.EMPLOYEES
WHERE (DEPARTMENT_ID, MANAGER_ID) IN(SELECT DEPARTMENT_ID, MANAGER_ID
FROM HR.EMPLOYEES
WHERE FIRST_NAME='John') AND FIRST_NAME<>'John';
--non-pairwsie comparison
SELECT EMPLOYEE_ID, LAST_NAME, MANAGER_ID, DEPARTMENT_ID FROM
HR.EMPLOYEES
WHERE DEPARTMENT_ID IN(SELECT DEPARTMENT_ID FROM HR.EMPLOYEES
WHERE FIRST_NAME='John')
AND
MANAGER_ID IN(SELECT MANAGER_ID FROM HR.EMPLOYEES WHERE
FIRST_NAME='John')
AND FIRST_NAME<>'John';
scalar sub queries: When the subquery returns only one column and only
one row
it is known as scalar subquery(SINGLE ROW AND SINGLE COLUMN SUBQUERY).
SELECT job, mgr FROM emp WHERE
ename='ALLEN'; -- not a scalar sub query
SELECT job FROM emp WHERE
ename='ALLEN'; -- scalar subquery
SELECT job FROM emp WHERE
deptno=10; -- not a scalar suqquery
SELECT ename,(SELECT sysdate FROM
dual) sq FROM emp;
SELECT ENAME, (SELECT DEPTNO FROM
DEPT) DNO FROM EMP; -- GIVES ERROR
SELECT (SELECT DNAME FROM DEPT WHERE
DEPTNO=10) DN, ENAME FROM EMP;
SELECT (SELECT DNAME FROM DEPT
WHERE DEPTNO=10) DN, ENAME, DEPTNO FROM EMP
WHERE DEPTNO=10;
INLINE VIEW: The subquery
written in from clause(or in place of
table name) sql statement it is known
as INLINE VIEW.
SELECT EMPNO, ENAME, SAL FROM
(SELECT EMPNO, ENAME, SAL SALARY, DEPTNO
FROM EMP WHERE DEPTNO=20) WHERE
SAL>=3000;
SELECT EMPNO, ENAME, SALARY FROM
(SELECT EMPNO, ENAME, SAL SALARY, DEPTNO
FROM EMP WHERE DEPTNO=20) WHERE
SALARY>=3000;
SELECT * FROM (SELECT EMPNO,
ENAME, SAL SALARY, DEPTNO
FROM EMP WHERE DEPTNO=20) WHERE
SALARY>=3000;
SELECT EMPNO, ENAME, SAL FROM
(SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP WHERE DEPTNO=20)
WHERE SAL>=3000;
SELECT * FROM
(SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP WHERE DEPTNO = 30) A
WHERE COMM IS NULL;
SELECT A.* FROM
(SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP WHERE DEPTNO = 30) A
WHERE COMM IS NULL;
SELECT * FROM
(SELECT EMPNO, ENAME, SAL,
DEPTNO, COMM FROM EMP WHERE DEPTNO = 30) A
WHERE COMM IS NULL;
SELECT A.* FROM
(SELECT EMPNO, ENAME, SAL,
DEPTNO, COMM FROM EMP WHERE DEPTNO = 30) A
WHERE COMM IS NULL;
-- Dt.24-Mar-2014 --
Null Values in a subquery:
Whenever null values are likely
to be part of sub query result set, do
not use the NOT IN operator.
SELECT * FROM EMP WHERE MGR
IN(SELECT EMPNO FROM EMP WHERE JOB='IT');
SELECT * FROM EMP WHERE MGR
IN(SELECT EMPNO FROM EMP WHERE JOB='ANALYST');
SELECT * FROM EMP WHERE MGR
IN(SELECT EMPNO FROM EMP WHERE JOB='SALESMAN');
SELECT * FROM emp WHERE MGR IN
(NULL);
SELECT * FROM emp
WHERE empno IN(SELECT DISTINCT
mgr FROM emp);
SELECT * FROM emp
WHERE empno NOT IN(SELECT
DISTINCT mgr FROM emp);
SELECT * FROM emp
WHERE empno NOT IN(7839, NULL,
7782, 7698, 7902, 7566, 7788);
SELECT * FROM emp
WHERE empno NOT IN(SELECT
DISTINCT mgr FROM emp WHERE mgr IS NOT NULL);
SELECT * FROM EMP
WHERE EMPNO NOT IN(SELECT
DISTINCT NVL(MGR,0) FROM EMP);
ANY operator:
=ANY, <ANY, >ANY,
<>ANY
=ANY -> gives same result as
IN operator
<ANY -> Less than the maximum
value
>ANY -> greater than the
minimum value
ALL operator:
=ALL, <ALL, >ALL
=ALL -> is not possible
<ALL -> less than the
minimum value
>ALL -> greater than the
maximum value
SELECT * FROM emp WHERE sal <
ANY (1600, 1375, 1375,1650);
SELECT * FROM emp WHERE sal
<=ANY (1600, 1375, 1375,1650);
SELECT * FROM emp WHERE sal <
ANY (SELECT sal FROM emp WHERE job='SALESMAN');
SELECT * FROM emp WHERE sal
>ANY(1760, 1375, 1250,1650);
SELECT * FROM emp WHERE sal
>=ANY(1760, 1375, 1250,1650);
SELECT * FROM emp WHERE sal
>ANY (SELECT sal FROM emp WHERE job='SALESMAN');
SELECT * FROM emp WHERE sal
<ALL(1600, 1250, 1375,1650);
SELECT * FROM emp WHERE sal <
ALL (SELECT sal FROM emp WHERE job='SALESMAN');
SELECT * FROM emp WHERE sal >
ALL (SELECT sal FROM emp WHERE job='SALESMAN');
SELECT * FROM emp WHERE sal
>ALL(1760, 1375, 1375,1650);
SELECT * FROM emp WHERE sal
<>ALL(1600, 1250, 1375,1650);
SELECT * FROM emp WHERE sal
=ALL(1600, 1250, 1375,1650); -- will not display any result
SELECT * FROM emp WHERE sal
=ANY(1600, 1250, 1375,1650);
SELECT E.ENAME, E.SAL, (SELECT
D.DNAME FROM DEPT D WHERE D.DEPTNO=10) DEPARTMENT
FROM EMP E WHERE E.SAL >=
3000;
SELECT ROWNUM, sal FROM (SELECT
DISTINCT sal FROM emp ORDER BY sal DESC)
WHERE ROWNUM <=5;
SELECT RANK, sal FROM (SELECT
ROWNUM AS RANK, sal FROM (SELECT DISTINCT sal
FROM emp ORDER BY sal DESC))
WHERE RANK=5;
corelated sub query
===================
Use: To do the row by row
processing
SELECT E.ENAME, (SELECT D.DNAME
FROM DEPT D WHERE D.DEPTNO=E.DEPTNO) DEPARTMENT
FROM EMP E;
/* DISPLAY EMPLOYEES name, deptno
and sal WHOSE SALARY IS EQUAL TO MAXIMUM SALARY IN
THEIR DEPARTMENT. */
SELECT E.ENAME, E.deptno, E.sal
FROM EMP E
WHERE E.SAL = (SELECT MAX(F.SAL)
FROM EMP F WHERE F.DEPTNO=E.DEPTNO);
/* DISPLAY ALL EMPLOYEES DETAILS
WHOSE JOINING DATE IS EQUAL TO JOINING DATE
OF MOST JUNIOR EMPLOYEE IN THEIR
PROFESSION */
SELECT E.JOB, E.ENAME, e.hiredate
FROM EMP E
WHERE E.HIREDATE =(SELECT
MAX(F.HIREDATE) FROM EMP F WHERE F.JOB = E.JOB);
Display details of employee(s)
drawing second highest salary
update emp set sal=1300 where
ename='MILLER';
select * from emp;
SELECT e.* FROM emp e
WHERE 2=(SELECT COUNT(DISTINCT
f.sal) FROM emp f WHERE e.sal<=f.sal);
select * from emp;
alter table emp add(dept_name
VARCHAR2(20));
update emp e set
dept_name=(select dname from dept d where e.deptno=d.deptno);
select * from emp;
create table table1(sno
number(4));
create table table2(stud_num number(4));
insert into table1 values(10);
insert into table1 values(20);
insert into table1 values(30);
insert into table2 values(10);
insert into table2 values(20);
insert into table2 values(60);
delete from table2 where stud_num
IN(select sno from table1);
delete from table2 t2 where
stud_num=(select sno from table1 t1
where t1.sno=t2.stud_num);
select * from table1;
select * from table2;
No comments:
Post a Comment