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

Reply via email to