Your first SELECT: columns, aliases and expressions

Every query that reads data is a SELECT. Its simplest form names the columns you want and the table they come from. The result is always a new table: rows and columns that exist only until SSMS shows them.

1SELECT CategoryId, Name2FROM dbo.Categories;

CategoryId  Name
----------  --------
         1  Tools
         2  Building
         3  Timber
         4  Fixings
         5  Paint
         6  Safety
         7  Plumbing

The SELECT list decides the shape of the result: which columns, in what order, with what names. Try it on the Products table:

1SELECT Name, Price2FROM dbo.Products;

SELECT * and why to avoid it in real code

SELECT * means “every column”. It is perfect for a quick look at a table in SSMS. In application code, list the columns: the query then returns the same shape even after someone adds a column, it reads less data, and anyone reading it can see what is used.

Aliases: renaming columns

AS gives a column a new name in the result. The table is not changed. An alias with a space or a reserved word needs square brackets.

1SELECT Name AS Product, Price AS [Unit price], Unit2FROM dbo.Products3WHERE CategoryId = 3;

Product              Unit price  Unit
-------------------  ----------  -----
Timber 2x4 2.4 m           6.80  each
Plywood sheet 18 mm       42.00  sheet

Calculated columns

A SELECT list can contain expressions, worked out for every row as the result is produced. Nothing is stored. Here is the value of the stock on the shelf for each paint product:

1SELECT Name, Price, Stock, Price * Stock AS StockValue2FROM dbo.Products3WHERE CategoryId = 5;

Name                Price  Stock  StockValue
------------------  -----  -----  ----------
Masonry paint 10 L  54.00     25     1350.00
Paint roller set    12.50     30      375.00

Arithmetic uses + - * / and % (remainder). The usual precedence applies: *, / and % before + and -, with parentheses to change it.

Joining text together

Use + or CONCAT to join strings. CONCAT also converts numbers for you and treats NULL as empty text, which + does not (more on NULL in two lessons). You can also put fixed values, called literals, in the SELECT list.

1SELECT FirstName + N' ' + LastName AS FullName,2       CONCAT(FirstName, N' joined on ', JoinedOn) AS Note,3       N'BuildMart' AS Shop4FROM dbo.Customers5WHERE CustomerId <= 3;

FullName     Note                        Shop
-----------  --------------------------  ---------
Asha Patel   Asha joined on 2023-03-02   BuildMart
Ben Carter   Ben joined on 2023-05-19    BuildMart
Chloe Evans  Chloe joined on 2023-06-07  BuildMart

CONCAT turned the JoinedOn date into text by itself. With +, SQL Server would try to turn the text into a date instead, and fail.

A SELECT does not even need a table. Without FROM it returns a single row, which makes SQL Server a handy calculator:

1SELECT 2 + 3 * 4 AS Answer, (2 + 3) * 4 AS WithBrackets, 17 % 5 AS Remainder;

Answer  WithBrackets  Remainder
------  ------------  ---------
    14            20          2

The order SQL Server reads a query in

You write SELECT first, but it is not processed first. The logical processing order is:

1. FROM: which table(s) the rows come from.
2. WHERE: keep only the rows that pass the test.
3. SELECT: work out the columns and expressions, and apply aliases.
4. ORDER BY: sort the result (lesson 6).

Because WHERE runs before SELECT, an alias made in the SELECT list does not exist yet when WHERE is checked:

1SELECT Name, Price * Stock AS StockValue2FROM dbo.Products3WHERE StockValue > 1000;

Msg 207, Level 16, State 1, Line 1
Invalid column name 'StockValue'.

Repeat the expression in the WHERE clause instead (WHERE Price * Stock > 1000). ORDER BY runs after SELECT, so it is the one clause where the alias does work.

Style that pays off

• Write keywords in capitals (SELECT, FROM, WHERE) and names as they are defined. SQL Server does not care; readers do.
• Put each clause on its own line.
• End statements with a semicolon. It is optional for most statements today, but required before some (such as a WITH common table expression), and Microsoft recommends it.
• Comment with -- to the end of the line, or /* … */ across lines.

1SELECT Name, Price, Price * 1.2 AS PriceIncVat2FROM dbo.Products3WHERE CategoryId = 1;

Name              Price  PriceIncVat
----------------  -----  -----------
Claw hammer       18.50       22.200
Cordless drill    89.00      106.800
Tape measure 8 m   9.75       11.700