coding style - Is using “NOT EXISTS” considered to be bad SQL practise? -

i have heard lot of people on years that:

"join" operators preferred on “not exists”

why?

in mysql, oracle, sql server , postgresql, not exists of same efficiency or more efficient left join / null.

while may seem "the inner query should executed each record outer query" (which seems bad not exists , worse not in, since latter query not correlated), may optimized other queries optimized, using appropriate anti-join methods.

in sql server, actually, left join / null may less efficient not exists / not in in case of unindexed or low cardinality column in inner table.

it heard mysql "especially bad in treating subqueries".

this roots fact mysql not capable of join methods other nested loops, severely limits optimization abilities.

the case when query benefit rewriting subquery join this:

select  *    big_table   big_table_column in         (         select  small_table_column            small_table         ) 

small_table not queried each record in big_table: though not seem correlated, implicitly correlated query optimizer , in fact rewritten exists (using index_subquery search first if needed if small_table_column indexed)

but big_table leading, makes query complete in big * log(small) rather small * log(big) reads.

this rewritten as

select  distinct bt.*    small_table st join    big_table bt on      bt.big_table_column = st.small_table_column 

however, won't improve not in (as opposed in). in mysql, not exists , left join / null same, since nested loops left table should leading in left join.

you may want read these articles:

• not in vs. not exists vs. left join / null: sql server
• not in vs. not exists vs. left join / null: postgresql
• not in vs. not exists vs. left join / null: oracle
• not in vs. not exists vs. left join / null: mysql
• in vs. join vs. exists: oracle
• in vs. join vs. exists (sql server)