Page 1 of 1

SQL: JOIN

Posted: Wed Aug 27, 2003 1:24 am
by Orkan
Hi...

Could anyone explain plz what difference between RIGHT / LEFT / FULL / CROSS / INNER JOINs?

I always use INNER...

Thx...

Posted: Wed Aug 27, 2003 10:36 am
by nielsene
CROSS JOIN -- generates the cartesian procduct of the two tables. Ie if there are M rows in table A, and N rows in table B, the resulting table with have MxN rows -- each row from A is paired with each row from B, without any restriction. This is what happens if you do SELECT * FROM A, B;

INNER JOIN -- a CROSS JOIN with a built-in WHERE clause (kinda), for instance SELECT * FROM a JOIN b ON (a.foo=b.bar) or SELECT * FROM a JOIN B USING (foo); will generate result set that has been "filtered" from the full cartesian join. The first query could have been written SELECT * FROM a,b WHERE a.foo=b.bar; I like to use JOIN's with ON/USING to build up my result set. I use WHERE to remove "valid" rows that I don't want.

NATURAL JOIN is basically SELECT * FROM a JOIN b USING(<all shared column names>); and is writen as SELECT * FROM a NATURAL JOIN b; If you've designed your database nicely, many of your JOINs will be NATURAL, which makes the queries simpler to understand.

RIGHT/LEFT/FULL are all types of OUTER JOINs. An OUTER JOIN will always return at least one row for every row in one (or both, in the case of FULL) tables, even if there isn't a matching row, substituting NULLs for the missing row-match.

Posted: Wed Aug 27, 2003 10:45 am
by Orkan
O! Thanx!
bout natural join... if I have:
table1: a_id, name, text...
table2: b_id, a_id, name...
If I write
"Select (wateva) from table1 natural join table2"

it would be equal to
"Select (wateva) from table1 inner join table2 on table1.a_id=table2.a_id"???

Posted: Wed Aug 27, 2003 7:24 pm
by nielsene
Orkan wrote:O! Thanx!
bout natural join... if I have:
table1: a_id, name, text...
table2: b_id, a_id, name...
If I write
"Select (wateva) from table1 natural join table2"

it would be equal to
"Select (wateva) from table1 inner join table2 on table1.a_id=table2.a_id"???
Yes. And there will only be one column in the result set named a_id.

problems...

Posted: Thu Aug 28, 2003 5:58 am
by Orkan

Code: Select all

SELECT * FROM `country` natural join `region`;
Country:

Code: Select all

country_id - name
1            USA
2            Germany
Region

Code: Select all

region_id - country_id - name
1               1        Alabama
2               1        Texas
I have nothing in result :(
What's wrong?
I have mysql 3.23.57

Posted: Thu Aug 28, 2003 9:10 am
by JAM

Code: Select all

SELECT * 
FROM country
INNER JOIN region
USING ( country_id )
...worked for me. not a natural join tho.

A tip is to use for example http://www.phpmyadmin.net/ or other sql managers. Makes it easier to experiment with your code.

Posted: Thu Aug 28, 2003 9:24 am
by Orkan
I use phpMyAdmin...
Could it be old MySQL not allowing me to use NATURAL JOIN?

I always use "INNER JOIN table on ..." :)
I just wanted to know more bout other ways to join :)

Thanx for help!

Posted: Thu Aug 28, 2003 9:26 am
by JAM
Ahh... Well, personally I never used the natural either so I'm stumped there.

Posted: Thu Aug 28, 2003 10:23 am
by nielsene
And I only use PostGreSQL, perhaps MySQL doesn't have NATURAL JOIN support. INNER JOIN ... USING (..) is a fine subsitute.