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:
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.