Wednesday, 26 December 2018

MySQL Column Aliases

In MySQL by default the column’s heading in the results produced by your query, is the original column’s name as it was defined in the table.
1
2
3
4
5
6
7
SELECT bookName
FROM books
 
bookName
----------
The Great Gatsby
Dune
In many cases, column’s heading is not descriptive enough. In addition different operations, such as: string concatenation or mathematical calculation, will change the column’s heading to be even less readable and more difficult to understand:
1
2
3
4
5
6
7
SELECT CONCAT('hello', ' ' , bookName)
FROM books
 
CONCAT('hello', ' ', bookname)
------------------------------
Hello The Great Gatsby
Hello Dune
MySQL Column Aliases are used to change column’s heading. Using the MySQL Column Aliases the headings will be more easy to understand and more meaningful. To create an MySQL Column Alias use the following syntax :
1
2
SELECT column_name AS 'Column_Alias'
FROM table_name
For Example : 
1
2
SELECT bookName, bookPrice, bookPrice  + 500  AS 'NewPrice'
FROM books
The result:
1
2
3
bookName       bookPrice     NewPrice
---------      ----------    ------------------
Dune           30            530
You can also write the MySQL column’s alias without the AS Keyword, for example:
1
2
SELECT bookName, bookPrice, bookPrice  + 500  'NewPrice'
FROM books
The result:
1
2
3
bookName        bookPrice   NewPrice
---------      -----------  ------------------
Dune           30           530
It is also possible to write the MySQL column’s alias without the enclosing single quotes (‘ ‘):
1
2
SELECT bookName, bookPrice, bookPrice  + 500 newPrice
FROM books
The result:
1
2
3
bookName        bookPrice  newPrice
-----------     ---------   ------------------
Dune            30          530
  • When an MySQL column alias is written without enclosing single quotes (‘ ‘), it is not possible to write the alias as two (or more) separate words; you will have to insert some character between these words, underline (_) is most commonly used in these cases.
01
02
03
04
05
06
07
08
09
10
11
12
-- This query will generate an error
-- The 'New Unit Price' column alias should be enclosed within single quotes
SELECT bookName, bookPrice, bookPrice * 1.1 AS New Unit Price
FROM books
 
-- Insert an underline between these words :
SELECT bookName, bookPrice, bookPrice * 1.1 AS New_bookPrice
FROM books
 
-- Or use single quotes :
SELECT bookName, bookPrice, bookPrice * 1.1 AS 'New Unit Price'
FROM books
  • In order to make your MySQL query more readable, always use the AS keyword, always use single quotes (‘ ‘), and when your alias consists of two words or more, always ensure that you insert an underline between them.
  • Another common approach to handle an alias consists of two words or more, is to concatenate the words, and capitalize the first letter of each word  :

1
2
SELECT bookName, bookPrice, bookPrice  + 500 AS 'NewBookPrice'
FROM books

0 comments:

Post a Comment