Syntax error How to find all records which are NOT in the set array with MySQL?

How to find all records which are NOT in the set array with MySQL?

For this, you can use NOT IN() function.

Let us first create a table −

mysql> create table DemoTable718 (
   Id int,
   FirstName varchar(100),
   Age int
);
Query OK, 0 rows affected (0.59 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable718 values(101,'Chris',26);
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable718 values(102,'Robert',24);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable718 values(103,'David',27);
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable718 values(104,'Mike',28);
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable718 values(105,'Sam',23);
Query OK, 1 row affected (0.14 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable718;

This will produce the following output -

+------+-----------+------+
| Id   | FirstName | Age  |
+------+-----------+------+
| 101  | Chris     | 26   |
| 102  | Robert    | 24   |
| 103  | David     | 27   |
| 104  | Mike      | 28   |
| 105  | Sam       | 23   |
+------+-----------+------+
5 rows in set (0.00 sec)

Here is the query to find all records which are NOT in the set array −

mysql> select *from DemoTable718 where Id NOT IN('101','103','104');

This will produce the following output -

+------+-----------+------+
| Id   | FirstName | Age  |
+------+-----------+------+
| 102  | Robert    | 24   |
| 105  | Sam       | 23   |
+------+-----------+------+
2 rows in set (0.00 sec)
Updated on: 2019-08-22T07:17:39+05:30

3K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements