SQL: SET Operations

SQL set operations combine the output of two or more SELECT statements into a single result set. The set operators appear to be similar to SQL joins, but there is a significant difference. SQL joins combine the columns from different tables, whereas SQL operators combine rows from different queries.

Types of Set Operation:

• Union
• Union All
• Intersect
• Minus

UNION:

SQL Union is used to join the results of two or more SQL SELECT queries. The number of data types and columns in both tables on which the UNION operation is performed must be the same.
Note: The union operation removes duplicate rows from the result set.

Syntax:

SELECT column_name FROM table1  
UNION  
SELECT column_name FROM table2;  

Example:

Consider Table 1:

ID	STUDENT
1	JOHN
2	NINA

consider table 2:

ID	STUDENT
3	JAKE
2	NINA

Query for SQL UNION operation:

SELECT * FROM Table_1 
UNION
SELECT * FROM Table_2;

Output:

ID	STUDENT
1	JOHN
2	NINA
3	JAKE

Union All:

Union All operation is equivalent to the Union operation, the only difference is it returns the set without removing duplicates and sorting the data.

Syntax:

SELECT column_name FROM table1  
UNION ALL
SELECT column_name FROM table2;  

Example:

Consider Table 1:

ID	STUDENT
1	JOHN
2	NINA

consider table 2:

ID	STUDENT
3	JAKE
2	NINA

Query for SQL UNION ALL operation:

SELECT * FROM Table_1 
UNION ALL
SELECT * FROM Table_2;

Output:

ID	STUDENT
1	JOHN
2	NINA
2	NINA
3	JAKE

INTERSECT:

It is used to join two SELECT statements together. The Intersect operation returns the rows that are common to both SELECT statements.
The number of columns and data types in the Intersect operation should be the same.
Note: It has no duplicates and, by default, arranges the data in ascending order.

Syntax:

SELECT column_name FROM table1  
INTERSECT  
SELECT column_name FROM table2; 

Example:

Consider Table 1:

ID	STUDENT
1	JOHN
2	NINA

consider table 2:

ID	STUDENT
3	JAKE
2	NINA

Query for SQL UNION ALL operation:

SELECT * FROM Table_1 
INTERSECT
SELECT * FROM Table_2;

Output:

ID	STUDENT
2	NINA

MINUS/Except :

The MINUS operator lets us filter away results that are present in the first query but not in the second. To successfully execute the MINUS operation, the number of columns and the data type must be the same in both tables. After performing the MINUS operation, the data/records which are not present in the second SELECT statement or query are displayed.

Syntax:

SELECT column_name FROM table1  
MINUS  
SELECT column_name FROM table2;  

Example:

Consider Table 1:

ID	STUDENT
1	JOHN
2	NINA

consider table 2:

ID	STUDENT
3	JAKE
2	NINA

Query for SQL UNION ALL operation:

SELECT * FROM Table_1 
MINUS
SELECT * FROM Table_2;

Output:

ID	STUDENT
1	JOHN

Note: also read about SQL JOIN

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py