Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
09-06-2006, 03:22 PM #1
Difference between types of Joins
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);
$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);
09-06-2006, 04:10 PM #2
- Join Date
- Mar 2006
- St. Catharines, Ontario Canada
- Thanked 147 Times in 138 Posts
a left join and a right join are both outer joins. the word outer is optional. you can write them as:
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.
09-06-2006, 05:18 PM #3
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