SQL primer: Why and how to use join statements?

Question

I have a MySQL database that looks like this:

users
    ( id , name )
groups
    ( id , name )
group_users
    ( id , group_id , user_id )

Now to look up all the groups that a user belongs to, I would do something like this:

select * from 'group_users' where 'user_id' = 47;

This will probably return something like:

( 1 , 3 , 47 ),
( 2 , 4 , 47 ),

But when I want to display this to the user, I'm going to want to display the name of the groups that they belong to instead of the group_id. In a loop, I could fetch each group with the group_id that was returned, but that seems like the wrong way to do it. Is this a case where a join statement should be used? Which type of join should I use and how would I use it?

Answer 1

That's a typical case for an inner join, such as:

select users.name, group.name from groups 
inner join group_users on groups.id = group_users.group_id
inner join users on group_users.user_id = users.id
where user_id = 47

Answer 2

SELECT gu.id, gu.group_id, g.name, gu.user_id
FROM group_users gu
INNER JOIN Group g 
 ON gu.group_id = g.group_id
     WHERE user_id = 47

Answer 3

I always find these charts to be very useful when doing joins:

http://www.codinghorror.com/blog/archives/000976.html

Answer 4

In general, you want to reduce the total number of queries to the database, by making each query do more. There are many reasons why this is a good thing, but the main one is that relational database management systems are specifically designed to be able to join tables quickly and are better at it than the equivalent code in some other language. Another reason is that it's usually more expensive to open many little queries than it is to run one large query that has everything you'll end up needing.

You want to take advantage of your RDBMS's strengths, so you should try to push data access into it in a few big queries rather than lots of little queries.

Now, that's just a general rule of thumb. There are cases when it's better to do some things outside of the database. It's important that you determine which is the right case for your situation by looking into bottlenecks if and only if they occur. Don't spend time worrying about performance until you find a performance problem.

But, in general, it's better to handle joins, lookups and all other query-related tasks in the database itself than it is to try to handle it in a general-purpose language.

That said, the kind of join you want is an inner join. You'd structure your join query like this:

SELECT groups.name, group_users.user_id
FROM group_users
INNER JOIN groups
  ON group_users.group_id = groups.group_id
WHERE groups.user_id = 47;