Naming and ordering columns
In the previous lesson, we looked at how to use SELECT and FROM to retrieve columns from a table. In this lesson, we'll look at how to specify the order and name of the columns we retrieve.
Ordering columns
When you write SQL queries, the order of the columns in your SELECT query determines the order in which they will appear in the query result set.
For example, even though the original students table stores the columns in the order id, name, major, age and nationality, we can retrieve the columns in any order we like. If we write:
SELECT name, id
FROM students
we’ll get the following:
+-------+----+
| name | id |
+-------+----+
| Matt | 1 |
| Roger | 2 |
| Shawn | 3 |
| Bella | 4 |
| Helen | 5 |
| Juno | 6 |
| Rapha | 7 |
+-------+----+
Even though the column id appears before the column name in the original students table, we were able to specify that the query result set should place the columns in a different order.
(Re)naming columns
By default, the columns in our query result set will have the same names as the original columns in the source table.
With SQL, however, it’s possible to rename columns with an alias, by using the AS operator. For example, if we write:
SELECT
id,
name AS student_name,
age
FROM students
The id and age columns in our query result set will retain their original names, but the column which was called name in the original table (students) has been given an alias and so will be called student_name in the query result set:
+----+--------------+-----+
| id | student_name | age |
+----+--------------+-----+
| 1 | Matt | 21 |
| 2 | Roger | 20 |
| 3 | Shawn | 19 |
| 4 | Bella | 25 |
| 5 | Helen | 27 |
| 6 | Juno | 22 |
| 7 | Rapha | 22 |
+----+--------------+-----+
In this example, there’s not really much point in renaming any columns using the AS operator, because the original column names were short and informative. But the AS operator can be really useful whenever the source tables have uninformative or not-useful names. For example, consider the following table payroll:
To someone with domain knowledge, it might be obvious that the code column is referring to the employee’s tax code. To unaware users like us, however, it’s not clear! In situations like this, we can use the AS operator to ensure that this column is displayed with a clearer name in our query result set:
SELECT
* EXCEPT (code),
code AS tax_code
FROM payroll
Note: We're not renaming the column in the original table. By using the AS operator like this, we're just specifying the name the column will have in our query result set (the temporary table created when we run our query). The code column will still retain its original name in the original payroll table.
Quick tip
Technically, you don’t need to use AS to rename columns in SQL. You’ll occasionally see people omit the AS operator and simply place the alias directly after the original column name:
SELECT
id,
name student_name,
age
FROM students
Personally, I think it's clearly to use AS, but it's up to you!
Practice
Now it's your turn! Open the following questions in a new tab to have a go at renaming and ordering columns: