This is a post in the SQL Interview series. These aren't trick or gotcha questions, they're just questions designed to scope out a candidate's knowledge around SQL Server and Azure SQL Database.
Section: Development
Level: Intro
Question:
Consider the following code:
|
1 2 3 4 5 6 7 8 9 |
SELECT CustomerID, CustomerName FROM Sales.Customers WHERE BusinessCategory = 'Cafe' UNION SELECT CustomerID, CustomerName FROM Sales.Customers WHERE City = 'Toronto'; |
A: What would be the difference in results if the query was changed to use a UNION ALL instead of the UNION?
B: What are the performance differences between using a UNION ALL and a UNION in a SELECT statement?
C: How could this query be rewritten without the UNION?
Answer:
A: If the query was changed to use UNION ALL, a customer might be returned more than once. A UNION performs a DISTINCT operation on the results of the query. A UNION ALL does not perform the DISTINCT operation on the results.
B: A UNION invariably involves more work and is slower as it needs to perform a DISTINCT operation on the results.
C: One option would be to rewrite it as follows:
|
1 2 3 4 |
SELECT CustomerID, CustomerName FROM Sales.Customers WHERE BusinessCategory = 'Cafe' OR City = 'Toronto'; |