...

View Full Version : Difference between types of Joins



musher
09-06-2006, 03:22 PM
Totaly confused!
I have two tables menu & page_content (the relationship is many page_content records to one menu record).

I set up a query to list all the records, worked fine on my local pc but did not when I loaded it to the server, after doing some reading I changed it to a "RIGHT JOIN" and it worked. Now I've done some reading and googleing and this stuff is still not making sense, My question is what is the difference between???
1. LEFT JOIN
2. RIGHT JOIN
3. INNER JOIN
4. OUTTER JOIN
and which one should I use???

Query that didnt work on server (host)

// $result = mysql_query("SELECT a.ID, a.menu_id, a.submenu_title, a.page_title, a.display_order as page_order, b.menu_title, b.display_order as menu_order FROM page_content as a JOIN menu as b ON (a.menu_id=b.ID) ORDER BY b.display_order,a.display_order",$db);
Query that did work on server (host)

$result = mysql_query("SELECT a.ID, a.menu_title, a.display_order AS menu_order, b.ID AS pageID, b.menu_id AS itis, b.submenu_title, b.page_title, b.display_order AS page_order, b.access_lvl FROM menu AS a RIGHT JOIN page_content AS b ON a.ID = b.menu_id ORDER BY menu_order, page_order",$db); The two query's are a tad bit different, was trying to follow the MySQL manual when I did the final test, but query 2 did work set up exactly like query 1 except that it had a right join in it.

guelphdad
09-06-2006, 04:10 PM
a left join and a right join are both outer joins. the word outer is optional. you can write them as:

LEFT JOIN
or
LEFT OUTER JOIN

the difference between an inner join and an outer join is that an inner join will only return records from both tables that match the field you are joining on.

an outer join will also return unmatched rows.

if you have a table of cars and car sales and want to show all cars that have been sold you would use an inner join on the two tables.

if you wanted to list all cars, irrespective of whether or not they have been sold, you would use an outer join.

you can use left or right joins, but you should stick with one or the other and not mix them up. If you read from left to right then you should use LEFT joins and then you will remember that the first table you name is "on the left" of the second table.

musher
09-06-2006, 05:18 PM
guelphdad,
Thanks "BIG TIME" makes total sense now, great example.

PS if your ever in southern MN swing on over I have a cold beer in the fridge for ya



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum