SQL: JOIN

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Orkan
Forum Commoner
Posts: 32
Joined: Sun Aug 24, 2003 9:07 am
Location: Ukraine
Contact:

SQL: JOIN

Post by Orkan »

Hi...

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

I always use INNER...

Thx...
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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.
User avatar
Orkan
Forum Commoner
Posts: 32
Joined: Sun Aug 24, 2003 9:07 am
Location: Ukraine
Contact:

Post 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"???
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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.
User avatar
Orkan
Forum Commoner
Posts: 32
Joined: Sun Aug 24, 2003 9:07 am
Location: Ukraine
Contact:

problems...

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
User avatar
Orkan
Forum Commoner
Posts: 32
Joined: Sun Aug 24, 2003 9:07 am
Location: Ukraine
Contact:

Post 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!
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Ahh... Well, personally I never used the natural either so I'm stumped there.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

And I only use PostGreSQL, perhaps MySQL doesn't have NATURAL JOIN support. INNER JOIN ... USING (..) is a fine subsitute.
Post Reply