Be it running reports or displaying data in some other visualization, SQL SELECT column expressions should be meaningful and understandable. To provide those valuable query results, SQL Developers, use a multitude of available functions, adjacent columns, or other means not readily apparent to end-users. All that being said, the column names often suffer the most as far as readability is concerned, taking on long function call names or other combined expressions. But, as luck would be on our side, there is an easy fix and that is aliasing columns using the AS keyword. Although AS is optional – in this particular context – I err on the side of readability and use it when aliasing SELECT column expressions.
OS and DB used:
- Linux Mint 20 “Ulyana”
- MySQL 8.0.23
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
For the examples in this post, I’ll use this ‘friends’ table with the following structure:
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| country | char(3) | YES | | NULL | |
| first_name | varchar(25) | NO | | NULL | |
| last_name | varchar(25) | NO | | NULL | |
| state | char(2) | YES | | NULL | |
| phone_num | char(12) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| age | int | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.0035 sec)
You can imagine that a common request might involve displaying the ‘first_name’ and ‘last_name’ columns into a full name type of output. Nothing difficult at all in MySQL. Using nested CONCAT() function calls, the query results look like this:
FROM friends;
+--------------------------------------------+
| CONCAT(CONCAT(first_name, ' '), last_name) |
+--------------------------------------------+
| Max Maxer |
| Mary Murphy |
| Charlie Charles |
| Humpty Dumpty |
| Roger Dodger |
| Jim Russ |
| Jupyter Moonbeam |
+--------------------------------------------+
7 rows in set (0.0012 sec)
Would you look at that column name? Show that in a report and users’ heads will likely spin. What is there to do about it? Use the AS keyword and alias the column to a more appropriate or display-friendly name:
FROM friends;
+------------------+
| full_name |
+------------------+
| Max Maxer |
| Mary Murphy |
| Charlie Charles |
| Humpty Dumpty |
| Roger Dodger |
| Jim Russ |
| Jupyter Moonbeam |
+------------------+
7 rows in set (0.0010 sec)
Much better isn’t it?
Truth be told, you can even omit the AS keyword all-together and get the same results:
FROM friends;
+------------------+
| full_name |
+------------------+
| Max Maxer |
| Mary Murphy |
| Charlie Charles |
| Humpty Dumpty |
| Roger Dodger |
| Jim Russ |
| Jupyter Moonbeam |
+------------------+
7 rows in set (0.0011 sec)
Consider making a small donation to support my efforts as I continue to provide valuable content here on my blog. Thanks so much!
Gotchas and Extras
Just to be clear, you can’t do this:
FROM friends
WHERE full_name LIKE 'M%';
ERROR: 1054: Unknown column 'full_name' in 'where clause'
SELECT columns and expressions are not yet available when the WHERE clause executes, hence the error message of “Unknown column full_name in where clause”.
But, you can use the column alias in the ORDER BY clause:
FROM friends
ORDER BY full_name ASC;
+------------------+
| full_name |
+------------------+
| Charlie Charles |
| Humpty Dumpty |
| Jim Russ |
| Jupyter Moonbeam |
| Mary Murphy |
| Max Maxer |
| Roger Dodger |
+------------------+
7 rows in set (0.0598 sec)
The next time your SELECT list column names need to be better named, alias them with (or without) the AS keyword.
Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.