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