Inner and Outer Joins
A join between two tables does not include any rows from either table that have no matching rows in the other. This is called an inner join and frequently causes confusion since fewer rows are returned than the user expects. For example, tables english and spanish look like this:
* select * from english; * select * from spanish; ----------------------------- ----------------------------- |tag |name | |tag |name | ----------------------------- ----------------------------- |1 |one | |2 |dos | |2 |two | |3 |tres | |3 |three | |4 |cuatro | ----------------------------- ----------------------------- 3 rows selected 3 rows selected
When you join these two tables, you get only the two rows that have the same tag:
* select e.name, e.tag, s.name from english e, spanish s where e.tag = s.tag; ------------------------------------------- |name |tag |name | ------------------------------------------- |two |2 |dos | |three |3 |tres | ------------------------------------------- 2 rows selected
Row one in table english and row cuatro in table spanish fall into the outer joins:
Joins +--------------+ left outer ---> | one 1 | | +--------------+ +--> | two | 2 : dos | inner join | | | : | +--> | three | 3 : tres | +--------|- - -+ | | 4 cuatro| <--- right outer +--------------+
You can select outer join rows by using not exists. This query fetches the row in english that is not in spanish (the left outer join):
* select e.name as English, e.tag, '--no row --' as Spanish from english e where not exists (select * from spanish s where e.tag=s.tag); ------------------------------------------- |English |tag |Spanish | ------------------------------------------- |one |1 |--no row -- | ------------------------------------------- one row selected
This query fetches the row in spanish that is not in english (the right outer join):
* select '--no entry--' as English, s.tag, s.name as Spanish from spanish s where not exists (select * from english e where e.tag=
