Best way to test if a row exists in a MySQL table

To test whether a row exists in a MySQL table or not, use exists condition. The exists condition can be used with subquery. It returns true when row exists in the table, otherwise false is returned. True is represented in the form of 1 and false is represented as 0.

For better understanding, firstly we will create a table with the help of CREATE command. The following is the query to create a table −

mysql> CREATE table ExistsRowDemo
-> (
-> ExistId int,
-> Name varchar(100)
-> );
Query OK, 0 rows affected (0.53 sec)

After creating the table successfully, we will insert some records with the help of INSERT command. The query to insert records into the table −

mysql> INSERT into ExistsRowDemo values(100,'John');
Query OK, 1 row affected (0.16 sec)

mysql> INSERT into ExistsRowDemo values(101,'Bob');
Query OK, 1 row affected (0.17 sec)

mysql> INSERT into ExistsRowDemo values(103,'Carol');
Query OK, 1 row affected (0.20 sec)

mysql> INSERT into ExistsRowDemo values(104,'David');
Query OK, 1 row affected (0.13 sec)

After inserting all the records, we can display them with the help of SELECT command, which is as follows −

mysql> SELECT * from ExistsRowDemo;

The following is the output −

+---------+-------+
| ExistId | Name  |
+---------+-------+
| 100     | John  |
| 101     | Bob   |
| 103     | Carol |
| 104     | David |
+---------+-------+
4 rows in set (0.00 sec)

We added some records into the table. The syntax to check whether a row exists in a table or not with the help of EXISTS condition is as follows −

SELECT EXISTS(SELECT * FROM yourTableName WHERE yourCondition);

I am applying the above query to get the result −

Note: Firstly, I am considering the condition when row exists in the table. After that, the
condition will be mentioned when a row does not exist.

Case 1

In this case, I am giving a condition when row exists. Let us apply the the above syntax to test whether row exists or not.

mysql> SELECT EXISTS(SELECT * from ExistsRowDemo WHERE ExistId=104);

The following is the output −

+------------------------------------------------------+
| EXISTS(SELECT * from ExistsRowDemo WHERE ExistId=104)|
+------------------------------------------------------+
| 1                                                    |
+------------------------------------------------------+
1 row in set (0.00 sec)

From the above sample output, it is clear that row exists, since the value we got is 1. This means TRUE!

Case 2

In this case, I am explaining the condition when row does not exist. Applying the above query.

mysql> SELECT EXISTS(SELECT * from ExistsRowDemo WHERE ExistId=105);

The following is the output −

+------------------------------------------------------+
| EXISTS(SELECT * from ExistsRowDemo WHERE ExistId=105)|
+------------------------------------------------------+
| 0                                                    |
+------------------------------------------------------+
1 row in set (0.00 sec)

From the above output, we can see the output is 0 i.e. false (row does not exist).