SET Operations in SQL| Union | Union ALL | Intersect | Minus
SET Operations in SQL||Union | Union ALL | Intersect | Minus
SET Operations in SQL
I. Union
II. Union ALL
III. Intersect
IV. Minus
-- TO PERFORM SET OPERATION NUMBER OF COLUMNS MUST SAME IN TWO DIFFERENT TABLE
-- DATA TYPE MUST BE SAME IN CORROSSPANDANCE COLUMN
create table emp1
(
id number,
name varchar(30),
dob date
);
create table emp2
(
id number,
name varchar(30),
dob date
);
insert into emp1 VALUES(1, 'Smith', '12-sep-01');
insert into emp1 VALUES(2, 'James', '1-nov-03');
insert into emp1 VALUES(3, 'Smith', '10-sep-02');
insert into emp1 VALUES(4, 'Kings', '13-jan-01');
insert into emp2 VALUES(3, 'Smith', '10-sep-02');
insert into emp2 VALUES(4, 'Kings', '13-jan-01');
insert into emp2 values (5, 'Asad', '19-feb-04');
insert into emp2 values (6, 'Thomes', '11-jan-98');
insert into emp2 values (7, 'Albret', '11-feb-99');
select * from emp1
UNION
SELECT * FROM EMP2;
select * from emp1;
select * from emp2;
select * from emp1
UNION ALL
SELECT * FROM EMP2;
select * from emp1;
select * from emp2;
select * from emp1
INTERSECT
SELECT * FROM EMP2;
SELECT * FROM EMP1;
SELECT * FROM EMP2;
SELECT * FROM EMP1
MINUS
SELECT * FROM EMP2;
SELECT * FROM EMP2
MINUS
SELECT * FROM EMP1;
