Jump to content

Archived

This topic is now archived and is closed to further replies.

SharkBait

SQL Joins

Recommended Posts

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?



Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[!--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?

Share this post


Link to post
Share on other sites
[!--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.

Share this post


Link to post
Share on other sites
Also, queries using the "FROM A JOIN B ON ..." execute faster than those using the "FROM A, B WHERE .." syntax

Share this post


Link to post
Share on other sites
[!--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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Well, no arguments here -- I switched to JOIN-only syntax a few years ago, and I've never looked back.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.