SQL Joins

[SharkBait]

I've never used a join in the sql query.

I am thinking that I really need to start doing it ;)

If I have two tables and wish to tie them together I would use a join correct? Currently I do the following:

[code]

SELECT * FROM table1, table2 WHERE table1.id = 3 AND table1.id = table2.id

[/code]

Would I be able to use a JOIN with that? I guess what I am really trying to do is select values from 3 seperate tables that all have some sort of correleation between them. IE an id field

Am I understanding how JOINs work? It mushes multple tables together into sort of a 'pseudo' table?

[fenway]

If you're just starting to work with JOINs, I suggest that you NEVER use the comma operator -- trust me. I can give you a laundry list of reasons why it's bad.

Nonetheless, you're correct -- JOINs will accomplish the task you have outlined:

[code]SELECT * FROM table1
JOIN table2 ON ( table1.id = table2.id )
WHERE table1.id = 3[/code]

Not only does this make it more explicit, but it makes it much easier to change at a later date.

[SharkBait]

[!--quoteo(post=380687:date=Jun 6 2006, 10:34 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jun 6 2006, 10:34 AM) [snapback]380687[/snapback][/div][div class=\'quotemain\'][!--quotec--]
If you're just starting to work with JOINs, I suggest that you NEVER use the comma operator -- trust me. I can give you a laundry list of reasons why it's bad.

Nonetheless, you're correct -- JOINs will accomplish the task you have outlined:

[code]SELECT * FROM table1
JOIN table2 ON ( table1.id = table2.id )
WHERE table1.id = 3[/code]

Not only does this make it more explicit, but it makes it much easier to change at a later date.
[/quote]

So when you mean I should not use the comma operator, I shouldn't do SELECT * FROM table1,table2.table3

So I should always use JOINs?

[fenway]

[!--quoteo(post=381848:date=Jun 9 2006, 10:25 AM:name=SharkBait)--][div class=\'quotetop\']QUOTE(SharkBait @ Jun 9 2006, 10:25 AM) [snapback]381848[/snapback][/div][div class=\'quotemain\'][!--quotec--]
So when you mean I should not use the comma operator, I shouldn't do SELECT * FROM table1,table2.table3

So I should always use JOINs?
[/quote]
That's right -- commas are short-hand. Not only do they force you to dump the join conditions in the where clause, which makes for many mistakes and clutter, but as of MySQL 5, they have different precedence that JOIN, which is yet another nightmare. Stick with JOINs.

[Barand]

Also, queries using the "FROM A JOIN B ON ..." execute faster than those using the "FROM A, B WHERE .." syntax

[fenway]

[!--quoteo(post=382188:date=Jun 10 2006, 05:43 AM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 10 2006, 05:43 AM) [snapback]382188[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Also, queries using the "FROM A JOIN B ON ..." execute faster than those using the "FROM A, B WHERE .." syntax
[/quote]
Really? I mean, I would tend to agree with you, but doesn't the optimizer do a fairly good job? That is, the EXPLAINs are often similar.

[Barand]

I know I read it somewhere but I cannot find the reference. Only noticeable with large queries but I have managed to improve performance by changing to the JOIN .. ON syntax.

[fenway]

Well, no arguments here -- I switched to JOIN-only syntax a few years ago, and I've never looked back.