TalkPHP - JOINs?

TalkPHP (http://www.talkphp.com/forums.php)

- Absolute Beginners (http://www.talkphp.com/absolute-beginners/)

- - JOINs? (http://www.talkphp.com/absolute-beginners/1743-joins.html)

I don't have a problem just for future reference.

I see alot about left joins and stuff, and I think it's used for what I'm about to explain.

Say I have something like this for games:

id - title - description - categoryid
1 - Game - Description - 1

And the category table is like this:
id - category
1 - Games

I could do a query like
select category from categories where id = 'categoryid'

or whatever, but, is there anyway i can do this in one query, so i can select all of the games stuff, and the actual category name

yea a join would do it:

mysql Code:

SELECT games.title, catagory.name
FROM games
JOIN catagory ON ( games.id = catagory.games ) 
WHERE catagory.name =  'pogo'

or something to that effect

incidentally i'd like to post this great mysql tutorial resource, its got live examples to help you understand stuff.

JOIN tutorial:
http://sqlzoo.net/3b.htm

Just remember to join on indexes. Don't join on non-indexes as it will become painfully slow.

LEFT JOIN basically says if the data is in table 1, but might not be in table 2 - if it's not in table 2, add a null and still pull table 1's data.

RIGHT JOIN is the reverse of the aforementioned.

INNER JOIN is when you only want to pull data from table 1 and table 2 when there's a valid JOIN on an index.

Although I agree with Wildhoney (especially about creating an INDEX on most (if not all) columns you regularly perform joins on), I've always found the easiest way to look at the difference between a RIGHT and LEFT JOIN is by realizing that the LEFT join pulls data from the table referenced on the left side of the SQL statement, e.g.

Code:

SELECT

 t1.c1, t1.c2, t2.c1

FROM table1 AS t1 LEFT OUTER JOIN table2 AS t2

USING (c3)

Obviously table1 is on the left so all results will be pulled from table1 whereas NULL will represent columns in missing records from table2 (on the right). The RIGHT OUTER JOIN (not used as often, in my experience) would represent all records from table2 (the right hand table) with NULL representing missing values from table1.

Note that both LEFT and RIGHT join statements are implied OUTER joins, but you can specifically use the OUTER keyword to avoid confusion.

Additionally, you can think of the difference between the INNER and OUTER joins as being the result of the join itself; the INNER join returns values only from inside the set of matched records. In other words, only records will be returned where there is a match on the conditions. The OUTER join also returns records 'outside' the parameters of the match, based on the table on the left or the right. Get it?