[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