Welcome to Part 3 of my series on SQL. If you need to catch up, try Part 1 or Part 2. I’ll make this installment short because of one or more of the following possible reasons:
- You don’t care about SQL.
- You don’t like baseball, which I use in my examples.
- You think I suck at writing and/or life.
- You’re a Grumpy Gus and you hate your job and your girlfriend and your life.
Onward, shall we?
Joins
There are a few types of joins in SQL: inner, left, and right. They each have their specific uses, so it’s important to know which one to use for a situation.
Inner Joins
I use inner joins most often at work and for personal use. When you use an inner join, you’re saying “give me all the rows from these two tables that contain a match on this field that I chose.”
I’ll go back to the Baseball Databank for examples. So far, I’ve been getting batters’ stats using his playerID. While someone can go look up a playerID in the database, it is not as useful when scanning results. In this example, I’ll get Babe Ruth’s career home run totals AND display his name in the same result set.
mysql> select m.nameFirst, m.nameLast, sum(b.HR) as HR -> from Master m, Batting b -> where m.playerID = b.playerID -> and b.playerID = 'ruthba01' -> group by b.playerID; +-----------+----------+------+ | nameFirst | nameLast | HR | +-----------+----------+------+ | Babe | Ruth | 714 | +-----------+----------+------+ 1 row in set (0.00 sec)
Notice that now the FROM clause has two different tables in it, Master and Batting. In the WHERE clause, I specified the criteria for the join: m.playerID = b.playerID. I’m also use aliases for the tables because you have to specify which table a field comes from. Otherwise, you get an ambiguity error.
That example is nice if you know the playerID of the batter you’re looking for. What if you don’t? What if you want a group of players that are related? I’ll use another example from a previous post to show this. I will show the top ten career home run totals of all time, along with the batters’ names.
mysql> select m.nameFirst, m.nameLast, sum(HR) as HR -> from Master m, Batting b -> where m.playerID = b.playerID -> group by b.playerID -> order by sum(HR) desc -> limit 10; +-----------+-----------+------+ | nameFirst | nameLast | HR | +-----------+-----------+------+ | Barry | Bonds | 762 | | Hank | Aaron | 755 | | Babe | Ruth | 714 | | Willie | Mays | 660 | | Sammy | Sosa | 609 | | Ken | Griffey | 593 | | Frank | Robinson | 586 | | Mark | McGwire | 583 | | Harmon | Killebrew | 573 | | Rafael | Palmeiro | 569 | +-----------+-----------+------+ 10 rows in set (3.12 sec)
Note how you can do all the group by’s, order by’s, and limits that you can use in a non-join query. I could have use a simpler example, but at this point in the series, you can handle it, right?
Alternate syntax
The previous example can also be coded using an alternate syntax, which uses an explicit INNER JOIN clause.
mysql> select m.nameFirst, m.nameLast, sum(HR) as HR
-> from Master m inner join Batting b
-> on m.playerID = b.playerID
-> group by b.playerID
-> order by sum(HR) desc
-> limit 10;
+-----------+-----------+------+
| nameFirst | nameLast | HR |
+-----------+-----------+------+
| Barry | Bonds | 762 |
| Hank | Aaron | 755 |
| Babe | Ruth | 714 |
| Willie | Mays | 660 |
| Sammy | Sosa | 609 |
| Ken | Griffey | 593 |
| Frank | Robinson | 586 |
| Mark | McGwire | 583 |
| Harmon | Killebrew | 573 |
| Rafael | Palmeiro | 569 |
+-----------+-----------+------+
10 rows in set (2.45 sec)
You just have to specify the two tables on either side of the INNER JOIN and then specify the join criteria after the ON. It really doesn’t matter which version you use. It comes down to personal preference (or your asshole boss telling you which one to use).
Next time
Since I promised to keep it short, I’ll stop here. Next time I’ll cover left and right joins. After that, it’s on to updates and inserts!
Two out of four. But I read it anyway. You can probably guess which two.
This post certainly cleared up a couple questions I had about joins.
But I think the question on everyone’s mind is “how do you do this in Access??”
Also, that’s the second “Your mom” joke involving databases I’ve seen today.
Good post.
My response to everyone’s question is: Fuck Access.
Also: I Accessed your mom last night.