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

• Related Questions & Answers
• Display NULL and NOT NULL records except a single specific value in MySQL
• Display all records ignoring the current date record in MySQL
• Ordering alphabetically in MySQL except for one entry?
• How to display all the MySQL tables in one line?
• How to select all rows from a table except the last one in MySQL?
• Display records ignoring NULL in MySQL
• Select all rows except from today in MySQL?
• MongoDB query to display all the fields value, except _id
• How to remove all objects except one or few in R?
• How to select all columns except one in a Pandas DataFrame?
• Take all records from one MySQL table and insert it to another?
• How to remove Duplicate Records except a single record in MySQL?
• How to select all the records except a row with certain id from a MySQL table?
• Find and display duplicate records in MySQL?
• Display records by grouping dates in MySQL