[thelist] new & improved SQL join syntax?

Judah McAuley judah at wiredotter.com
Fri Jan 19 15:45:21 CST 2007


Brian Cummiskey wrote:
> INNER joins everything from both tables on the match on both sides, and 
> often produces multiple rows per record (depending on table data of 
> course, often MANY TO MANY)
> LEFT (OUTER) JOIN joins only the source row to a matching joined table 
> row.  I put outer in parenthesis, as this is also best practice, even 
> though LEFT JOIN is an implied outer join. (often, 1 TO MANY or 1 TO 1)
> RIGHT is the same thing as left, only backwards.  There's really no use 
> for it...  just saves you from switching the order of your ON clause, 
> and IMO, should be avoided where possible.

This isn't quite right, actually. Or perhaps I just am misreading what 
you say, but here's the difference:

Take a two tables, person and house. A person can have multiple houses.

person
______
id
name

house
______
id
person_id
name

And data like so:

person
_______

1	anne
2	bob
3	charlene

house
_______
1	1	primary home
2	1	vacation home
3	3	primary home

An INNER JOIN only returns rows that have a column match in both tables. 
So if you say

SELECT person.name
      , house.name AS home_name
FROM person INNER JOIN house ON person.id = house.person_id

you will only return the rows that have a corresponding matches in both 
tables (the ON person.id = house.person_id).

Since poor Bob is homeless, he doesn't have a record in the house table, 
so that query will return:

anne	primary home
anne	vacation home
charlene	primary home

A LEFT OUTER JOIN returns the rows from person even if there are no 
matching tables in house


SELECT person.name
      , house.name AS home_name
FROM person LEFT OUTER JOIN house ON person.id = house.person_id

This would return:

anne	primary home
anne	vacation home
bob	NULL
charlene	primary home

LEFT JOIN is alternate syntax for LEFT OUTER JOIN and JOIN is alternate 
syntax for INNER JOIN. I prefer the explicit terminology as it matches 
up with ANSI SQL-92 syntax.

Some databases also support FULL OUTER JOIN or CROSS JOIN which will 
return all records from both tables even if there is not a matching 
record in the other table. This is the Cartesian Product that Matt 
referred to in his email.

Hope this clarifies things.

Judah






More information about the thelist mailing list