SQL: JOIN
Moderator: General Moderators
SQL: JOIN
Hi...
Could anyone explain plz what difference between RIGHT / LEFT / FULL / CROSS / INNER JOINs?
I always use INNER...
Thx...
Could anyone explain plz what difference between RIGHT / LEFT / FULL / CROSS / INNER JOINs?
I always use INNER...
Thx...
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.
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.
Yes. And there will only be one column in the result set named a_id.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"???
problems...
Code: Select all
SELECT * FROM `country` natural join `region`;Code: Select all
country_id - name
1 USA
2 GermanyCode: Select all
region_id - country_id - name
1 1 Alabama
2 1 TexasWhat's wrong?
I have mysql 3.23.57
Code: Select all
SELECT *
FROM country
INNER JOIN region
USING ( country_id )A tip is to use for example http://www.phpmyadmin.net/ or other sql managers. Makes it easier to experiment with your code.