Specifying Columns
List the columns you want, in the order you want them, and rename them in the result with AS.
Novice · T-SQL · SQL · Updated
After this lesson you can
- Choose and rename the columns a query returns
Key takeaways
- In application code, name the columns you need: less data travels, and the result doesn't change when someone adds a column.
- Columns come back in the order you list them, whatever their order in the table.
ASgives a column an alias, a new name in the result only; the table doesn't change.- An alias with a space needs square brackets, as in
UnitPrice AS [List Price]; brackets also allow names such as[Order].
SELECT * is handy for exploring, but in application programming it's best to name exactly the columns you need:
SELECT ProductName, UnitPrice, UnitsInStock
FROM Products;
| ProductName | UnitPrice | UnitsInStock |
|---|---|---|
| Chai | 18.00 | 39 |
| Chang | 19.00 | 17 |
| Aniseed Syrup | 10.00 | 13 |
| Chef Anton's Cajun Seasoning | 22.00 | 53 |
Why not just use *?
- Less data travels. A table can have dozens of columns, some of them large. Your app only pays for what it asks for.
- The result doesn't change by surprise. If someone adds a column to the table later, a
SELECT *suddenly returns it, and code that expected the old columns can break. - The query says what it's for. Anyone reading it sees exactly which data the page uses.
Choosing the order
The columns come back in the order you list them, whatever their order in the table:
SELECT City, Country, CompanyName
FROM Customers;
Long column lists are often written one per line, with the commas at the start of each line, so a column is easy to add, remove or comment out:
SELECT CompanyName
, ContactName
, City
, Country
FROM Customers;
Renaming columns with AS
AS gives a column a different name in the result, called an alias. The table doesn't change; only the heading of the result does:
SELECT ProductName AS Product
, UnitPrice AS Price
, UnitsInStock AS InStock
FROM Products;
| Product | Price | InStock |
|---|---|---|
| Chai | 18.00 | 39 |
| Chang | 19.00 | 17 |
An alias with a space in it needs square brackets: UnitPrice AS [List Price]. Square brackets also let you use a table or column name that has a space or is a keyword, such as [Order] in the BooksPlus database.
Do it: make the sales team's call sheet
The sales team is calling every customer this week. Their call sheet should list the person to ask for first, then the company, then the phone number, with headings people can read.
The Customers table (the Tables tab shows the SQL that makes it) has these rows:
| CustomerId | CompanyName | ContactName | City | Phone |
|---|---|---|---|---|
| ALFKI | Alfreds Futterkiste | Maria Anders | Berlin | 030-0074321 |
| ANATR | Ana Trujillo Emparedados y helados | Ana Trujillo | México D.F. | (5) 555-4729 |
| AROUT | Around the Horn | Thomas Hardy | London | (171) 555-7788 |
| BERGS | Berglunds snabbköp | Christina Berglund | Luleå | 0921-12 34 65 |
Your query should give exactly these rows, in any order:
| Contact | Company | Phone Number |
|---|---|---|
| Maria Anders | Alfreds Futterkiste | 030-0074321 |
| Ana Trujillo | Ana Trujillo Emparedados y helados | (5) 555-4729 |
| Thomas Hardy | Around the Horn | (171) 555-7788 |
| Christina Berglund | Berglunds snabbköp | 0921-12 34 65 |
Your job: change the query in the box so it gives the call sheet.
- Three columns, in this order:
ContactNamenamedContact,CompanyNamenamedCompany, andPhonenamedPhone Number(with a space). - Every customer: no filter.
Practiced live on the lesson page: write the SQL right there and check it.
Check yourself
1 question to try on the lesson page.
So what
Naming your columns makes a query faster, safer and self-explaining, and an alias lets the result use the names your app or your report needs.
Key terms
- Column Alias
- Square Bracket Identifiers