How to Check if a Row Exists in the MySQL Table

1. Different Ways to Check if a Row Exists in MySQL Table
2. Use the EXISTS Operator to Check if a Row (Record) Exists in MySQL Table
3. Use the NOT EXISTS Operator to Check if a Row (Record) Does Not Exist in MySQL Table
4. Use the EXISTS/NOT EXISTS Operator With the IF() Function to Check if a Row Exists in MySQL Table

This article highlights the different ways to check if a row exists in the MySQL table. We will use the EXISTS and NOT EXISTS operators.

We can also use these two operators with the IF() function to get a meaningful message if a row (a record) is found.

Different Ways to Check if a Row Exists in MySQL Table

We can use the following methods to check if a row exists in the MySQL table or not.

1. Use the EXISTS operator to check if a row exists.
2. Use the NOT EXISTS operator to check if a row does not exist.
3. Use the EXISTS/NOT EXISTS operator with the IF() function.

We should have a table to use all of the approaches mentioned above.

For that, we create a table named person with ID and NAME attributes (columns). You can also create and populate it with some data by using the following queries.

Create Table:

/*
create a table named `person`.
Here, the schema (database)
name is `ms20`
*/

CREATE TABLE `ms20`.`person` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ID`));

Populate Table (Insert Data):

/*
populate the table with some data. Here,
we are only inserting the names because
ID is auto increment, and we don't need to
insert that.
*/

INSERT INTO ms20.person (NAME) VALUES
('Mehvish'),
('Thomas'),
('John'),
('Daniel');

/*
The following query can be used if we want to insert
a custom ID rather than the auto-incremented one
*/

INSERT INTO ms20.person (ID, NAME) VALUES (6,'Sara');

Display Table:

SELECT * FROM ms20.person;

Output:

Use the EXISTS Operator to Check if a Row (Record) Exists in MySQL Table

Example Code:

SELECT EXISTS (
    SELECT NAME FROM ms20.person
    WHERE ID = 6) as OUTPUT;

Output (if record found):

OUTPUT
1

Example Code:

SELECT EXISTS (
    SELECT NAME FROM ms20.person
    WHERE ID = 7) as OUTPUT;

Output (if record not found):

OUTPUT
0

The EXISTS operator checks whether a record (that meets the specified condition) is present in the table. It is used in combination with another subquery that may or may not be satisfied.

The EXISTS operator returns true if the subquery finds at least one record. The true and false are represented with 1 and 0, respectively.

We can use the EXISTS clause with other MySQL commands, including SELECT, INSERT, UPDATE, DELETE. Additionally, the further processing is terminated by the EXISTS clause once it successfully finds a row that meets the specified condition.

This technique helps boost the query’s performance, specifically when we are searching in a table with thousands of records.

Use the NOT EXISTS Operator to Check if a Row (Record) Does Not Exist in MySQL Table

Example Code:

SELECT NOT EXISTS (
    SELECT NAME FROM ms20.person WHERE ID = 7)
    As RESULT;

Output (if record not found):

RESULT
1

Example Code:

SELECT NOT EXISTS (
    SELECT NAME FROM ms20.person WHERE ID = 6)
    As RESULT;

Output (if record found):

RESULT
0

The NOT EXISTS operator works opposite the EXISTS operator and returns true (represented with 1) if the table does not contain the row with a given condition. If the record is found in the table, the NOT EXISTS returns false, represented with 0.

We can use the NOT EXISTS operator with MySQL 8.0.19 or above. It can also be used with a TABLE in a subquery, for instance, SELECT c1 FROM t1 WHERE EXISTS(TABLE t2);.

Use the EXISTS/NOT EXISTS Operator With the IF() Function to Check if a Row Exists in MySQL Table

Example Code (with EXISTS operator):

SELECT IF ( EXISTS
           ( SELECT NAME FROM ms20.person WHERE ID = 7) ,
           "FOUND", "NOT FOUND");

Output:

NOT FOUND

This approach is more user-friendly than Boolean values (1 or 0), which are hard to remember.

The above query returns "FOUND" if there is a record with ID 7 in the person table. Otherwise, we will get "NOT FOUND".

Here, you might be wondering which section of the query will be executed first. In that case, you can see the following screenshot to know the execution sequence of the query given above.

Similarly, we can use the NOT EXISTS with the IF() function as follows, where we get YES if the record is not found in the specified table; otherwise, NO.

Example Code (with NOT EXISTS operator):

SELECT IF ( NOT EXISTS
           ( SELECT NAME FROM ms20.person WHERE ID = 7) ,
           "YES", "NO");

Output:

YES

Remember, we can use the SELECT column, SELECT someConstant, SELECT * or something else in a subquery. The output would be the same because the SELECT list (which appears due to the SELECT clause) is ignored by MySQL.