Stack Stack - 1 year ago 28
SQL Question

Why should I use EXISTS() function in MySQL?

I have this query:

SELECT * FROM mytable t1
EXISTS(SELECT 1 FROM t2 WHERE t2.post_id = :id)

And when I remove that
function, still my code works:

SELECT * FROM mytable t1
(SELECT 1 FROM t2 WHERE t2.post_id = :id LIMIT 1)

So why should I write that? What's its advantage?


In short:

  • EXISTS returns when it finds the first result instead of fetching all matching records (so it is more efficient when there are multiple records mathing the criterias)
  • EXISTS is the semantically correct.
  • When there is a column name instead of 1 in the second query, and the column contains NULL, FALSE, 0, etc, MySQL will implicitly convert it to FALSE, which leads to a false result.
  • EXISTS is actually defined by the ANSI standard, while the second form is not. (The second query may fail in other DBMS)

As an extra side note, you are fine with * too when you are using EXISTS, since it checks if there is a matching record, not the value.