Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


Validate Your Refactored SQL Queries with a New SQL Server 2005 Operator : Page 2

Prove that your refactored SQL query returns the expected results using the EXCEPT operator, new in SQL Server 2005.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Meet the EXCEPT Operator
The EXCEPT operator is a SQL language enhancement introduced in SQL Server 2005. Like the UNION operator, EXCEPT combines two SELECT statements into a single results set. The same rules about number of columns and compatible data types apply equally to UNION and EXCEPT. EXCEPT takes the output from the first (or left-hand side—LHS) SELECT and compares it with the second (or right-hand side—RHS) SELECT and returns distinct rows from the LHS SELECT that are not present in the RHS SELECT. Think of this processing as similar to the way that a UNION ignores duplicate rows.

When I first read about the EXCEPT operator, I thought it was cool but I could not actually find a valid reason to use it in production code. In most cases, you can obtain the same results using joins and WHERE clauses. However, EXCEPT becomes very useful when validating a refactored query.

Validate Your Refactored Query
In this example, you use EXCEPT to verify that your refactored query returns the same results, by checking whether the query using EXCEPT returns no rows. If it does, you will know that the queries return the same results:

SELECT * FROM TableA WHERE EXISTS (SELECT * FROM TableB WHERE TableA.id = TableB.refid) EXCEPT SELECT TableA.* FROM TableA LEFT OUTER JOIN TableB ON TableA.id = TableB.refid

Here are the results:

id textfield ----------- -------------------- (0 row(s) affected)

Great, this returns no rows. That must mean the SQL queries are returning the same results, right? Wrong! EXCEPT works uni-directionally, so it will display only rows from the LHS query that are not in the RHS query. If the RHS query is returning rows that are not in the LHS query, EXCEPT won't return these rows. Therefore, you can test fully only by running the query reversed as well:

SELECT TableA.* FROM TableA LEFT OUTER JOIN TableB ON TableA.id = TableB.refid EXCEPT SELECT * FROM TableA WHERE EXISTS (SELECT * FROM TableB WHERE TableA.id = TableB.refid)

Here are the results:

id textfield ----------- -------------------- 3 Row three 5 Row five (2 row(s) affected)

This query produces a list of results indicating that when you refactored the query, you changed the results. It also tells you which extra rows are being returned.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date