Tuesday 3 December 2019

How to use SQL Except clause in MySQL

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(11NOT NULL,
  BRAND VARCHAR(30NOT NULL,
  MODEL VARCHAR(30NOT NULL,
  SERIAL VARCHAR(30NOT 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(11NOT NULL,
  COMPUTER_ID INT(11NOT NULL,
  RECEIVED DATE NOT NULL,
  STATUS VARCHAR(30NOT NULL
ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO REPAIR_ORDER(ID,COMPUTER_ID,RECEIVED,STATUSVALUES
(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:
IDBRANDMODELSERIALPRICE
1BRAND AMODEL 1A1B2C3D4500
2BRAND BMODEL 2AB12CD34550
3BRAND CMODEL 3ABC123D4600
4BRAND DMODEL 4ABCD1234650
5BRAND EMODEL 51234ABCD700
6BRAND FMODEL 6ABCDEFGH750
7BRAND GMODEL 712345678800
8BRAND HMODEL 81234ABCD850
9BRAND IMODEL 912AB3456900
10BRAND JMODEL 10ABC12DEF950
Table 1. Computer table rows
SELECT * FROM `repair_order`
Script 3. Query to retrieve Repair_oreder  table rows
Result:
IDCOMPUTER_IDRECEIVEDSTATUS
122019-01-05PENDING
152019-02-10COMPLETED
172019-05-02COMPLETED
192019-06-01PENDING
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:
IDBRANDMODELSERIALPRICE
1BRAND AMODEL 1A1B2C3D4500
3BRAND CMODEL 3ABC123D4600
4BRAND DMODEL 4ABCD1234650
6BRAND FMODEL 6ABCDEFGH750
8BRAND HMODEL 81234ABCD850
10BRAND JMODEL 10ABC12DEF950
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:
IDBRANDMODELSERIALPRICE
1BRAND AMODEL 1A1B2C3D4500
3BRAND CMODEL 3ABC123D4600
4BRAND DMODEL 4ABCD1234650
6BRAND FMODEL 6ABCDEFGH750
8BRAND HMODEL 81234ABCD850
10BRAND JMODEL 10ABC12DEF950
Table 3. SQL SELECT ALL EXCEPT emulation example 2 result

0 comments:

Post a Comment