Expressions in SELECT

Concepts covered: Query Basics, Operator Precedence

An item on the SELECT list does not have to be a column. It can be an expression: a column combined with numbers and other columns using +, -, * and /. The database evaluates the expression once per row, using that row's own values, and the results become a new column that exists only in the result. The table gains nothing and loses nothing; the query answers a question the table never stored.

SELECT
  product_name,
  price - 5 AS coupon_price
FROM products

Each row's price has 5 taken from it: 24.0 becomes 19.0, 349.0 becomes 344.0, 89.5 becomes 84.5, 199.0 becomes 194.0 and 18.75 becomes 13.75. The alias matters here more than anywhere else. Without it the database has to invent a header for the computed column, and the one it invents, _col1 for the second item on the list, tells the reader nothing about what the number means. Every computed column gets an AS, with a name that says what was computed.

Columns combined with columns

SELECT
  product_name,
  price,
  price * 1.5 AS retail_price,
  price * 1.5 - price AS markup
FROM products

The query reads price in 2 expressions. retail_price multiplies it by 1.5, so the Office Chair at 199.0 shows 298.5. markup repeats that product and subtracts the original price, giving 99.5 on the same row. Each expression is computed on its own from the table's columns, which is why the multiplication is spelled out twice: markup cannot refer to retail_price by its alias, and price * 1.5 AS retail_price, retail_price - price AS markup stops with an error that retail_price cannot be resolved. The reason sits in the order the database works through a query, which a later lesson lays out.

Operator order and parentheses

Multiplication and division happen before addition and subtraction, as in arithmetic class. price - 5 * 2 takes 10 from the price. (price - 5) * 2 takes 5 and then doubles what is left. Parentheses cost nothing, and a reader should never have to recall precedence to know what a query computes, so wrap any expression that mixes operators even when the default order happens to be the one you want.

For the Desk Lamp, where price is 24.0, what value does this expression produce?

SELECT
  product_name,
  price - 5 * 2 AS adjusted
FROM products

• 38.0, because the subtraction runs first and the result is then doubled
• 19.0, because the database applies price - 5 and ignores the * 2
• 14.0, because 5 * 2 is computed first and 10 is then taken from 24.0
• 48.0, because the * 2 doubles the price and the - 5 is applied to nothing

Whole numbers divide to whole numbers

SELECT
  product_name,
  units_on_hand / box_size AS full_boxes,
  units_on_hand % box_size AS loose_units
FROM inventory

This example reads a second small table, inventory, which holds stock counts for the same 5 products: product_name, units_on_hand for how many units sit in the warehouse, and box_size for how many units fit in 1 shipping box. Both count columns hold whole numbers, and dividing 2 whole numbers gives a whole number with the fraction dropped: 40 / 12 returns 3, with the .33 discarded. The % operator returns what that division left over, so 40 % 12 is 4, and the 2 results together describe 40 lamps as 3 full boxes plus 4 loose units. When the fraction is wanted, write 1 side with a decimal point: 40 / 12.0 returns 3.3, keeping as many decimal places as the 12.0 was written with, and 10.0 / 4 returns 2.5.

A merchant wants every product's name beside its price with a 3 dollar handling fee added, under the header final_price. Drag the tiles into the order that builds this query from the products table. One tile would turn the header into a piece of text, so leave it in the bank.

About this lesson

This section is part of the Query Structure: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.