NYCPHP Meetup

[John Campbell]

On Tue, Sep 1, 2009 at 10:55 AM, tedd<tedd at sperling.com> wrote:
>
> I fully understand WHERE, it's understanding how JOIN's simplify things.

1. Joins make your sql more readable (and are a substatement to the
FROM clause, and should be indented)

2.  When you use the WHERE clause, it is easy to accidentally do a
cartesian join, and select bazillions of rows during development.

3. You can change to a LEFT join as needed.  Since you can't do a left
join in the where clause, you might as well use the JOIN syntax
everywhere.

Consider
SELECT user.name, COUNT(post.post_id) as post_count
FROM user
  LEFT JOIN post USING user_id
GROUP BY 1

vs

SELECT user.name, COUNT(post.post_id) as post_count
FROM user
  INNER JOIN post USING user_id
GROUP BY 1

The second one can be rewritten using a WHERE clause, but the first one can't.

People who don't understand the first query, end up writing stuff like:

SELECT user.name, (SELECT COUNT(*) from post WHERE
post.user_id=user.user_id) as post_count
FROM user

-john campbell