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.
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 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 |
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 |
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
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
Staying up to the mark is what defines me. Hi all! I’m Rabecca Fatima a keen learner, great enthusiast, ready to take new challenges as stepping stones towards flying colors.
Given an integer A. Compute and return the square root of A. If A is…
Given a zero-based permutation nums (0-indexed), build an array ans of the same length where…
A heap is a specialized tree-based data structure that satisfies the heap property. It is…
What is the Lowest Common Ancestor? In a tree, the lowest common ancestor (LCA) of…
A Binary Search Tree (BST) is a type of binary tree that satisfies the following…