MySQL - different joins on multiple same value of field from two tables

create table logic_test(id int);

insert into  logic_test values(1);
insert into  logic_test values(1);

select * from logic_test;
/*
# id
'1'
'1'
*/

create table logic_test2(id int);
insert into  logic_test2 values(1);
insert into  logic_test2 values(1);

insert into  logic_test2 values(1);
insert into  logic_test2 values(1);

insert into  logic_test2 values(1);
insert into  logic_test2 values(1);

select * from logic_test2;
/*
# id
'1'
'1'
'1'
'1'
'1'
'1'
*/

select lt.id AS idLeft, lt2.id idRight from logic_test lt
left join logic_test2 lt2 on lt.id=lt2.id;
-- 12 rows returned
/*
# idLeft, idRight
'1', '1'
'1', '1'
'1', '1'
'1', '1'
'1', '1'
'1', '1'
'1', '1'
'1', '1'
'1', '1'
'1', '1'
'1', '1'
'1', '1'
*/
select lt.id AS idLeft, lt2.id idRight from logic_test lt
right join logic_test2 lt2 on lt.id=lt2.id;
-- 12 rows returned
/*
# idLeft, idRight
'1', '1'
'1', '1'
'1', '1'
'1', '1'
'1', '1'
'1', '1'
'1', '1'
'1', '1'
'1', '1'
'1', '1'
'1', '1'
'1', '1'
*/
select lt.id AS idLeft, lt2.id idRight from logic_test lt
 join logic_test2 lt2 on lt.id=lt2.id ;
 -- 12 rows returned
 /*
 1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
 */
-- There is no full outer join mysql instead use below logic
/* http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql*/
/*
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
 */

SELECT * FROM logic_test lt
LEFT JOIN logic_test2 lt2 ON lt.id = lt2.id
UNION
 SELECT * FROM logic_test lt
RIGHT JOIN logic_test2 lt2 ON lt.id = lt2.id


This is FAQ in DWH--BI.