Jump to content


Photo

SQL Joins


  • Please log in to reply
7 replies to this topic

#1 SharkBait

SharkBait
  • Members
  • PipPipPip
  • Advanced Member
  • 845 posts
  • LocationMetro Vancouver, BC

Posted 06 June 2006 - 05:10 PM

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:


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


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?





#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 June 2006 - 05:34 PM

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:

SELECT * FROM table1 
JOIN table2 ON ( table1.id = table2.id )
WHERE table1.id = 3

Not only does this make it more explicit, but it makes it much easier to change at a later date.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 SharkBait

SharkBait
  • Members
  • PipPipPip
  • Advanced Member
  • 845 posts
  • LocationMetro Vancouver, BC

Posted 09 June 2006 - 02:25 PM

[!--quoteo(post=380687:date=Jun 6 2006, 10:34 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jun 6 2006, 10:34 AM) View Post[/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:

SELECT * FROM table1 
JOIN table2 ON ( table1.id = table2.id )
WHERE table1.id = 3

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?

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 June 2006 - 04:57 PM

[!--quoteo(post=381848:date=Jun 9 2006, 10:25 AM:name=SharkBait)--][div class=\'quotetop\']QUOTE(SharkBait @ Jun 9 2006, 10:25 AM) View Post[/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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 10 June 2006 - 09:43 AM

Also, queries using the "FROM A JOIN B ON ..." execute faster than those using the "FROM A, B WHERE .." syntax
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 June 2006 - 01:59 PM

[!--quoteo(post=382188:date=Jun 10 2006, 05:43 AM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 10 2006, 05:43 AM) View Post[/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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 10 June 2006 - 05:56 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 June 2006 - 08:14 PM

Well, no arguments here -- I switched to JOIN-only syntax a few years ago, and I've never looked back.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users