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.
  • AS gives 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.

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

Cheat sheets