In this step by step SQL Tutorial I will show you how to use EXCEPT operator.For the purpose of this tutorial I will use SQL Server 2012 but this example should work with SQL Server 2008 and SQL Server 2005.
NOTE: People often type in google EXCEPT clause. I thought I will clarify that EXCEPT is not a clause but SET Opearator.
Prior knowledge:
It might be easier for you to follow this tutorial if you have knowledge of SQL UNION Operator that generally most developer learn first and is very similar to EXCEPT operator in rules and structure. The only difference is end result.
How does EXCEPT work?
EXCEPT operator combines two queries that have the same number of fields and returns unique rows from first query that don't exist in second query.
EXCEPT operator has two basic rules:
- The number of columns and order must be the same in all queries.
- The data types must be the same.
EXCEPT EXAMPLE
To better understand EXCEPT operator let's give you a real life example. In our building a data warehouse project I was given a task to see if we are getting website visits to pages that don't exist in our "master list". To do that I need to compare two different sources of information and find any values that exist in first source but don't exist in second source.
Below is a PAGE table that contains all pages that we created. In theory when someone visit our website it should go to a page that we created and our task is to check if that is what is happening and if not find out why that is not the case.
Our next source is TrackingSystem view that display information about each visit including our website page name. This data is gathered automatically and we do not have full control over it so it is important to cross reference data and see if we have "good quality" data.
See below view Tracking System that shows Page Name which in theory should match our Page Table from previous example.
Now that I have found fields from two different sources that I need to compare; I can do EXCEPT operator to compare the result of this two queries and get unique values of pages that are recorded during each visit and don't exist in our Page table that should contains all pages.
Below is our query using EXCEPT operator and uses two different databases.
NOTE: We got an error! If you would like to know more about Collation confict please follow this link (we will upload the link soon).
Our new query is exactly the same but uses COLLATE to resolve collation conflict.
See below our query and results of EXCEPT operator.
As you can see we have a few page names that have been recorded and don't exist in our Page table. Looking at the data I can see that there are some special characters %5C that indicate that our internal links or external links that point to our pages are not fully correct.
Thanks to EXCEPT operator we were able to quickly find a list of page name exception and using this information we can create suitable actions.
I hope you found our EXCEPT operator example useful
Take care
Katie
