I know this is a rookie question but in mysql what is the difference between inner join and using the where claus?

Question

Ok take Exhibit A:

Select * from users as user, userprofile as profile where user.user_id = profile.user_id;

Now take exhibit B:

Select * from users as user inner join userprofile as profile on user.user_id = profile.user_id;

The two returns exactly the same results. So my question is why do people prefer Exhibit B over Exhibit A? is there really a difference between using the two?

Ratchetr · Accepted Answer

I am also an experienced programmer. I work with Oracle much more than MySQL, but I think my comments will apply to both.

I most definitely prefer exhibit B over exhibit A. Hands down.
But first let's refactor exhibit B to make it more readable. In both Oracle and MySQL, inner join and join are synonymous, so we can drop the INNER. In both Oracle and MySQL, when the PK and FK have the same column name, you can use USING rather than ON. I also only use aliases when needed. I don't see a need to alias users as user and userprofle as profile here. It just pollutes the namespace.

So my version of Exhibit B would be:
SELECT * -- Of course, you never ever use * in production code
FROM users
JOIN userprofile USING(user_id);

That is a simpler start, NO?

But that is just a readability tweak, it doesn't answer your question.
Why use a JOIN when you can do that in the WHERE clause?

Because there is almost always other crap in the WHERE clause. Business logic rules. If you use Exhibit A, then your table joining logic and your business logic all get mushed together in the WHERE clause. Joining is about your data model. Business logic is about your business. Keep them apart!!!

In production, it is more likely to end up looking like this:
SELECT *
FROM users
JOIN userprofile USING(user_id)
WHERE is_user_active = 1 
  AND (p_SHOW_BANNED USERS = 1 OR is_user_banned = 0)
  AND PHASE_OF_MOON(sysdate) <> userprofile.phase_of_moon
  AND Other Yucky Stuff;

Use JOIN to connect tables.
Use WHERE for business rules.

Easier on the eyes.

Trending News

I know this is a rookie question but in mysql what is the difference between inner join and using the where claus?

1 Answer