Actually, the left outer join is sufficient to execute all the outer
join operators:
- right outer join: just swap the "from" arguments
- full outer joins: union of left and right outer joins
Examples (classical "supplier-part-supply" example):
create table S(SN,NAME);
create table P(PN,COLOR);
create table SP(SN,PN,Q);
insert into S values ('S1','SMITH'),('S2','JONES'),('S3','BLAKE');
insert into P values ('P1','red'),('P2','green'),('P3','blue');
insert into SP values
('S1','P1',30),('S2','P1',30),('S2','P2',40),('S3','P4',10);
- Left outer join S --> SP --> P:
------------------------------
select S.SN,S.NAME,SP.PN,P.COLOR,SP.Q
from S left join SP using (SN)
left join P using (PN);
+----+-------+----+-------+----+
| SN | NAME | PN | COLOR | Q |
+----+-------+----+-------+----+
| S1 | SMITH | P1 | red | 30 |
| S2 | JONES | P1 | red | 30 |
| S2 | JONES | P2 | green | 40 |
| S3 | BLAKE | P4 | -- | 10 |
+----+-------+----+-------+----+
- Right outer join P --> SP --> S:
------------------------------
select S.SN,S.NAME,P.PN,P.COLOR,SP.Q
from P left join SP using (PN)
left join S using (SN);
+----+-------+----+-------+----+
| SN | NAME | PN | COLOR | Q |
+----+-------+----+-------+----+
| S1 | SMITH | P1 | red | 30 |
| S2 | JONES | P1 | red | 30 |
| S2 | JONES | P2 | green | 40 |
| -- | -- | P3 | blue | -- |
+----+-------+----+-------+----+
- Full outer join P <--> SP <--> S:
-------------------------------
select S.SN,S.NAME,SP.PN,P.COLOR,SP.Q
from S left join SP using (SN)
left join P using (PN)
union
select S.SN,S.NAME,P.PN,P.COLOR,SP.Q
from P left join SP using (PN)
left join S using (SN);
- Full outer join with a "union all" (may be faster but may include
duplicates):
---------------------------------
select S.SN,S.NAME,SP.PN,P.COLOR,SP.Q
from S left join SP using (SN)
left join P using (PN)
union all
select S.SN,S.NAME,P.PN,P.COLOR,SP.Q
from P left join SP using (PN)
left join S using (SN)
where Q is null;
+----+-------+----+-------+----+
| SN | NAME | PN | COLOR | Q |
+----+-------+----+-------+----+
| -- | -- | P3 | blue | -- |
| S1 | SMITH | P1 | red | 30 |
| S2 | JONES | P1 | red | 30 |
| S2 | JONES | P2 | green | 40 |
| S3 | BLAKE | P4 | -- | 10 |
+----+-------+----+-------+----+
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
