AS after a column or table precedes an alias.
SELECT trk.AlbumID , alb.Title AS AlbumTitle , trk.SongID , trk.TrackNo , sng.Name AS SongTitle FROM AlbumTrack AS trk INNER JOIN Album AS alb ON trk.AlbumID = alb.AlbumID INNER JOIN Song AS sng ON trk.SongID = sng.SongID;
In the above example, we can see a few Table Aliases, these being
Now if i want to reference the
Album relation, I can simply use the alias
alb (and pray that Intellisense is working).
Notice how in the
SELECT, it is immediately clear what fields I’m using and which relations they come from. This is simply because I’ve chosen sensibly named aliases.
I’ve chosen to use short aliases. So my table references are not as verbose. This mainly comes into play in the
You don’t have to alias if you don’t want to
Aliasing is not necessary (except in self-joins!). If you want to, you can avoid aliasing altogether and be explicit with your references like so:
SELECT AlbumTrack.AlbumID , Album.Title AS AlbumTitle , AlbumTrack.SongID , AlbumTrack.TrackNo , Song.Name AS SongTitle FROM AlbumTrack INNER JOIN Album ON AlbumTrack.AlbumID = Album.AlbumID INNER JOIN music.Song ON AlbumTrack.SongID = Song.SongID
You may feel that this is more readable.
And certainly there are situations where it is possible for aliases to make things more complicated instead…
Alphabetical Table Aliases = BAD
Let’s take the original query and change all the aliases to letters of the alphabet.
We often find ourselves doing this. It’s quick and easy and requires no planning. But in terms of readability, it has no benefit. And down the road you may be making things harder for yourself.
SELECT a.AlbumID , b.Title AS AlbumTitle , a.SongID , a.TrackNo , c.Name AS SongTitle FROM AlbumTrack AS a INNER JOIN Album AS b ON a.AlbumID = b.AlbumID INNER JOIN Song AS c ON a.SongID = c.SongID;
One idea here is that we can be consistent across all our queries with our table aliases.
That way, we always know that the first table is
a, followed by
b and so on…
Indeed, the alphabet is an ordered sequence and if the ordering of our tables is important then we might want to represent that importance via our aliases.
But aliases are just syntactic sugar. As I mentioned above, you don’t even need to have aliases. The point of an alias is to improve readability.
Aliases are not there to convey the structure of the code. Let the code speak for itself.
Let’s focus on this part:
SELECT a.AlbumID , b.Title AS AlbumTitle , a.SongID , a.TrackNo , c.Name AS SongTitle
Try to discern which tables the fields belong to.
a.AlbumID comes from
b.Title comes from
But what tables are these?
In order for the alias to have any meaning, we need to peruse the query in its entirely. And we will need to do this every time because we won’t remember! Ultimately, these aliases provide no benefit in readability.
Furthermore, the ordering of our tables is not important when it comes to the
And when the
INNER JOIN is commutative.
FROM AlbumTrack AS trk INNER JOIN Album AS alb ON trk.AlbumID = alb.AlbumID
The above could be written as below and produce the same result.
FROM Album AS alb INNER JOIN AlbumTrack AS trk ON alb.AlbumID = trk.AlbumID
INNER JOIN is associative.
The order of the groupings of multiple joins does not matter:
FROM AlbumTrack AS trk INNER JOIN Album AS alb ON trk.AlbumID = alb.AlbumID INNER JOIN Song AS sng ON trk.SongID = sng.SongID;
The above is equivalent to:
FROM AlbumTrack AS trk INNER JOIN Song AS sng ON trk.SongID = sng.SongID INNER JOIN Album AS alb ON trk.AlbumID = alb.AlbumID;
In terms of the execution plan, there may be some optimal ordering. However, the query optimizer will figure that out for you.
SELECT alb.Title AS AlbumTitle ...
In the above example, we rename
Note how it is still obvious that
alb.Title is a projection of
Album, thanks to the alias.
In Relational Algebra, this is simple a rename.