Thursday, October 16, 2008

mysql - find missing rows in a join

The left join can be used to get all rows from a secondary table matching the rows of the first table, along with those rows from the first table including any rows that doesn't find a match on the secondary table. for rows with no match, NULL would be returned on the secondary table column. this can be useful when you want to find the rows that have no match:

select, from a left join b on ( where is null;

And most times, you want to correct this by providing a good value for the NULL column in the secondary table. If you want to set all those rows with a NULL column to the same value, you can do:

update a left join b on ( set a.secid=12629002 where is null;

No comments: