The SQL Set operations is used to combine the two or more sql statements.
Types of Set operations:
- Union
- Unionall
- Intersect
- Minus
Union Operation:
- The SQL Union operation is used to combine the result of two or more SQL SELECT queries.
- In the union operation, all the number of datatype and columns must be same in both the tables on which UNION operation is being applied.
- The columns in every
SELECTstatement must also be in the same order - The union operation eliminates the duplicate rows from its resultset.
Syntax
SELECT Column_name FROM table1
UNION
SELECT column_name FROM table2;
Example
SELECT * FROM First
UNION
SELECT * FROM Second;
Union all operation:
The
UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
UNION ALL
SELECT column_name(s) FROM table2;
Intersect Operation
- It is used to combine two SELECT statements.
- The Intersect operation returns the common rows from both the SELECT statements.
- In the Intersect operation, the number of datatype and columns must be the same.
- It has no duplicates and it arranges the data in ascending order by default.
Syntax
SELECT column_name FROM table1
INTERSECT
SELECT column_name FROM table2;
Example
SELECT * FROM First
INTERSECT
SELECT * FROM Second;
MINUSOperation
- It combines the result of two SELECT statements.
- Minus operator is used to display the rows which are present in the first query but absent in the second query.
- It has no duplicates and data arranged in ascending order by default.
Syntax
SELECT column_name FROM table1
MINUS
SELECT column_name FROM table2;
Example
SELECT * FROM First
MINUS
SELECT * FROM Second
No comments:
Post a Comment