Get to know the data
JOIN revisited
LEFT JOIN
RIGHT JOIN
FULL JOIN
OUTER JOINS
Aliases for tables
17. Table aliases

Instruction

Speaking of keyboard strokes, there is one thing that may come in handy and help you write less: a table alias.

Imagine the following situation: we want to select many columns from two joined tables. These tables may share some column names, so best practices tell us to precede the column names with the table name and a dot. You could, of course, write it like this:

SELECT
  Person.ID,
  Person.Name,
  Person.Year,
  Car.ID,
  Car.Name,
  Car.Year
FROM Person
INNER JOIN Car
  ON Person.ID = Car.OwnerID;

But that takes a lot of writing, doesn't it? Fortunately, there is a way to make things simpler: we can introduce new temporary names (aliases) for our tables:

SELECT
  P.ID,
  P.Name,
  P.Year,
  C.ID,
  C.Name,
  C.Year
FROM Person AS P
INNER JOIN Car AS C
  ON P.ID = C.OwnerID;

As you can see, we introduced the AS keyword after the table names in the FROM clause. Just as with column aliases, AS indicates that whatever came before it should be given the new, temporary name (alias) that follows. With AS, we can save our fingers a lot of typing and write shorter names for our tables.

Exercise

Use INNER JOIN on the Room and Equipment tables so that all pieces of equipment are shown together with their rooms. Use the table aliases R and E, respectively. Select the ID and Name columns from the Equipment table and the RoomNumber and Beds columns from the Room table.

Stuck? Here's a hint!

Type:

SELECT
  E.ID,
  E.Name,
  R.RoomNumber,
  R.Beds
FROM Equipment AS E
INNER JOIN Room AS R
  ON E.RoomID = R.ID;