Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What is EXCEPT?
3.
Why Use EXCEPT?
4.
Syntax of EXCEPT
5.
Implementing EXCEPT in Real Scenarios
5.1.
Example 1: Basic Usage
5.2.
Example 2: Using EXCEPT with Multiple Columns
5.3.
Example 3 : EXCEPT with BETWEEN Operator
5.4.
Example 4 : EXCEPT with IN Operator
5.5.
Example 5 : EXCEPT with LIKE Operator
5.6.
Example 6 : EXCEPT with ORDER BY Clause
5.7.
Example 7 : EXCEPT Statements in a Single Table
6.
EXCEPT vs NOT NULL
7.
Frequently Asked Questions
7.1.
What if the tables in the EXCEPT operation don't have the same columns?
7.2.
Is the EXCEPT operator case-sensitive?
7.3.
Is there an operator that does the opposite of EXCEPT?
8.
Conclusion
Last Updated: Mar 27, 2024
Easy

SQL EXCEPT

Author Gunjan Batra
0 upvote

Introduction

Working with data often involves comparing and contrasting different datasets. SQL, being a powerful language for data manipulation and query, provides a set of set operators for such operations, one of which is the EXCEPT operator. 

SQL except

In this article, we'll dive deep into the EXCEPT operator, its uses, and how it can simplify your data analysis tasks.

What is EXCEPT?

The EXCEPT operator in SQL is used to return all the distinct rows from the first (left) query that are not in the output of the second (right) query.

Why Use EXCEPT?

EXCEPT comes handy when you:

Identify Differences: You can use EXCEPT to find differences between two tables.

Clean Data: It helps remove duplicates or unwanted data from a dataset.

Syntax of EXCEPT

The basic syntax of the EXCEPT operator in SQL is as follows:
 

SELECT column_names FROM table1
EXCEPT

SELECT column_names FROM table2;

This query will return all distinct rows from table1 that are not present in table2.

Implementing EXCEPT in Real Scenarios

Now let's take a look at some examples of the EXCEPT operator.

Example 1: Basic Usage

Assume we have two tables, Orders and Delivered, with the following data:

Orders:

Order_ID
1
2
3
4
Orders table

Delivered:

Order_ID
1
2
delivered table

To find out which orders have not been delivered yet, you can use the EXCEPT operator:

SELECT Order_ID FROM Orders
EXCEPT
SELECT Order_ID FROM Delivered;

This will output:

Order_ID
3
4
Output

Example 2: Using EXCEPT with Multiple Columns

Consider two tables, Employees and Managers, with the following data:

Employees:

Employee_ID Name
1 John Doe
2 Jane Doe
Employee table

Managers:

Employee_ID Name
1 John Doe
managers table

To find out employees who are not managers:

SELECT Employee_ID, Name FROM Employees
EXCEPT
SELECT Employee_ID, Name FROM Managers;

This will output:

Employee_ID Name
2 Jane Doe
Output

Example 3 : EXCEPT with BETWEEN Operator

Let us create a different database and table. 

CREATE DATABASE ecommerce_db;
USE ecommerce_db;

 

Now, we need to create a table products and out_of_stock_products in this database:

CREATE TABLE products (
 product_id INT PRIMARY KEY,
 product_name VARCHAR(255),
 stock_quantity INT
);

CREATE TABLE out_of_stock_products (
 product_id INT PRIMARY KEY
);

 

Now, we need to insert the data into the tables:

INSERT INTO products (product_id, product_name, stock_quantity)
VALUES
 (1, 'Laptop', 10),
 (2, 'Smartphone', 5),
 (3, 'Headphones', 15),
 (4, 'Tablet', 0); 

INSERT INTO out_of_stock_products (product_id)
VALUES (4);

 

Now, let use EXCEPT with BETWEEN operator to retrieve products that are in stock and have a stock quantity between 5 and 15. 

