Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment