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

By

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.

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: ContactName named Contact, CompanyName named Company, and Phone named Phone 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

Cheat sheets