Why should I use EXISTS() function in MySQL?

I have this query:

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

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

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

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

Answer

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 matching the criteria)
• EXISTS is 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.