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]);
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.
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);
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.
You can download the full source code of this example here: SQL Except Example