3 ms·

As soon as you filter on the table you’re joining with in the where clause it becomes an inner join. FROM foo LEFT JOIN bar ON ... /* Makes it an i

• astine 8y ago
  Only if you're resting for a non null value. If you test for a null value, you'll be looking for all values in one table that don't have a match in the other table.
  • Svip 8y ago
    Indeed, a faster way to check if a table does _not_ have a comparable row is a LEFT JOIN with a WHERE NULL clause: SELECT t1.* FROM t1 LEFT JOIN t2 ON t2.id = t1.foreign_id WHERE t2.id IS NULL Is a lot faster than the naïve alternative: SELECT t1.* FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE id = t1.foreign_id)
    • brokensegue 8y ago
      Unless the query is optimized?
      • Svip 8y ago
        For a simple query like this, then yes, it will be optimised. But once your queries get more complicated, the optimiser might not be able to make that deduction on its own.