The Working with SET Operators module provides you with the instructions and devices to develop your hands on skills in the following topics.
- Use EXCEPT and INTERSECT operators to combine query results
- Use UNION operator to combine results of two SELECT statements
- Use ALL and parentheses to combine results of multiple SELECT statements
Lab time: It will take approximately 30 minutes to complete this lab.
The following exam objectives are covered in this lab:
- Demonstrate how to combine results from two or more queries into a single result set using EXCEPT and INTERSECT operators
- Demonstrate how to combine the results of two SELECT statements with WHERE and ORDER BY using UNION operator
- Demonstrate how to combine the results of three SELECT statements to show effects of ALL and parentheses
Exercise 1 - Use EXCEPT and INTERSECT Operators to Combine Query Results
The EXCEPT and INTERSECT operators compare and combine the results from two or more queries into a single result set.
The EXCEPT operator compares the results of the first or the left input query in the query statement with the results of the second or right input query. Further, it returns distinct rows from the result of the left input query that does not match with the result set of the right input query.
The INTERSECT operator retrieves rows that are common or match in both the result sets of the left and right input query.
The rules of the INTERSECT and EXCEPT operator are as follows:
- Number of columns in both the result sets should be same.
- Data types should be common in both the query results.
- EXCEPT and INTERSECT operators cannot be used in distributed partitioned view and query notifications.
- EXCEPT and INTERSECT operators cannot be used in distributed queries.
- In this exercise, you will learn to use the EXCEPT and INTERSECT operators.
Exercise 2 - Use UNION Operator to Combine Results of Two SELECT Statements
UNION operator combines or collates the results of two or more SELECT statements. It displays the result set that includes all the rows from the given queries.
UNION operator is different from JOIN statement. JOIN statement combines the fields from two tables, whereas UNION operator combines all the rows from two tables.
However to use the UNION operator, you need to adhere to the following rules:
- The number and order of the fields must be same across all the queries.
- The data types of the fields must be the same.
In this exercise, you will learn to use UNION operator to combine the results of two SELECT statements with WHERE and ORDER BY clause.
Exercise 3 - Use ALL and Parentheses to Combine Results of Multiple SELECT Statements
You can use ALL to display all the rows in the result set. Duplicate rows are also included in the result set. Parentheses decide the order of query execution.
In this exercise, you will learn to use ALL and parentheses to combine the results of multiple queries.
See the full benefits of our immersive learning experience with interactive courses and guided career paths.