joins - inner, natural, union, subquery, <=> operator

use test;
create table t1 ( id int not null auto_increment primary key, value varchar(20));

insert into t1 (value)
select 'ass'
union all
select 'foo'
union all
select 'bar'
union all
select 'moo'
union all
select 'choo';

insert into t1 (value)
select '767'
union all
select '76876'
union all
select '76876'
union all
select '7'
union all
select '768';

select * from t1;

create table t2 ( id int not null auto_increment primary key, value varchar(20) not null);

insert into t2 (value)
select 'ass'
union all
select 'foo'
union all
select 'bur'
union all
select 'moo poio'
union all
select 'choo loo';

select * from t2;

-- inner join
select t1.id as T1Id, t1.value as T1Val, t2.id as T2Id, t2.value as T2Val from t1 inner join t2 on t1.value = t2.value;
-- left join
select t1.id as T1Id, t1.value as T1Val, ifnull(t2.id, 'N/A') as T2Id, ifnull(t2.value, 'N/A') as T2Val from t1 left join t2 on t1.value = t2.value;
-- right join
select ifnull(t1.id, 'N/A') as T1Id, ifnull(t1.value, 'N/A') as T1Val, ifnull(t2.id, 'N/A') as T2Id, ifnull(t2.value, 'N/A') as T2Val from t1 right join t2 on t1.value = t2.value;
-- natural left join
select t1.id as T1Id, t1.value as T1Val, t2.id as T2Id, t2.value as T2Val from t1 natural left join t2;
-- natural right join
select t1.id as T1Id, t1.value as T1Val, t2.id as T2Id, t2.value as T2Val from t1 natural right join t2;
-- = 
select t1.id as T1Id, t1.value as T1Val, t2.id as T2Id, t2.value as T2Val from t1 inner join t2 using (id);

select t1.id as T1Id, t1.value as T1Val, t2.id as T2Id, t2.value as T2Val from t1 inner join t2 using (id, value);

select t1.id as T1Id, t1.value as T1Val, t2.id as T2Id, t2.value as T2Val from t1 left join t2 using (value);

select * from t1 union select * from t2 order by value asc;

-- union
select * from t1 union select * from t2 order by id desc, value desc;

select * from t1 left join t2 on t1.id = t2.id union select * from t1 right join t2 on t1.id = t2.id;

-- subquery
select * from t1 where t1.id in (select id from t2);
select * from t1 where t1.id not in (select id from t2);
select t1.* from t1 where t1.id in (select t2.id from t2 where t2.value = t1.value);
select id, value from t1 where t1.value not in (select value from t2);

select t1.id, t1.value from t1 inner join t2 on t1.id = t2.id and t1.value != t2.value;
select t1.id, t1.value, t2.id, t2.value from t1 inner join t2 on t1.id != t2.id and t1.value != t2.value;

create table payment ( id int not null auto_increment primary key, value int not null);

insert into payment (value)
select 2300
union all
select 1400
union all
select 65765
union all
select 76876
union all
select 7687;

select * from payment;

select pt.value, pt.id from payment pt where pt.value <= (select avg(value) from payment pt1 where pt1.id = pt.id) ;

select * from t1;
select * from t2;

select t1.id as A, t1.value as B, t2.id as X, t2.value as Y from t1 left join t2 on t1.id = t2.id where t2.id = NULL;
select t1.id as A, t1.value as B, t2.id as X, t2.value as Y from t1 left join t2 on t1.id = t2.id where t2.value = NULL;

select t1.id as A, t1.value as B, t2.id as X, t2.value as Y from t1 left join t2 on t1.id = t2.id where t2.id <=> NULL;
select t1.id as A, t1.value as B, t2.id as X, t2.value as Y from t1 left join t2 on t1.id = t2.id where not (t2.value <=> NULL);
select t1.id as A, t1.value as B, t2.id as X, t2.value as Y from t1 inner join t2 on t1.id <=> t2.id;


select t1.id, t1.value, t2.id, t2.value from t1 right join t2 on t1.id = t2.id where t2.id = NULL;