Syntax error Except not working in MySQL?

Except not working in MySQL?

You cannot use except in MySQL. You can work with NOT IN operator to get the same result. Let us first create a table −

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

Insert some records in the table using insert command −

mysql> insert into DemoTable1 values(100);
Query OK, 1 row affected (0.14 sec)

mysql> insert into DemoTable1 values(200);
Query OK, 1 row affected (0.13 sec)

mysql> insert into DemoTable1 values(300);
Query OK, 1 row affected (0.13 sec)

Display all records from the table using select statement:

mysql> select *from DemoTable1

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 learn how to use NOT IN operator in place of except −

mysql> select Number1 from DemoTable1
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

542 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements