MS SQL Server UNION Operator and INTERSECT Operator
MS SQL Server UNION Operator
- UNION operator is used to merge the results of two or more SELECT statements.
- It selects only distinct values by default.
- Syntax:
SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] UNION
SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];
- expression1, expression2,....expression_n indicates the column and calculations.
- tables specify the tables from where you want to retrieve records.
- WHERE Condition is optional and specifies the condition that must be satisfied for records to be selected.
UNION operator with a single expression
Example:
SELECT name FROM [University].[dbo].[Student] UNION SELECT name FROM [University].[dbo][staff];
UNION operator with a Multiple expression
Example:
SELECT Id,Name,Branch FROM [University].[dbo].[student] WHERE CGPA>=8.0 UNION SELECT Id,Name, Branch FROM [University].[dbo].[staff];
UNION ALL Operator
- UNION ALL operators select all the values including duplicate values.
- Syntax:
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;
Example:
SELECT Id,Name,Branch FROM [University].[dbo].[student] WHERE CGPA>=8.0 UNION ALL SELECT Id,Name, Branch FROM [University].[dbo].[staff];
MS SQL Server INTERSECT Operator
- INTERSECT operator is used to fetch common records between two select statements.
- Syntax:
SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] INTERSECT SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];
- expression1, expression2,....expression_n indicates the column and calculations.
- tables specify the tables from where you want to retrieve records.
- WHERE Condition is optional and specifies the condition that must be satisfied for records to be selected.
NOTES:
- The same number of expressions in both select statements.
- Corresponding columns in both select statements must have the same data type.
INTERSECT operator with a single expression
Example:
SELECT name FROM [University].[dbo].[Student] INTERSECT SELECT name FROM [University].[dbo][staff];
INTERSECT operator with a Multiple expression
Example:
SELECT Id,Name,Branch FROM [University].[dbo].[student] WHERE CGPA>=8.0 UNION SELECT Id,Name, Branch FROM [University].[dbo].[staff];
