Mastering SQL EXCEPT statement with
dbForge Studio for SQL Server

Introduced in Microsoft SQL Server 2005, the EXCEPT statement has become one of the most widely used tools when it comes to filtering records and identifying differences between two sets of data. Its operating principle is similar to what minus does in mathematics. Namely, EXCEPT returns only those records from the left SELECT query that are not present in the results returned by the SELECT query on the right side. Today, we are going to talk about this statement from several points of view: its basic (and not so basic) syntax, use cases, advanced techniques, and the ways to implement the EXCEPT clause in real-life scenarios using a convenient and user-friendly GUI tool - dbForge Studio for SQL Server. Let's get it started!

Understanding the SQL EXCEPT clause

EXCEPT is not the only means of working with two datasets. For example, there are two more operators that serve a similar purpose:

• UNION combines the results of two SELECT statements into a single dataset and removes any duplicate rows. In other words, it shows you everything there is to show without repeating itself.
• INTERSECT gets only those rows that exist in both datasets. To rephrase, this operator finds a common denominator for different datasets.
• EXCEPT identifies rows unique to one dataset by subtracting another dataset from it. As a result, you have everything that can only be found in the first dataset and nothing from the second one.

Once we have gotten any possible confusion among EXCEPT, INTERSECT, and UNION out of the way, let us focus entirely on the hero of the occasion:

right SELECT query EXCEPT left SELECT query;

As you can see, the basic syntax is rather simple. Let us try it out in practice.

Practical applications of the EXCEPT clause

In this section, we are going to unfold how the EXCEPT clause finds practical use in different real-world scenarios and provide you with some tips for ensuring accurate comparisons and results. For instance, it can be used to compare expected and actual results in quality control processes, flagging any inconsistencies for further investigation. In financial analysis, EXCEPT can help detect irregularities by comparing transaction records from different sources. This can aid auditors in pinpointing discrepancies or fraudulent activities within financial statements. Moreover, content-driven platforms can utilize this clause to manage duplicates or outdated information. By comparing new and existing datasets, redundant entries can be identified and removed, ensuring freshness and relevance of the content.

Now, let us list some tips that will help you achieve the most accurate results while comparing data using EXCEPT:

• Normalize data types, formats, and structures across datasets to minimize discrepancies and enhance reliability.
• Consider using IS NULL or COALESCE functions to account for null values and maintain consistency in comparisons.
• Use the DISTINCT keyword when necessary to remove duplicate records from datasets. This ensures that comparisons are based on unique records, preventing inaccuracies in results.
• Conduct thorough testing of EXCEPT queries across various scenarios to validate their effectiveness. Test for edge cases, such as empty datasets, null values, etc.
• Validate the integrity and completeness of data sources before comparing.

Try it yourself with dbForge Studio

Even though SQL Server Management Studio (SSMS) is the most popular and familiar tool that allows you to work with SQL Server databases, it is not the only one. Moreover, in the continuously evolving world of database development, administration, and management, new GUIs keep appearing like mushrooms after the rain. How do you choose the tool that is perfect for you in this variety?

Let us compare dbForge Studio for SQL Server with SSMS so that you can make an informed decision on which solution best aligns with your daily requirements:

Take advantage of dbForge Studio for SQL Server by downloading a free, fully functional 30-day trial version and installing it on your computer. With a huge pack of advanced features and intuitive GUI, this all-in-one MSSQL tool can maximize productivity and make SQL Server database development, administration, and management process efficient. The Studio can also be of use when it comes to today's topic, from generating test data to performing advanced percentage calculations.

For a more visual comparison of the two solutions, watch the SSMS vs. dbForge Studio for SQL Server - Features Comparison video on the Devart YouTube channel.

Hands-on examples

At last, we have reached the point when the theoretical part is over, and it is time for action. Let us use the most basic EXCEPT command in practice. For demonstration purposes, we chose dbForge Studio for SQL Server as our playground and AdventureWorks2022 database as a test subject.

1. For starters, launch dbForge Studio and click New SQL.

2. In the SQL window that opens, enter the following query:

SELECT
  ID
 ,ProductName
 ,Category
FROM Sales.OnlineShop
EXCEPT
SELECT
  ID
 ,ProductName
 ,Category
FROM Sales.Shop;

The command above retrieves the names of products from the Sales.OnlineShop table that are not present in Sales.Shop table.

3. Click Execute on the SQL toolbar.

You will see the query results in the grid right below the SQL document.

Similarly, if we switch the SELECT queries, we will get a completely different result: the records from Sales.OnlineShop not present in Sales.Shop.

Advanced use cases

Now that we understand the basics of how the EXCEPT clause works, we can challenge ourselves with more complex excersises.

1. In this example, we retrieve all the records from the Sales.OnlineShop table, excluding those with prices exceeding 1000.

SELECT
  ID
 ,ProductName
 ,Category
 ,Price
FROM Sales.OnlineShop
EXCEPT
SELECT
  ID
 ,ProductName
 ,Category
 ,Price
FROM Sales.OnlineShop
WHERE Price > 1000;

2. The next example features the ORDER BY operator. First, the query retrieves records from the Sales.OnlineShop table that are not present in Sales.Shop. Then, the records with prices less than 500 are filtered out. Finally, the ORDER BY clause sorts the results by the Category column.

SELECT
  ID
 ,ProductName
 ,Category
 ,Price
FROM Sales.OnlineShop
EXCEPT
SELECT
  ID
 ,ProductName
 ,Category
 ,Price
FROM Sales.Shop
WHERE Price < 500
ORDER BY Category;

3. In the beginning of this article, we mentioned two operators that are similar to EXCEPT but serve a different purpose. Now, let us take a closer look at both to broaden our knowledge and apply it accordingly in the future. The first one we are going to demonstrate is UNION.

SELECT
  ID
 ,ProductName
 ,Category
FROM Sales.Shop
UNION
SELECT
  ID
 ,ProductName
 ,Category
FROM Sales.OnlineShop;

The query above retrieves distinct records from both the Sales.Shop and Sales.OnlineShop tables, combining them into a single result set.

4. The last example for today would be the practical usage of the INTERSECT operator.

SELECT
  ID
 ,ProductName
 ,Category
FROM Sales.OnlineShop
INTERSECT
SELECT
  ID
 ,ProductName
 ,Category
FROM Sales.Shop;

As you can see in the results grid, only those records that exist in both mentioned tables are present.

Further learning

After everything has been said and done, we would like to provide you with several additional sources of information that can further deepen your SQL Server expertise:

• SQL Server Tutorials
• dbForge Studio Documentation
• dbForge Studio Video Tutorials
• Devart Academy