Outer joins
The joins we have seen so far are inner joins
. An inner join selects records from both tables only when they match. In some cases this leaves out records that we want to include.
A left join
includes all records from the left table - even if they do not have a matching record from the right table. There is also the right join
(what you'd expect).
The syntax for a right join is as follows:
All MSPs and parties, including parties with no MSPs, but not MSPs with no parties.
SELECT msp.name, party.name
FROM msp RIGHT JOIN party ON msp.party=party.code
Oracle use a rather more elegant syntax. To obtain a right outer join a (+) is used on the field on the left. You can think of this as being the table with an imaginary additional element. The Additional element has all NULL
values and so will match a NULL
in the other table.
All MSPs and parties, including parties with no MSPs, but not MSPs with no parties.
SELECT msp.name, party.name
FROM msp ,party
WHERE msp.party(+)=party.code