To JOIN or Not to JOIN

[JSHINER]

Just wondering what the best query is (and maybe a quick explanation why:))

 

SELECT * FROM table1 JOIN table2 ON table1.user_id = table2.user_id WHERE table1.id = '$id'

 

or

 

SELECT table1.*, table2.* FROM table1, table2 WHERE table1.user_id = table2.user_id AND table1.id = '$id'

[craygo]

Pretty much will do the same thing. Only difference is the organization. When you use a join you don't have to put the 2 fields joining the tables in the where section, so you can actually see what you are filtering out a little more easy. Now when you have a one to many join and you have data in one table and no data to link to in another table and you want to see EVERYTHING in the first table you would have to use a LEFT JOIN. If you try to use the second query above you will not get any rows for the ones that have no matching data in the second table.

 

Ray

[JSHINER]

Thanks!

[cooldude832]

that is just a sloppy query IMHO because you are pulling "*" from two tables when odds are you don't need every field in both tables. 

 

As for join/not join I found out the other day that if Table2 lacks rows with valid user_id of Table1 then it will return an empty set when done without a join.

 

i.e

select a.a, b.b from `a`, `b` where a.id = b.id Group By a.id

if table b has no valid matches for the ID of table a it returns empty

select a.a, b.b from `a` LEFT JOIN `b` on (b.id = a.id) Group By a.id

Will return the rows of a even if table b is empty.