How to use SQL Except clause in MySQL ~ Tech Blog

MySQL does not support the Except clause, but in this article, we will see how to emulate its functioning.

First, let’s create a couple of tables that will help us build our example queries. The tables will be called Computer and Repair_Order and will have the following structure and data:

CREATE TABLE COMPUTER(
  ID INT(11) NOT NULL,
  BRAND VARCHAR(30) NOT NULL,
  MODEL VARCHAR(30) NOT NULL,
  SERIAL VARCHAR(30) NOT NULL,
  PRICE FLOAT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO COMPUTER (ID,BRAND,MODEL,SERIAL,PRICE) VALUES
(1, 'BRAND A', 'MODEL 1', 'A1B2C3D4',500),
(2, 'BRAND B', 'MODEL 2', 'AB12CD34',550),
(3, 'BRAND C', 'MODEL 3', 'ABC123D4',600),
(4, 'BRAND D', 'MODEL 4', 'ABCD1234',650),
(5, 'BRAND E', 'MODEL 5', '1234ABCD',700),
(6, 'BRAND F', 'MODEL 6', 'ABCDEFGH',750),
(7, 'BRAND G', 'MODEL 7', '12345678',800),
(8, 'BRAND H', 'MODEL 8', '1234ABCD',850),
(9, 'BRAND I', 'MODEL 9', '12AB3456',900),
(10, 'BRAND J', 'MODEL 10', 'ABC12DEF',950);

CREATE TABLE REPAIR_ORDER(
  ID INT(11) NOT NULL,
  COMPUTER_ID INT(11) NOT NULL,
  RECEIVED DATE NOT NULL,
  STATUS VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO REPAIR_ORDER(ID,COMPUTER_ID,RECEIVED,STATUS) VALUES
(1,2,'2019/01/05','PENDING'),
(1,5,'2019/02/10','COMPLETED'),
(1,7,'2019/05/02','COMPLETED'),
(1,9,'2019/06/01','PENDING');

Script 1. Commands to create example tables

We can execute the following queries to verify the content of the tables we just created:

SELECT * FROM `computer`

Script 2. Query to retrieve Computer table rows

Result:

ID	BRAND	MODEL	SERIAL	PRICE
1	BRAND A	MODEL 1	A1B2C3D4	500
2	BRAND B	MODEL 2	AB12CD34	550
3	BRAND C	MODEL 3	ABC123D4	600
4	BRAND D	MODEL 4	ABCD1234	650
5	BRAND E	MODEL 5	1234ABCD	700
6	BRAND F	MODEL 6	ABCDEFGH	750
7	BRAND G	MODEL 7	12345678	800
8	BRAND H	MODEL 8	1234ABCD	850
9	BRAND I	MODEL 9	12AB3456	900
10	BRAND J	MODEL 10	ABC12DEF	950

Table 1. Computer table rows

SELECT * FROM `repair_order`

Script 3. Query to retrieve Repair_oreder table rows

Result:

ID	COMPUTER_ID	RECEIVED	STATUS
1	2	2019-01-05	PENDING
1	5	2019-02-10	COMPLETED
1	7	2019-05-02	COMPLETED
1	9	2019-06-01	PENDING

Table 2. Repair_order table rows

MySQL EXCEPT clause emulation

In MySQL, we can emulate the operation of the Except clause in several ways. In this article, we will focus on two of those techniques.

MySQL EXCEPT emulation with NOT IN clause

In the first technique we will review, we will use the NOT IT clause. We will build a query that returns all the rows of the Computer table Except those that have a repair order:

SELECT * FROM computer a
WHERE a.id NOT IN (SELECT COMPUTER_ID from repair_order)

Script 4. SQL SELECT ALL EXCEPT emulation example 1

Result:

ID	BRAND	MODEL	SERIAL	PRICE
1	BRAND A	MODEL 1	A1B2C3D4	500
3	BRAND C	MODEL 3	ABC123D4	600
4	BRAND D	MODEL 4	ABCD1234	650
6	BRAND F	MODEL 6	ABCDEFGH	750
8	BRAND H	MODEL 8	1234ABCD	850
10	BRAND J	MODEL 10	ABC12DEF	950

Table 3. SQL SELECT ALL EXCEPT emulation example 1 result

MySQL Except emulation with LEFT JOIN clause

In the second technique that we will review, we will use a LEFT JOIN clause to recreate the previous example:

SELECT a.*
  FROM computer a
  LEFT JOIN repair_order b
    ON a.ID = b.COMPUTER_ID
WHERE b.COMPUTER_ID IS NULL

Script 5. SQL SELECT ALL EXCEPT emulation example 2

Result:

ID	BRAND	MODEL	SERIAL	PRICE
1	BRAND A	MODEL 1	A1B2C3D4	500
3	BRAND C	MODEL 3	ABC123D4	600
4	BRAND D	MODEL 4	ABCD1234	650
6	BRAND F	MODEL 6	ABCDEFGH	750
8	BRAND H	MODEL 8	1234ABCD	850
10	BRAND J	MODEL 10	ABC12DEF	950

Table 3. SQL SELECT ALL EXCEPT emulation example 2 result

Tech Blog

Tuesday 3 December 2019