JIYIK CN >

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

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

Different ways to check if a row exists in a MySQL table

We can use the following method to check if a row exists in a MySQL table.

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

For this purpose, we created a persontable named with IDand NAMEattributes (columns). You can also create and populate it with some data using the following query.

Create the 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 the 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:

Check if a row (record) exists in a MySQL table using EXISTSthe operator

Sample code:

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

Output (if record found):

OUTPUT
1

Sample code:

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

Output (if no record found):

OUTPUT
0

EXISTSThe operator checks if a record exists in a table (satisfies the specified condition). It is used in conjunction with another subquery which may or may not satisfy the condition.

EXISTSThe operator returns if the subquery finds at least one record true. trueand are represented by and , falserespectively .10

We can EXISTSuse the clause with other MySQL commands including SELECT, INSERT, UPDATE, DELETE. In addition, further processing is EXISTSterminated by the clause once a row that satisfies the specified condition is successfully found.

This technique helps improve the performance of queries, especially when we are searching in a table containing thousands of records.

Use NOT EXISTSthe operator to check if a row(record) does not exist in a MySQL table

Sample code:

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

Output (if no record found):

RESULT
1

Sample code:

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

Output (if record found):

RESULT
0

NOT EXISTSThe operator EXISTSis the opposite of the operator and returns if the table does not contain a row with the given condition true( 1denoted by ). If the record is found in the table, it NOT EXISTSreturns false, 0denoted by .

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

Use the EXISTS/ NOT EXISTSoperator and IF()the function to check if a row exists in a MySQL table

Example code (with EXISTSoperator):

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

Output:

NOT FOUND

This approach is easier to use than the hard-to-remember Boolean ( 1or ).0

If there is a record with 7 personin the table , the above query returns . Otherwise, we will get .ID"FOUND""NOT FOUND"

Here, you might be wondering which part of the query will be executed first. In that case, you can look at the following screenshot to understand the order of execution of the queries given above.

Similarly, we can use NOT EXISTSwith IF()the function as shown below, if the record is not found in the specified table, we will get YES; otherwise, 否.

Example code (using NOT EXISTSthe operator):

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

Output:

YES

Remember that we can use SELECT column, SELECT someConstant, , SELECT *or anything else in the subquery. The output will be the same because MySQL ignores SELECTthe _list ( SELECTwhich is present because of the _clause).