In this medium post, we will explore aliases and self-joins. These can be helpful for a number of reasons.
Learning objectives:
- How to create aliases for use in our queries.
- Discuss common naming conventions.
- Discuss and establish self-joins within a SQL database.
What is an Alias?
- SQL aliases give a table or column a temporary name.
- Make column names more readable.
- An alias only exists for the duration of the query.
Query Example with and without Aliases.
SELECT vendor_name,
product_name,
product_price
FROM Vendors, Products
WHERE Vendors.vendor_id = Products.vendor_id;Above is an example of a query without aliases.
SELECT vendor_name,
product_name,
product_price
FROM Vendors AS v, Products AS p
WHERE v.vendor_id = p.vendor_id;The above is an example of the use of aliases.
As you can see the second example, even though we had to define the alias, is still shorter.
The alias you use can be anything you want, common ways to use aliases is by using a shortened form of the word, the first letter of the word or even just ‘a’, ‘b’, ‘c’, etc.
Self Joins.
In this example, we will match customers from the same city.
We will be taking the table and treating it like two separate tables.
We will then join the original table to itself.
Sound tricky? This pseudocode should help.
SELECT column_name(s)
FROM table1 T1,
table2 T2
WHERE condition.Here is an actual example:
SELECT A.CustomerName AS CustomerName1,
B.CustomerName AS CustomerName2,
A.City
FROM Customers A,
Customers B
WHERE A.CustomerID = B.CustomerID
AND A.City = B.City
ORDER BY A.City;You can see that we are drawing from the same list of customers twice. Then we are declaring that we want to SELF JOIN on ‘A.CustomerID’ and ‘A.City’. Following this, we will order by ‘A.City’.
Without table aliases, we would not be able to do Self Joins.
