Display all records except one in MySQL

You can use IN() to display all records except one in MySQL. Let us first create a table −

mysql> create table DemoTable
(
   Id int,
   FirstName varchar(20)
);
Query OK, 0 rows affected (0.57 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values(100,'Larry');
Query OK, 1 row affected (0.31 sec)
mysql> insert into DemoTable values(10,'Chris');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable values(110,'Robert');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values(90,'David');
Query OK, 1 row affected (0.20 sec)

Following is the query to display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+------+-----------+
| Id   | FirstName |
+------+-----------+
| 100  | Larry     |
| 10   | Chris     |
| 110  | Robert    |
| 90   | David     |
+------+-----------+
4 rows in set (0.00 sec)

Here is the query to display all records except one in MySQL −

mysql> select *from DemoTable where Id IN(110,90,100);

This will produce the following output −

+------+-----------+
| Id   | FirstName |
+------+-----------+
| 100  | Larry     |
| 110  | Robert    |
| 90   | David     |
+------+-----------+
3 rows in set (0.00 sec)

Arjun Thakur

Published on 29-Apr-2019 10:55:25

Previous Page Print Page