What’s the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?

by

  • NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server

  • NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: PostgreSQL

  • NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: Oracle

  • NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL

In a nutshell:

NOT IN is a little bit different: it never matches if there is but a single NULL in the list.

  • In MySQL, NOT EXISTS is a little bit less efficient

  • In SQL Server, LEFT JOIN / IS NULL is less efficient

  • In PostgreSQL, NOT IN is less efficient

  • In Oracle, all three methods are the same.

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)