this is obsolete doc -- see http://doc.nethence.com/ instead

Playing with multiple outer joins 

Introduction 

Alghough announced in the guide, full outer join doesn't seem to work, 

Hence I am simulating it, as advertised on several places. Problem is, I want to fully join three tables. Plan A is simple, with tables B and C refereing to the same table A. Plan B is a bit more complicated as table B refers to table A and table C refers to table B to release the multiple fully outer joined result. 

Plan A] one user table, two tables depending on it 

Example presentation, 

- user1, Maria has both 

- user2, John only has the badge 

- user3, Joceline only has the car 

- user4, Benjamin has nothing 

Here it goes, 

create table users (user varchar(64), primary key (user)); 

insert into users values ('user1'); 

insert into users values ('user2'); 

insert into users values ('user3'); 

insert into users values ('user4'); 

create table vehicles (vehicle varchar(64), user varchar(64), primary key (vehicle)); 

insert into vehicles values ('vehicle1', 'user1'); 

insert into vehicles values ('vehicle2', 'user3'); 

insert into vehicles values ('vehicle3', 'userextravehicle'); 

insert into vehicles values ('vehicle4', NULL); 

create table badges (badge varchar(64), user varchar(64), primary key (badge)); 

insert into badges values ('badge1', 'user1'); 

insert into badges values ('badge2', 'user2'); 

insert into badges values ('badge3', 'userextrabadge'); 

insert into badges values ('badge4', NULL); 

Query explanation, 

- 1st query = outer users 

- 2nd query = outer vehicles 

- 3rd query = outer badges which needs outer users to show user2 

Note. as badges also depends on users, I don't need outer vehicles. 

Here it goes, 

select users.user, vehicles.vehicle, vehicles.user, badges.badge, badges.user 

from users 

left join vehicles on users.user = vehicles.user 

left join badges on badges.user = users.user 

union all 

select users.user, vehicles.vehicle, vehicles.user, badges.badge, badges.user 

from users 

right join vehicles on users.user = vehicles.user 

left join badges on badges.user = users.user 

where users.user is null 

union all 

select users.user, vehicles.vehicle, vehicles.user, badges.badge, badges.user 

from users 

left join vehicles on users.user = vehicles.user 

right join badges on badges.user = users.user 

where users.user is null 

Plan B] inter-dependent tables 

Example presentation, 

- user1 has a vehicle with an antenna 

- user2 has a vehicle without an antenna 

(cannot set a user with only an antenna because the antenna depends on the vehicles' table) 

- userextra3 has a vehicle and an antenna 

- userextra4 has a vehicle 

- anothervehicle is not known in the vehicles table 

Here it goes, 

drop table users; 

drop table vehicles; 

drop table badges; 

drop table antennas; 

create table users (user varchar(64), primary key (user)); 

insert into users values ('user1'); 

insert into users values ('user2'); 

create table vehicles (vehicle varchar(64), user varchar(64), primary key (vehicle)); 

insert into vehicles values ('vehicle1', 'user1'); 

insert into vehicles values ('vehicle2', 'user2'); 

insert into vehicles values ('vehicle3', 'userextra3'); 

insert into vehicles values ('vehicle4', 'userextra4'); 

insert into vehicles values ('vehicle5', NULL); 

create table antennas (antenna varchar(64), vehicle varchar(64), primary key (antenna)); 

insert into antennas values ('antenna1', 'vehicle1'); 

insert into antennas values ('antenna2', 'vehicle3'); 

insert into antennas values ('antenna3', 'anothervehicule'); 

insert into antennas values ('antenna4', NULL); 

Query explanation, 

- 1st stanza = outer users 

- 2nd stanza = outer vehicles 

- 3rd stanza = outer antennas which only needs outer vehicules (hence right right) 

Here it goes, 

select users.user, vehicles.vehicle, vehicles.user, antennas.antenna, antennas.vehicle 

from users 

left join vehicles on users.user = vehicles.user 

left join antennas on vehicles.vehicle = antennas.vehicle 

union all 

select users.user, vehicles.vehicle, vehicles.user, antennas.antenna, antennas.vehicle 

from users 

right join vehicles on users.user = vehicles.user 

left join antennas on vehicles.vehicle = antennas.vehicle 

where users.user is null 

union all 

select users.user, vehicles.vehicle, vehicles.user, antennas.antenna, antennas.vehicle 

from users 

right join vehicles on users.user = vehicles.user 

right join antennas on vehicles.vehicle = antennas.vehicle 

where users.user is null and vehicles.vehicle is null