CODEWITHSUNDEEP
mysqljoin
cgweb87
cgweb87

Reputation: 461

Is there a better way to do this mysql join?

Here is the query,

$q = $dbc -> prepare("SELECT social.withID, accounts.username 
      FROM social, accounts 
      WHERE social.withID = accounts.id AND social.id = ? AND friendAccept = 1");
$q -> execute(array($user['id']));

It basically pulls the username out of the accounts table and adds it to the friends table where they are accepted friends. I am new to joins and don't know wether I should use this LEFT (i think) join or another?

Upvotes: 0

Views: 52

Answers (3)

derobert
derobert

Reputation: 51147

You're currently doing an inner join. Your query is equivalent to writing:

SELECT social.withID, accounts.username 
  FROM social INNER JOIN accounts ON (social.withID = accounts.id)
  WHERE social.id = ? AND friendAccept = 1

The difference between your query and what you'd get if you switched to a left outer join is what happens if the record doesn't exist in the right-hand table. With an inner join, if the record doesn't exist in accounts (according to the ON condition, e.g., can't find an account with that id), then no row will be returned. With a left outer join, nulls will be returned instead.

I don't know your schema, but I suspect a connection (withID) can only go to an account that exists, so the row in accounts will always exist. But let's consider a related query:

SELECT a.account_id, COUNT(s.withID) AS friend_count
   FROM accounts a INNER JOIN social s ON (a.account_id = s.withID)
   GROUP BY a.account_id

If you leave that as inner join, then people without any friends will not show in the output (because there will be no match for the ON condition). If you change it to left join, then they'll show up in the output as having zero friends. [This query may be ignoring accepted status, etc.… once again, I don't know your schema]

Upvotes: 2

Icarus
Icarus

Reputation: 63956

Yes, explicitly show the join:

SELECT social.withID, accounts.username 
      FROM social inner join accounts on social.withID = accounts.id 
      WHERE social.id = ? AND friendAccept = 1

It won't hide the intent of your query, it will be more obvious to the developer what your intention is as opposed to having where a.id=b.id

Upvotes: 0

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385144

Looks ok to me.

Your comma usage is equivalent to an INNER JOIN, BTW.

Upvotes: 0

Related Questions