SQL Except Example

1. Introduction

In this article, we will look at the Except operation. The Except is an operator of SQL Set Theory. The Except operator was introduced in the Microsoft SQL Server. Oracle supports MINUS operator which is equivalent to Except. However, MySQL supports neither Minus nor except. In this article, we will understand what the Except operator is and how to emulate it in MySQL.

2. What does Set Theory mean?

In SQL, an entity that can hold a collection of objects is called a “set”. Hence in SQL all “Relations” i.e. tables are sets. Set Theory is the set of operations that allow objects from multiple sets to be returned as a single result. In other words, the set operators allow multiple select queries to be combined to give a single result.

The SQL Set operators are UNION, INTERSECT, and EXCEPT/MINUS. Out of these operators, MySQL supports only UNION. It does not support the Intersect and Except keywords. However, MySQL supports other queries and constructs that easily emulate them.

3. What is Except Operator?

The Intersect operator is the intersection of 2 or more datasets. In Relational Algebra, the intersection is shown with – symbol. The Venn diagram representation of the intersect operator is as follows:

In SQL, the basic syntax of the Except operator is as follows:

SELECT [column_names or *]
FROM tables
[WHERE conditions]

EXCEPT

SELECT [column_names or *]
FROM tables
[WHERE conditions];

4. How to Emulate Except in MySQL?

To get the effect of the Except operator, we can use the following methods in MySQL RDBMS.

• LEFT [OUTER] JOIN: MySQL supports the LEFT JOIN/LEFT OUTER JOIN which when used with a condition, gives the exact same result as the Except operator. The LEFT OUTER JOIN usually gives back the result of all the records from the first(left) table which includes the records which match the condition mentioned. IF we add the condition that the records from right table are NULL i.e. not present, then the resulting query gives the exact same effect as using an Except operator.  Left join can be implemented on two or more tables. The Basic syntax of the Left join query is as follows

 SELECT [column_names]
 FROM [table1]
LEFT [OUTER] JOIN table2 
USING([column_name_common_to_both]);

OR

SELECT [column_names]
 FROM [table1]
LEFT [OUTER] JOIN [table2] ON [table1.column_name] = [table2.column_name];

The OUTER is just syntactic sugar. In terms of working, the LEFT and LEFT OUTER JOIN are the exact same operation.

• NOT IN and Sub Query: We can also use the NOT In Operator and subqueries to get the same result as Intersect. The basic syntax of a NOT IN operator is as follows

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name NOT IN
 (SELECT column_name [, column_name ]
 FROM table1 [, table2 ]
 [WHERE] conditions);

• NOT EXISTS: The NOT EXISTS operator is also used to get the effect of an Intersect operator. The basic syntax of a NOT EXISTS query is as follows:

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE NOT EXISTS
(SELECT column_name
  FROM table1 
   [WHERE condition]);

5. Setup

For going through the examples related to the Except operator, we will consider the database called “Sakila”. This is an example database given by MySQL. The schema structure is available on the documentation page.

For running the queries, we will use the MySQL Workbench. The documentation for MySQL Workbench is available here.

Please note, I have modified the data (nullified a few values) to ensure result from the example queries.

6. Examples

We will look at all examples of all the 3 ways in which we can emulate the except operator in MySQL.

6.1 LEFT JOIN

If for example, we perform –

 Table A LEFT JOIN Table B ON A.id = B.id 

then Left join will return all the records from table A that do not match Table B + all the records that from Table A that do. Essentially the Full Table A. However, if we want only the records that are present in table A and not in Table B then we need to add the following condition

Table A LEFT JOIN Table B ON A.id = B.id
Where B.id IS NULL

This will give the same effect as the “Except” operator.

6.1.1 LEFT Join 2 tables using the ON Keyword

LEFT joins can be done using the ON keyword. We will also add the condition mentioned above. For example:

SELECT C.CUSTOMER_ID,C.FIRST_NAME,C.LAST_NAME
FROM CUSTOMER C
LEFT JOIN ACTOR A 
ON C.LAST_NAME = A.LAST_NAME
WHERE A.LAST_NAME IS NULL
ORDER BY C.LAST_NAME DESC;

6.1.2 LEFT JOIN 2 tables with the USING Keyword

If the column names from the tables in the Join are the same, then we can use the USING keyword instead. For example

SELECT C.CUSTOMER_ID,C.FIRST_NAME,C.LAST_NAME
FROM CUSTOMER C
LEFT JOIN ACTOR A 
USING (LAST_NAME)
WHERE A.LAST_NAME IS NULL
ORDER BY C.LAST_NAME DESC;

