Set operators in SQL Server 2016 are used to compare rows between input sets. This course covers the UNION, INTERSECT, and EXCEPT set operators, as well as the APPLY operator, which is used to evaluate rows in one input set against the expression defining the second input set. This course also introduces the basic components of common table expressions. This course is one of a series of courses that cover the objectives for 70-761: Querying Data with Transact-SQL exam.
Learning Objectives
Set Operators
- start the course
- distinguish when to use the UNION and UNION All operators in SQL Server 2016
- demonstrate how to write SQL Server 2016 queries that use the UNION operator
- demonstrate how to write SQL Server 2016 queries that use the UNION ALL operator
- demonstrate how to use the UNION and UNION ALL operators together
EXCEPT and INTERSECT Operators
- recognize when to use the EXCEPT operator in SQL Server 2016
- demonstrate how to use the EXCEPT and INTERSECT operators in SQL Server 2016
- determine the results of reversing the order of input with the EXCEPT operator
- determine the effect of NULL values on the INTERSECT operator
Apply Table Operator
- recognize when to use the APPLY operator in SQL Server 2016
- use APPLY statements that return given data based on supplied data in SQL Server 2016
- use CROSS APPLY operators in SQL Server 2016
- use OUTER APPLY operators in SQL Server 2016
Common Table Expressions
- recognize the basic components of table expressions in SQL Server 2016
- describe the difference between table expressions and temporary tables
- create recursive table expressions in SQL Server 2016
Practice: Apply SQL Server 2016 Operators
- use operators in SQL Server 2016