CODEWITHSUNDEEP
mysqlsqlleft-join
user1785721
user1785721

Reputation:

LEFT JOIN and IS NULL effect

At http://dev.mysql.com/doc/refman/5.5/en/left-join-optimization.html one can read:

If you use LEFT JOIN to find rows that do not exist in some table and you have the following test: col_name IS NULL in the WHERE part, where col_name is a column that is declared as NOT NULL, MySQL stops searching for more rows (for a particular key combination) after it has found one row that matches the LEFT JOIN condition.

Can any body explain me what that mean in a human readable language? or in a newbie readable language ;)

Upvotes: 3

Views: 683

Answers (2)

John Woo
John Woo

Reputation: 263883

Only rows that has no match on the right table will only be returned. This can be illustrated as:

enter image description here

As you can see, there are two tables being joined. The shaded part are the records which do not exists on Table B.

Upvotes: 2

Hammerite
Hammerite

Reputation: 22350

A left join always joins at least one row in the right-hand table for each row in the left-hand table, even if there are no rows in the right-hand table that match the join condition. If this happens, then the columns from the right-hand table for that row will all be null.

This means that performing a left join and then testing for null (in a not null column) in a right hand table column is an effective idiom for testing for rows that have no corresponding row in the joined table.

The excerpt you quote is saying that MySQL is intelligent enough when carrying out a query of this form to stop joining more rows from the right-hand table once the first one has been joined (because it would be a waste of time to do so - you've made it clear that you are looking for rows with no match in the right-hand table, so looking for more rows once at least one has been found would serve no purpose).

Upvotes: 0

Related Questions