6.1.3 LEFT JOIN with Other joins

We can combine joins to form a complex query. The column names and tables in each of the intersects can be different or same. For example:

SELECT ADR.ADDRESS, ADR.DISTRICT, CT.CITY, CN.COUNTRY,ADR.POSTAL_CODE
FROM ADDRESS ADR 
LEFT JOIN STORE ST
ON ST.ADDRESS_ID = ADR.ADDRESS_ID
JOIN CITY CT USING (CITY_ID)
JOIN COUNTRY CN USING (COUNTRY_ID)
WHERE ST.ADDRESS_ID IS NULL;

6.1.4 LEFT JOIN using Multiple tables.

We can use multiple left joins in a query across multiple tables. In the below example we are trying to get all such films which have not been categorized but has revenue based on their rating.

SELECT COUNT(F.RATING),F.RATING,SUM( IFNULL(PAY.AMOUNT, 0) ) REVENUE
FROM FILM_CATEGORY FC
LEFT JOIN CATEGORY CAT
ON CAT.CATEGORY_ID = FC.CATEGORY_ID
LEFT JOIN FILM F
ON FC.FILM_ID = F.FILM_ID
LEFT JOIN INVENTORY INV
ON F.FILM_ID = INV.FILM_ID
LEFT JOIN RENTAL REN
ON INV.INVENTORY_ID = REN.INVENTORY_ID
LEFT JOIN PAYMENT PAY
ON REN.RENTAL_ID = PAY.RENTAL_ID
WHERE FC.CATEGORY_ID IS NULL
GROUP BY F.RATING;

6.2 Not In with a Subquery

The Not In operator also does the job of the except operator. The Subquery used with the NOT IN, returns one or more records. The Query using the NOT IN then matches the column with every record returned and the result contains only the records present in the outer table.

6.2.1 Simple NOT IN with a subquery

The Not In query can be used with a simple subquery as in the below example

SELECT *
FROM FILM
WHERE LANGUAGE_ID NOT IN (
	SELECT LANGUAGE_ID 
	FROM LANGUAGE 
	WHERE NAME = 'ENGLISH'
)
ORDER BY TITLE;

6.2.2 Using NOT IN multiple times on the same column name

We can use the NOT IN operator in the same query, on the same column to check multiple conditions. As an example in the below we are retrieving all such film_ids that are for films which are not short and have not been rented.

SELECT FILM_ID,TITLE,DESCRIPTION,RATING,SPECIAL_FEATURES 
FROM FILM
WHERE FILM_ID NOT IN(
SELECT FILM_ID
FROM FILM JOIN FILM_CATEGORY USING (FILM_ID) JOIN CATEGORY USING (CATEGORY_ID)
GROUP BY CATEGORY.NAME
HAVING AVG(LENGTH) < (SELECT AVG(LENGTH) FROM FILM))
AND FILM_ID NOT IN(
SELECT FILM_ID FROM INVENTORY
WHERE INVENTORY_ID IN (SELECT INVENTORY_ID FROM RENTAL WHERE INVENTORY_ID IS NULL));

6.3 Not Exists

The Not Exists operator also gives the effect of the Except operator. In general, the NOT IN and the NOT Exists clauses are preferred over the Left Join to get the effect of the Except operator.

6.3.1 Simple Not Exists clause

A simple query with the Not Exists Clause is as follows.

SELECT * FROM INVENTORY WHERE
NOT EXISTS (
 SELECT 1 FROM RENTAL WHERE RENTAL.INVENTORY_ID=10
 AND RETURN_DATE IS NULL
);

6.3.2 Not Exists with Other conditions

The Not Exists Clause can also be combined with other conditions to make a complex query. An example of the same is as follows:

SELECT INVENTORY_ID,RENTAL_DATE,RETURN_DATE,STAFF_ID
FROM INVENTORY JOIN STORE USING (STORE_ID)
     JOIN FILM USING (FILM_ID)
     JOIN RENTAL USING (INVENTORY_ID)
WHERE FILM.TITLE = 'ACADEMY DINOSAUR'
      AND STORE.STORE_ID = 1
      AND NOT EXISTS (SELECT * FROM RENTAL
                      WHERE RENTAL.INVENTORY_ID = INVENTORY.INVENTORY_ID
                      AND RENTAL.RETURN_DATE IS NULL);

7. Summary

Even though My SQL does not directly support the Except operator, we can still emulate the Except operator in multiple ways. There have been several requests made already to My SQL to introduce support for all the set operators to provide better dynamic SQL query building.   

8. Download the Source Code

This was an example of the Except operation using MySQL RDBMS.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.