Nethence Documentation Lab Webmail Your IP BBDock  


Those documents are obsolete, please use the Nethence Documentation instead.

HomeUnixWindowsOracleObsoleteHardwareDIYMechanicsScriptsConfigsPrivate

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
 

Last update: Mar 06, 2016