compound query, SET operators

COMPOUND QUERIES USING SET OPERATORS

====================================

UNION

UNION ALL

MINUS

INTERSECT

SELECT empno, ename, sal, hiredate FROM emp;

SELECT deptno, dname, loc FROM dept;

query1={10,20,30,40,50}

query2={10,20,60,70,80}

UNION: IT WILL DISPLAY ALL THE ROWS FROM FIRST QUERY AND ALL THE ROWS FROM

SECOND QUERY BUT NO DUPLICATE ROWS, AND IT WILL SORT THE OUTPUT IN ASCENDING

ORDER BY DEFAULT BY USING THE FIRST COLUMN OF FIRST QUERY.

query1

UNION

query2

output:10,20,30,40,50,60,70,80

CREATE TABLE q1(sno NUMBEr(4));

INSERT INTO q1 VALUES(&no);

CREATE TABLE q2(no NUMBER(4));

INSERT INTO q2 VALUES(&no);

SELECT * FROM q1

UNION

SELECT * FROM q2;

UNION ALL: IT WILL DISPLAY ALL THE ROWS FROM FIRST QUERY AND ALL THE ROWS FROM

SECOND QUERY INCLUDING DUPLICATES AND IT WILL NOT SORT THE OUTPUT BY DEFAULT.

query1={10,20,30,40,50}

query2={10,20,60,70,80}

query1

UNION ALL

query2

output: 10,20,30,40,50,10,20,60,70,80

SELECT sno FROM q1

UNION ALL

SELECT no FROM q2;

INTERSECT: IT WILL DISPLAY ALL THE COMMON ROWS FROM BOTH THE QUERY BUT NOT

DUPLICATE ROWS AND IT WILL SORT THE OUTPUT IN ASCENDING ORDER BY DEFAULT BY USING

THE FIRST COLUMN OF FIRST QUERY.

query1={10,20,30,40,50}

query2={10,20,60,70,80}

query1

INTERSECT

query2

output: 10,20

SELECT sno FROM q1

INTERSECT

SELECT no FROM q2;

MINUS: IT WILL DISPLAY ALL THE ROWS FROM FIRST QUERY WHICH IS NOT AVAILABLE IN SECOND

QUERY. DUPLICATE ROWS WILL BE DISPLAYED ONCE ONLY. AND THE OUTPUT WILL BE SORTED

IN ASCENDING ORDER BY DEFAULT USING FIRST COLUMN FROM FIRST QUERY.

query1={10,20,30,40,50}

query2={10,20,60,70,80}

query1

MINUS

query2

output: 30,40,50

SELECT sno FROM q1

MINUS

SELECT no FROM q2;

Rules:

1) number of columns in both the queries should be same, name of the columns

can be different.

2) data type of columns should match.

3) if at all if you want to sort the output, order by clause should be

written with last query.

QUERY1

UNION

(QUERY2

INTERSECT

QUERY3)

MINUS

QUERY4

UNION ALL

QUERY5  ORDER BY 2;

ALL SET OPERATORS ARE HAVING SAME PRIORITY.

SELECT * FROM hr.employees;

SELECT * FROM hr.job_history;

/* I WANT TO DISPLAY ALL THE EMPLOYEES DETAILS WITH PRESENT JOB AND PRESENT DEPARTMENT

AND ALSO I WANT TO DISPLAY ALL THE EMPLOYEES DETAILS WITH PAST JOB AND PAST DEPARTMENT

INCLUDING DUPLICATE */

SELECT * FROM hr.employees

UNION ALL

SELECT * FROM hr.job_history; -- wrong

SELECT employee_id emp, job_id job, department_id deptno FROM hr.employees

UNION ALL

SELECT employee_id, job_id, department_id FROM hr.job_history

ORDER BY EMP;

/* I WANT TO DISPLAY ALL THE EMPLOYEES DETAILS WITH PRESENT JOB AND PRESENT DEPARTMENT

AND ALSO I WANT TO DISPLAY ALL THE EMPLOYEES DETAILS WITH PAST JOB AND PAST DEPARTMENT

WITHOUT DUPLICATE RECORDS */

SELECT employee_id emp, job_id job, department_id deptno FROM hr.employees

UNION

SELECT employee_id, job_id, department_id FROM hr.job_history;

/* I want to display all the employees details who are doing in current job

and who did in job in past */

SELECT employee_id, job_id FROM hr.employees

UNION

SELECT employee_id, job_id FROM hr.job_history;

/* I want to display all the employees details who are doing in current department

and who did in a department in past */

SELECT employee_id, department_id FROM hr.employees

UNION

SELECT employee_id, department_id FROM hr.job_history;

/* I WANT TO DISPLAY ALL THE EMPLOYEES DETAILS WHO ARE DOING IN THE PRESENT JOB

AND IN THE PRESENT DEPARTMENT AND WHO DID IN THE JOB AND SAME DEPARTMENT IN THE

PAST */

SELECT employee_id emp, job_id job, department_id deptno FROM hr.employees

INTERSECT

SELECT employee_id, job_id, department_id FROM hr.job_history;

SELECT employee_id, job_id FROM hr.employees

INTERSECT

SELECT employee_id, job_id FROM hr.job_history;

SELECT employee_id, department_id FROM hr.employees

INTERSECT

SELECT employee_id, department_id FROM hr.job_history;

/* I WANT TO DISPLAY ALL THE EMPLOYEES DETAILS WHO NEVER CHANGED THEIR JOBS

AND DEPARTMENT */

SELECT employee_id FROM hr.employees

MINUS

SELECT employee_id FROM hr.job_history;

SELECT DEPTNO  FROM EMP

INTERSECT

SELECT DEPTNO FROM DEPT;

SELECT EMPNO  FROM EMP

INTERSECT

SELECT DEPTNO FROM DEPT;

SELECT DEPTNO FROM EMP

INTERSECT

SELECT DEPTNO FROM EMP;

SELECT EMPNO, ENAME  FROM EMP

INTERSECT

SELECT DEPTNO, DNAME FROM DEPT;

SELECT EMPNO, ENAME  FROM EMP

INTERSECT

SELECT DNAME, DEPTNO FROM DEPT;

SELECT DEPTNO FROM DEPT

MINUS

SELECT DEPTNO FROM EMP;

SELECT DEPTNO FROM DEPT

UNION

SELECT DEPTNO FROM EMP;

SELECT DEPTNO FROM DEPT

UNION ALL

SELECT DEPTNO FROM EMP;

SELECT DEPTNO FROM DEPT  ORDER BY 1 -- will give error

UNION ALL

SELECT DEPTNO FROM EMP ORDER BY 1;

SELECT DEPTNO FROM DEPT

UNION ALL

SELECT DEPTNO FROM EMP ORDER BY 1;

SELECT GRADE, TO_CHAR(LOSAL), TO_CHAR(HISAL) FROM SALGRADE

UNION

SELECT * FROM DEPT;

DESC dept;

SELECT DEPTNO, DNAME FROM DEPT

UNION

SELECT GRADE,  TO_CHAR(NULL) FROM SALGRADE;

SELECT empno, ename, deptno, job, TO_CHAR(NULL) LOC FROM emp

UNION

SELECT deptno, dname, 0,     NULL, loc FROM dept;

desc dept;