Archive for May 27th, 2010
In one of the previous articles I discussed performance of the three methods to implement an anti-join in MySQL.
Just a quick reminder: an anti-join is an operation that returns all records from one table which share a value of a certain column with no records from another table.
In SQL, there are at least three methods to implement it:
LEFT JOIN / IS NULL
SELECT o.* FROM outer o LEFT JOIN inner i ON i.value = o.value WHERE i.value IS NULL
SELECT o.* FROM outer o WHERE o.value NOT IN ( SELECT value FROM inner )
SELECT o.* FROM outer o WHERE NOT EXISTS ( SELECT NULL FROM inner i WHERE i.value = o.value )
inner.value is marked as
NOT NULL, all these queries are semantically equivalent and with proper indexing have similarly optimized execution plans in MySQL.
Now, what if
inner.value is not nullable and does contain some
Let's create some sample tables:
Read the rest of this entry »