Oracle

you know about set operators which is used mathematically. We implement database with set Operators.

UNION

In Oracle, UNION operator is used to combine the result sets of two or more Oracle SELECT statements.

Syntax:

(SELECT expression1, expression2, ... expression_n  
FROM table1  
WHERE conditions )
UNION 
(SELECT expression1, expression2, ... expression_n  
FROM table2  
WHERE conditions);   

Example:

SELECT student_id 
FROM students  
UNION  
SELECT admission_id
FROM admission; 

Note: If you don't want to remove duplicates, use Oracle UNION ALL operator.

UNION ALL Operator

In Oracle, the UNION ALL operator is used to combine the result sets of 2 or more SELECT statements.

Each SELECT statement within the UNION ALL must have the same number of fields in the result sets with similar data types.

Syntax

SELECT expression1, expression2, ... expression_n  
FROM table1  
WHERE conditions  
UNION ALL 
SELECT expression1, expression2, ... expression_n  
FROM table2  
WHERE conditions;   

Example:

SELECT student_id 
FROM students  
UNION ALL
SELECT admission_id
FROM admission; 

INTERSECT

In Oracle, INTERSECT Operator is used to return the results of 2 or more SELECT statement. It picks the common or intersecting records from compound SELECT queries.

Syntax

SELECT expression1, expression2, ... expression_n  
FROM table1  
WHERE conditions  
INTERSECT 
SELECT expression1, expression2, ... expression_n  
FROM table2  
WHERE conditions;  

Example:

SELECT student_id 
FROM students  
INTERSECT
SELECT student_id
FROM admission; 

MINUS

In Oracle, MINUS operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement.

Each SELECT statement has a dataset and the MINUS operator returns all documents from the first dataset and then removes all documents from the second dataset.

Syntax

SELECT expression1, expression2, ... expression_n  
FROM table1  
WHERE conditions  
MINUS  
SELECT expression1, expression2, ... expression_n  
FROM table2  
WHERE conditions;  

Example:

SELECT dept_id 
FROM department  
MINUS
SELECT dept_id
FROM admission; 

Thank you for reading. If any Query, Feel free to comment down below.