Subqueries with EXISTS vs IN – MySQL

by

An Explain Plan would have shown you why exactly you should use Exists. Usually the question comes Exists vs Count(*). Exists is faster. Why?

  • With regard to challenges present by NULL: when subquery returns Null, for IN the entire query becomes Null. So you need to handle that as well. But using Exist, it’s merely a false. Much easier to cope. Simply IN can’t compare anything with Null but Exists can.

  • e.g. Exists (Select * from yourtable where bla="blabla"); you get true/false the moment one hit is found/matched.

  • In this case IN sort of takes the position of the Count(*) to select ALL matching rows based on the WHERE because it’s comparing all values.

But don’t forget this either:

  • EXISTS executes at high speed against IN : when the subquery results is very large.
  • IN gets ahead of EXISTS : when the subquery results is very small.

Reference to for more details:

More Related Contents:

  1. i have an error which i dont understand. can you help me? [closed]
  2. If two columns are 1 then make new column
  3. What is better subquery vs literal on IN clause in mysql
  4. Getting the SUM() of claps and at the same time counting bookmarks in one query
  5. Remove duplicate rows in MySQL
  6. How to do the Recursive SELECT query in MySQL?
  7. MySQL : isn’t in GROUP BY
  8. MySQL: Can’t create table (errno: 150)
  9. MySQL DELETE FROM with subquery as condition
  10. Restore table structure from frm and ibd files
  11. MySQL/MariaDB – order by inside subquery
  12. 60 million entries, select entries from a certain month. How to optimize database?
  13. Checking multiple columns for one value
  14. Mysql Improve Search Performance with wildcards (%%)
  15. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  16. Error Dropping Database (Can’t rmdir ‘.test\’, errno: 17)
  17. Whats the fastest way to lookup big tables for points within radius MySQL (latitude longitude)
  18. Composite key as foreign key (sql)
  19. #1292 – Incorrect date value: ‘0000-00-00’ [duplicate]
  20. mySQL query to search all tables within a database for a string?
  21. SQL Print the name of all Employees together with the name of their supervisor
  22. MySQL offset infinite rows
  23. SQL Selecting from two Tables with inner join and limit [duplicate]
  24. Setting up foreign keys in phpMyAdmin?
  25. Invalid default value for ‘dateAdded’
  26. Split a string and loop through values in MySQL stored procedure
  27. How to create dummy variable columns for thousands of categories in Google BigQuery?
  28. Most efficient way to store IP Address in MySQL [duplicate]
  29. MySQL OPTIMIZE all tables?
  30. Deleting a row based on the max value

Leave a Comment