How to Make a Join Easier to Read using Table Aliases

I recently put together a lesson on  table aliases and multi-table joins.  It is part of my Join Together Now course.  Click here get the course at a discount.

You’ll find that as you write more complicated joins, that the SQL can become harder to read, as you’ll need to qualify column with table names to avoid ambiguity.  To make you SQL more compact and more readable you can create table aliases.

The aliases become a short-hand naming convention.  That way, instead of referring to Person.PersonPhone.BusinessEntityID we can create the alias PP for Person.PersonPhone and simply refer to the column as PP.BusinessEntityID.

You can learn more about this this video, it’s transcript follows:

If you want to learn more about joins, why not take my course Join Together Now?  Use coupon code JTN-COURSES-V1 to get it at a significant discount.

How to Make Join easier to Read using Table Aliases Transcript

In this lesson we’re going to talk about table aliases and multi-table joins.

So, why do we use table aliases? Well, as joins get more complicated, it makes sense to use table aliases.  Remember, table aliases are a way of renaming the table name giving it a shorter name.  And it makes the SQL easier to read.

Why Use Table Aliases

It also, since it’s a shorter name, you’re less apt to make a typing mistake, and when you’re working with multiple tables, especially those that have, maybe the same columns within them, it helps avoid ambiguous column names.  Meaning, are you picking the business entity ID from the employee table or from the person table? By being able to use an alias and always prefixing the column name with that alias, you know exactly what table you’re grabbing the column from.

So let’s look at some examples.  So here’s a table alias example.

Table Alias – Before and After

In the top you can see an INNER JOIN that we’ve worked on in the past, where we specified the join using only table names.  We’re selecting the FirstName from the Person table, the LastName from the Person table, the PhoneNumber from the PersonPhone table.  The join condition is on the Person and the PersonPhone table.  And we’re matching by BusinessEntityID.

SELECT Person.FirstName,
       Person.LastName,
       PersonPhone.PhoneNumber
FROM   Person.Person
       INNER JOIN Person.PersonPhone
       ON Person.BusinessEntityID = PersonPhone.BusinessEntityID

There’s a lot here.  It can be simplified by using table aliases.  It kind of tightens up the SQL.  And how we do that is by putting the alias at the end of the table name.

Notice how the each column is prefix by some letters.  Those are the table aliases.  The first alias is named in the FROM clause, the second in the INNER JOIN.

SELECT P.FirstName,
       P.LastName,
       PP.PhoneNumber
FROM   Person.Person P
       INNER JOIN Person.PersonPhone PP
       ON P.BusinessEntityID = PP.BusinessEntityID

Well if we go down into our INNER JOIN, we’ll see that we are doing an inner join on Person.PersonPhone.  And we alias with PP.  Now, when we reference any column from PersonPhone, we can just use PP as the alias.  So we have, for our for our join condition then, a join condition on table alias P.BusinessEntityID = PP.BusinessEntityID.

I personally think this makes it easier to read.  It’s easy to type.  I’m less apt to make mistakes.  I will caution you though, that if you start using table aliases that are a little arcane, and you have lots of tables in your joins, after a while, it can be easy to forget which letters go with which tables.  So there is a trade-off there, in terms of readability.  And you want to be aware of that.

Joining Multiple Tables Using Table Aliases

Now let’s move on to joining three tables.  And this is really where you start wanting to use table aliases because the more tables you have in your SQL, the bigger your SQL statements get, and the easier I think it is to manage once you can alias.

We’re going to create a query where we’re pulling columns from each of these tables.  Now we already know how to do a join with two tables.  In fact, through some of our examples, we’ve already done a join where we’ve joined person with person phone.  And then we’ve also done an example where we joined person phone with phone number type.

The Queries Two Tables at a Time

And you can see that I’ve put these queries down here below.  Once query obtain the employee name and their phone number, the other the phone number and type.

So the big question is how can I write a join that pulls all this together? And the simple answer is that we can have more than one inner join clause in our SQL.  I’d like to show you how to do that right now.

I’m going to start out and I’m going to select from Person, INNER JOIN on PersonPhone.  And then we’ll do our join clause.  So this should look very familiar.

SELECT P.FirstName,
       P.LastName,
       PP.PhoneNumber
FROM   Person.Person P
       INNER JOIN Person.PersonPhone PP
       ON P.BusinessEntityID = PP.BusinessEntityID

I’m going to indent this out a little bit.  I know this is going to come out to here.  This inner join’s going to …  I’m going to move this out.  And we’ll move this out.

So, so far what we’re doing is we’re going to join the Person table to the PersonPhone table using the BusinessEntityID column.  Now, what we’re going to do is go and join from PersonPhone to PhoneNumberType.  And to do that I just add another inner join clause.  And I’ll alias that as PT on.  And then I can say PersonPhone.PhoneNumberTypeID equals PT.PhoneNumberTypeID.

SELECT P.FirstName,
       P.LastName,
       PP.PhoneNumber,
       PT.Name
FROM   Person.Person P
       INNER JOIN <span style="color: #ff0000;">Person.PersonPhone PP</span>
       ON P.BusinessEntityID = PP.BusinessEntityID
       <span style="color: #ff0000;">INNER JOIN Person.PhoneNumberType PT</span>
<span style="color: #ff0000;">       ON PP.PhoneNumberTypeID = PT.PhoneNumberTypeID</span>

So, as you can imagine, what we’re doing is we’re linking a person to their phone number using their unique ID, which is the BusinessEntityID.  And then when we’re on the PhoneNumber table, to know what type of phone number it is, we’re going to link to the PhoneNumberType and then we’ll get the name.

Let’s run the query.  So now we’re bringing in the first name and last name from the person table.  The phone number from the phone number table.  And then the type from the phone number type table.

so here’s the first table, person.  We’re joining to person phone.  That’s where we get the phone number.  We’re linking on business entity ID, because we’re using the person to link to what their phone number is.  And then we’re doing a second join.  We’re going to link in phone number type.  And the main reason we’re doing this is we need to understand what type of phone number we have on the person phone record.  We know there’s a phone number type ID.  So we’re going to use that as our match to look up and bring in the phone number type record.  And the reason we’re doing that is so that we can get the phone type name, and display that as the phone number type.

So now that you’ve seen this as a query, I’m going to go back to our slide and show you how this is all related.  And we’ll walk through it again on the slide.

The three tables that we joined together, along with the SQL that we used to do so.  So our objective was to display the person, their first and last name, their phone number, and then the type of phone they have listed.  To do this, we used the person’s business entity ID, which is their unique identifier, to link to the person phone record.  At this point we may bring back one or more phone record for that person.

Now we need to understand what type of phone we are talking about within the listing.  To understand that we used the phone number type ID to link to the phone number type table to retrieve the name.  This is shown in the SQL where we’re selecting the first name and last name from person, the person phone phone number, and the person phone number type.  These are all aliased.

Joining Three Tables – Final Query

You’ll see that there is the main table, person, alias with a P.  And then I have two join clauses.  One to person phone, and another join clause to person phone type.  Also notice that these clauses are the join condition where we are, in one case, joining on business entity ID.  And in the second we are joining on phone number type ID.

so as you can see, joining between two tables and three tables is not that much different.  So I hope you enjoyed this lesson.  And I’ll see you in the next.

The post How to Make a Join Easier to Read using Table Aliases appeared first on Essential SQL.