SELECT product_id, product_name, stock_quantity
FROM products
WHERE stock_quantity BETWEEN 5 AND 15
EXCEPT
SELECT product_id, product_name, stock_quantity
FROM out_of_stock_products;

Example 4 : EXCEPT with IN Operator

Let us use the same database ecommerce_db and tables. We can use EXCEPT with IN Operator to retrieve products that are in stock and not in the out_of_stock_products table.

SELECT product_id, product_name, stock_quantity
FROM products
WHERE product_id NOT IN (SELECT product_id FROM out_of_stock_products);

Example 5 : EXCEPT with LIKE Operator

Let us use the same database ecommerce_db and tables. We can use EXCEPT with LIKE Operator to retrieve products that are in stock and the product name doesn't start with 'Table'.

SELECT product_id, product_name, stock_quantity
FROM products
WHERE product_name NOT LIKE 'Table%';

Example 6 : EXCEPT with ORDER BY Clause

Let us use the same database ecommerce_db and tables. We can use EXCEPT with ORDER BY Clause to retrieve products that are in stock, not in the out_of_stock_products table, and order by stock_quantity descending.

SELECT product_id, product_name, stock_quantity
FROM products
WHERE product_id NOT IN (SELECT product_id FROM out_of_stock_products)
ORDER BY stock_quantity DESC;

Example 7 : EXCEPT Statements in a Single Table

Let us use the same database ecommerce_db and tables. We can use EXCEPT Statements in a Single Table to retrieve products from the products table where stock_quantity is not 0.

SELECT product_id, product_name, stock_quantity
FROM products
WHERE stock_quantity != 0
EXCEPT
SELECT product_id, product_name, stock_quantity
FROM out_of_stock_products;

EXCEPT vs NOT NULL

Feature EXCEPT Operator NOT NULL Constraint
Purpose Used to retrieve distinct rows from two sets A constraint that ensures a column cannot have a NULL value
Applicability Typically used in queries involving set operations, such as finding differences between result sets Applied at the column level to ensure data integrity and enforce non-null values
Syntax sql SELECT column1 FROM table1 EXCEPT SELECT column1 FROM table2; sql CREATE TABLE table_name ( column_name data_type NOT NULL, ... );
Usage Examples - Find products in stock in one table but not in another - Ensure that a username column in a users table cannot be NULL
Result Set Returns distinct rows from the first set that are not present in the second set Does not return a result set; it is a constraint applied to a column
Common Alternatives NOT IN, LEFT JOIN, NOT EXISTS DEFAULT constraint, CHECK constraint, UNIQUE constraint, PRIMARY KEY constraint
Null Handling Does not directly deal with handling NULL values Specifically used to disallow NULL values in a column
Examples sql SELECT product_id FROM products1 EXCEPT SELECT product_id FROM products2; sql CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(255) NOT NULL, ... );

Frequently Asked Questions

What if the tables in the EXCEPT operation don't have the same columns?

The EXCEPT operation requires the queries to have the same number and type of columns.

Is the EXCEPT operator case-sensitive?

Yes, EXCEPT is case-sensitive, it treats 'ABC' and 'abc' as different strings.

Is there an operator that does the opposite of EXCEPT?

Yes, the INTERSECT operator returns rows that are common to both queries.

Conclusion

The EXCEPT operator is a powerful tool in SQL for identifying differences between datasets. It allows you to quickly and easily find distinct rows from one table that do not exist in another. Whether you're an analyst, a developer, or a database administrator, understanding the EXCEPT operator will no doubt aid in your data manipulation and analysis tasks. Remember, SQL is like a Swiss army knife for data - knowing how to use each tool effectively is the key to success.

Here are some more related articles:

You may refer to our Guided Path on Code Studios for enhancing your skill set on DSA, Competitive Programming, System Design, etc. Check out essential interview questions, practice our available mock tests, look at the interview bundle for interview preparations, and so much more!

Happy Learning!!

Live masterclass