Query Primer - Column Aliases

Although the mysql tool will generate labels for the columns returned by your queries, you may want to assign your own labels. While you might want to assign a new label to a column from a table (if it is poorly or ambiguously named), you will almost certainly want to assign your own labels to those columns in your result set that are generated by expressions or built-in function calls. You can do so by adding a column alias after each element of your selectclause. Here’s the previous query against theemployeetable with column aliases applied to three of the columns:

  mysql> SELECT emp_id,
      ->   'ACTIVE' status,
      ->   emp_id * 3.14159 empid_x_pi,
      ->   UPPER(lname) last_name_upper
      -> FROM employee;

emp_id  status  empid_x_pi  last_name_upper
	1  ACTIVE	3.14159  SMITH
	2  ACTIVE	6.28318  BARKER
	3  ACTIVE	9.42477  TYLER
	4 ACTIVE	12.56636  HAWTHORNE
	5  ACTIVE	15.70795  GOODING
	6  ACTIVE	18.84954  FLEMING
	7 ACTIVE	21.99113  TUCKER
	8 ACTIVE	25.13272 PARKER
	9 ACTIVE	28.27431  GROSSMAN
	10  ACTIVE	31.41590 ROBERTS
	11  ACTIVE	34.55749  ZIEGLER
	12  ACTIVE	37.69908 JAMESON
	13 ACTIVE	40.84067  BLAKE
	14  ACTIVE	43.98226 MASON
	15 ACTIVE	47.12385 PORTMAN
	16  ACTIVE	50.26544 MARKHAM
	17  ACTIVE	53.40703  FOWLER
	18 ACTIVE	56.54862 TULMAN

18 rows in set (0.00 sec)

If you look at the column headers, you can see that the second, third, and fourth columns now have reasonable names instead of simply being labeled with the function or expression that generated the column. If you look at theselectclause, you can see how the column aliasesstatus,empid_x_pi, andlast_name_upperare added after the second, third, and fourth columns. I think you will agree that the output is easier to understand with column aliases in place, and it would be easier to work with programmatically if you were issuing the query from within Java or C# rather than interactively via the mysql tool.

Please check back next week for the continuation of this article.