SQL EXCEPT is a set operator used in SQL queries to subtract the results of one query from another. It is used to compare two SELECT statements and return the records that are present in the first query but not in the second query. This tutorial will guide you through the process of using SQL EXCEPT in a step-by-step manner.
Syntax of SQL EXCEPT
The syntax of SQL EXCEPT is as follows:
SELECT column1, column2, ... FROM table1
EXCEPT
SELECT column1, column2, ... FROM table2;The result of the above query will be the records present in table1 but not in table2.
Example of SQL EXCEPT
Let us take an example to understand the practical use of SQL EXCEPT.
Assume we have two tables, ‘Customers’ and ‘Orders’. The ‘Customers’ table has the following records:
| CustomerID | CustomerName | ContactName | Country |
|---|---|---|---|
| 1 | Alfreds | Maria | Germany |
| 2 | Ana Trujillo | Ana | Mexico |
| 3 | Antonio | Antonio | Mexico |
| 4 | Mark | Thomas | UK |
| 5 | Berglunds | Christina | Sweden |
The ‘Orders’ table has the following records:
| OrderID | CustomerID | OrderDate |
|---|---|---|
| 1 | 3 | 2019-01-01 |
| 2 | 2 | 2019-01-02 |
| 3 | 3 | 2019-01-03 |
| 4 | 1 | 2019-01-04 |
| 5 | 5 | 2019-01-05 |
Now, let us write an SQL query using EXCEPT to find the customers who have not placed any orders:
SELECT CustomerID, CustomerName, Country FROM Customers
EXCEPT
SELECT CustomerID, CustomerName, Country FROM OrdersThe output of the above query will be:
Here’s the updated table with the provided information:
| CustomerID | CustomerName | Country |
|---|---|---|
| 4 | Mark | UK |
| 5 | Berglunds | Sweden |
If you have any more questions or need further assistance, feel free to ask!
Troubleshooting Tips
- Make sure that the number and order of columns selected in both queries are the same.
- SQL EXCEPT only returns distinct records, so if there are duplicate records, only one of them will be returned.
- SQL EXCEPT is not supported by all databases. If you are using a database that does not support SQL EXCEPT, you can use the NOT EXISTS operator to achieve the same result.
Conclusion
SQL EXCEPT is a powerful tool for comparing two SELECT statements and finding the records that are present in one query but not in the other. By following the steps outlined in this tutorial, you can easily use SQL EXCEPT in your own SQL queries. Remember to follow the troubleshooting tips to ensure that your queries are working as expected.