This lecture covers using table aliases to simplify queries. In our last few lectures, we wrote queries that joined tables. You probably noticed that we had to type out the full table name every time we specified a column, like Customers.CustomerID or Orders.OrderID. This can make our queries long, repetitive, and harder to read.
To solve this, SQL allows us to create a temporary, shorter name for a table inside a query. This temporary name is called an alias.
The Alias Syntax
You can create an alias for a table using the AS keyword, or by simply putting the alias after the table name. Both of the following are valid:
FROM TableName AS t
FROM TableName t (This is the more common and quicker way).
Rewriting Our `INNER JOIN` with Aliases
Let’s take the INNER JOIN query from lecture 24 and rewrite it using aliases. It’s common practice to use the first letter of the table name as its alias.
Without Aliases (The Old Way)
SELECT
Orders.OrderID,
Orders.OrderDate,
Customers.FirstName
FROM Orders
INNER JOIN Customers ON Orders.Customer_ID = Customers.CustomerID;With Aliases (The New, Better Way)
SELECT
o.OrderID,
o.OrderDate,
c.FirstName
FROM Orders AS o
INNER JOIN Customers AS c ON o.Customer_ID = c.CustomerID;
See how much cleaner that is? We declared an alias `o` for `Orders` and `c` for `Customers`. Now, instead of writing the full table name each time, we just use the short alias. The query is easier to write and, once you get used to it, much easier to read.
Why Aliases Are Essential
Aliases are more than just a convenience. They are sometimes required. For example, if you need to join a table to *itself* (a self-join), you must use aliases to distinguish between the two instances of the table. We will see an advanced example of this later in the course.
From now on, we will use table aliases in all of our join queries as it is a standard best practice.
In the Next Lecture…
It’s time for a new, more complex project. We will begin designing a database for a blog. This will involve identifying the need for multiple tables (`Authors`, `Posts`, `Comments`) and planning the relationships between them before we write a single line of SQL code.
