How to Check If Value Exists in a MySQL Table

Published Aug 11, 2022

We can easily check if a value exists in a MySQL table using SELECT EXISTS.

1. Using SELECT EXISTS

We can combine SELECT EXISTS with SELECT 1 to achieve this.

SELECT EXISTS(SELECT 1 FROM table WHERE condition = value)
  • SELECT 1 will return 1 for every row that satisfies the condition, otherwise it will not return anything.
  • SELECT EXISTS will return 1 or 0 depending on the existence of any record (this is useful if we explicitly need a 0 for rows that do not satisfy a condition).

We could technically run SELECT EXISTS(SELECT * ...), but it has been proven not to affect performance.

From the MySQL documentation:

Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference.

2. Using SELECT EXISTS with LIMIT 1

Adding a LIMIT clause will allow the query engine to halt after finding a single row.

SELECT EXISTS(SELECT 1 FROM table WHERE condition = value LIMIT 1)

To avoid a long column name, we can add an alias (e.g. AS 'does_exist') to our query.