Syntax error What is the equivalent of EXCEPT in MySQL?

What is the equivalent of EXCEPT in MySQL?

You cannot use EXCEPT in MySQL, instead use the NOT IN operator. Let us first create a table −

mysql> create table DemoTable
   (
   Number1 int
   );
Query OK, 0 rows affected (0.71 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values(100);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values(200);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values(300);
Query OK, 1 row affected (0.13 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable;

This will produce the following output −

+---------+
| Number1 |
+---------+
| 100     |
| 200     |
| 300     |
+---------+
3 rows in set (0.00 sec)

Following is the query to create second table −

mysql> create table DemoTable2
   (
   Number1 int
   );
Query OK, 0 rows affected (0.52 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable2 values(100);
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable2 values(400);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable2 values(300);
Query OK, 1 row affected (0.11 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable2;

This will produce the following output −

+---------+
| Number1 |
+---------+
| 100     |
| 400     |
| 300     |
+---------+
3 rows in set (0.00 sec)

Following is the query to use NOT IN operator in place of EXCEPT −

mysql> select Number1 from DemoTable
where Number1 not in (SELECT Number1 FROM DemoTable2);

This will produce the following output −

+---------+
| Number1 |
+---------+
| 200     |
+---------+
1 row in set (0.04 sec)
Updated on: 2019-07-30T22:30:26+05:30

4K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements