drop temporary table if exists left1; drop temporary table if exists left2; drop temporary table if exists right1; drop temporary table if exists right2; drop temporary table if exists right3; create temporary table left1 ( col1 int unsigned not null auto_increment primary key, col2 bit not null default 0, col3 bit not null default 0 ) type=MyISAM; -- Exactly the same as left1, but will only have one row. create temporary table left2 ( col1 int unsigned not null auto_increment primary key, col2 bit not null default 0, col3 bit not null default 0 ) type=MyISAM; create temporary table right1 ( col1 int unsigned not null auto_increment primary key, col2 bit not null default 0, col3 int unsigned not null, col4 varchar(50) ) type=MyISAM; -- Same as right1, except has no col4 create temporary table right2 ( col1 int unsigned not null auto_increment primary key, col2 bit not null default 0, col3 int unsigned not null ) type=MyISAM; -- Same as right1, except col4 comes before col3 create temporary table right3 ( col1 int unsigned not null auto_increment primary key, col2 bit not null default 0, col4 varchar(50), col3 int unsigned not null ) type=MyISAM; insert into left1 (col3) values (1), (1); insert into left2 (col3) values (1); insert into right1 (col3) select 1; insert into right2 (col3) select 1; insert into right3 (col3) select 1; select * from left1; select * from left2; select * from right1; -- Row 1 should succeed. Row 2 should fail. Both fail. select left1.col1 as col1, left1.col2 + 0 as col2, left1.col3 + 0 as col3, right1.col1 as col1, right1.col2 + 0 as col2, right1.col3 as col3, right1.col4 as col4 from left1 left outer join right1 on left1.col1 = right1.col3 and right1.col2 <> 1 where left1.col2 <> 1 order by left1.col1; -- There is only one row and it should succeed. It does. -- The only difference from above is one less row in the left-hand table. -- Uses left2 instead of left1. select * from left2 left outer join right1 on left2.col1 = right1.col3 and right1.col2 <> 1 where left2.col2 <> 1; -- Row 1 should and does succeed. Row 2 should and does fail. -- Part of the join clause is commented out. select * from left1 left outer join right1 on left1.col1 = right1.col3 -- and right1.col2 <> 1 where left1.col2 <> 1; -- Row 1 should and does succeed. Row 2 should and does fail. -- Uses right2 instead of right1. select * from left1 left outer join right2 on left1.col1 = right2.col3 and right2.col2 <> 1 where left1.col2 <> 1; -- Row 1 should and does succeed. Row 2 should and does fail. -- Uses right3 instead of right1. select * from left1 left outer join right3 on left1.col1 = right3.col3 and right3.col2 <> 1 where left1.col2 <> 1;