SELECT with expressions and calculations in SQL - Deep Dive

Overview - SELECT with expressions and calculations

What is it?

SELECT with expressions and calculations means using SQL queries to not only retrieve data but also perform math or combine values directly in the query. Instead of just showing stored data, you can add, subtract, multiply, divide, or create new values on the fly. This helps you get answers faster without changing the original data. It’s like doing quick math while asking for information.

Why it matters

Without being able to calculate or manipulate data in a query, you would need to get raw data first and then do math separately, which is slower and more error-prone. This feature saves time and reduces mistakes by letting the database do the work. It helps businesses make decisions quickly, like calculating total sales or discounts instantly.

Where it fits

Before learning this, you should know basic SELECT queries to get data from tables. After this, you can learn about filtering results with WHERE, grouping data with GROUP BY, and using functions for more complex analysis.

Mental Model

Core Idea

SELECT with expressions lets you create new calculated values from existing data directly in your query.

Think of it like...

It’s like ordering a sandwich and asking the chef to add extra cheese or cut it in half before serving, instead of getting a plain sandwich and doing it yourself later.

┌───────────────┐
│   Table Data  │
└──────┬────────┘
       │
       ▼
┌─────────────────────────────┐
│ SELECT column1, column2,     │
│        (column3 * 2) AS new │
│ FROM table_name             │
└──────────────┬──────────────┘
               │
               ▼
       ┌─────────────────┐
       │ Result with     │
       │ calculated data │
       └─────────────────┘

Build-Up - 6 Steps

1

Foundation

Basic SELECT statement review

Concept: Recall how to retrieve columns from a table using SELECT.

The SELECT statement lets you choose which columns to see from a table. For example, SELECT name, age FROM people; shows the name and age columns for all people.

Result

A list of names and ages from the people table.

Understanding how to pick columns is the first step before adding calculations.

2

Foundation

Simple arithmetic in SELECT

Concept: You can do math operations like addition or multiplication directly in SELECT.

You can write expressions like SELECT price, price * 0.9 AS discounted_price FROM products; to calculate a 10% discount on the fly.

Result

A list of product prices and their discounted prices calculated in the query.

Knowing that SQL can do math inside queries lets you get more useful data without extra steps.

3

Intermediate

Using multiple expressions together

🤔Before reading on: Do you think you can combine addition and multiplication in one SELECT expression? Commit to your answer.

Concept: You can combine several calculations in one query to create complex results.

For example, SELECT price, quantity, price * quantity AS total_cost, (price * quantity) * 0.1 AS tax FROM sales; calculates total cost and tax in one query.

Result

A table showing price, quantity, total cost, and tax for each sale.

Combining expressions lets you get detailed calculations instantly, saving time and reducing errors.

4

Intermediate

Using expressions with string data

🤔Before reading on: Can you add or multiply text columns in SQL? Commit to your answer.

Concept: Expressions can also manipulate text, like joining strings or changing case.

For example, SELECT first_name || ' ' || last_name AS full_name FROM employees; joins first and last names into one column.

Result

A list of full names created by combining two columns.

Knowing you can manipulate text in SELECT expands what you can do without changing data.

5

Advanced

Using CASE expressions for conditional calculations

🤔Before reading on: Do you think SQL can do if-else style calculations inside SELECT? Commit to your answer.

Concept: CASE lets you create conditions inside SELECT to calculate different results based on data.

Example: SELECT price, quantity, CASE WHEN quantity > 10 THEN price * quantity * 0.9 ELSE price * quantity END AS total FROM sales; applies a discount only if quantity is over 10.

Result

A total column that changes calculation based on quantity.

Conditional logic inside SELECT makes queries smarter and more flexible.

6

Expert

Performance impact of complex expressions

🤔Before reading on: Do you think adding many calculations in SELECT always runs fast? Commit to your answer.

Concept: Complex expressions can slow down queries, especially on large data sets or without indexes.

Using many calculations or CASE statements can increase processing time. Optimizing by indexing or pre-calculating values may be needed.

Result

Understanding that complex SELECT expressions can affect speed and resource use.

Knowing performance trade-offs helps you write efficient queries and avoid slowdowns in real systems.

Under the Hood

When you run a SELECT with expressions, the database engine reads each row, then calculates the expressions for that row before returning the result. It does this using its query processor, which parses the SQL, plans the execution, and evaluates expressions in memory. Calculations happen on the fly without changing stored data.

Why designed this way?

This design lets users get customized results quickly without extra steps or changing data. It balances flexibility and safety by keeping data intact while allowing dynamic calculations. Alternatives like pre-calculating and storing results would waste space and reduce flexibility.

┌───────────────┐
│   Table Rows  │
└──────┬────────┘
       │
       ▼
┌─────────────────────────────┐
│ SQL Parser & Optimizer       │
│ (understands query and plans)│
└──────────────┬──────────────┘
               │
               ▼
┌─────────────────────────────┐
│ Query Executor              │
│ (reads rows, computes expr) │
└──────────────┬──────────────┘
               │
               ▼
       ┌─────────────────┐
       │ Result Set with │
       │ calculated cols │
       └─────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does SQL store the results of expressions back into the table automatically? Commit to yes or no.

Common Belief: When you use calculations in SELECT, the database updates the table with those new values.

Reality: SELECT expressions only calculate results for the query output; they do not change stored data unless you use UPDATE or INSERT.

Why it matters: Believing this can cause confusion and errors, expecting data to change when it doesn’t.

Quick: Can you use column aliases defined in SELECT expressions inside the WHERE clause? Commit to yes or no.

Common Belief: You can use the new column names (aliases) from SELECT expressions directly in WHERE to filter rows.

Reality: Aliases from SELECT are not available in WHERE because WHERE runs before SELECT expressions are calculated.

Why it matters: Trying to filter using aliases causes errors or unexpected results; you must repeat the expression or use subqueries.

Quick: Does adding many calculations in SELECT always slow down the query significantly? Commit to yes or no.

Common Belief: More calculations always make queries much slower.

Reality: Simple calculations usually have minimal impact; performance depends on data size, indexes, and complexity.

Why it matters: Overestimating impact may lead to premature optimization or avoiding useful calculations.

Quick: Can you multiply text columns in SQL? Commit to yes or no.

Common Belief: You can multiply or add text columns like numbers in SQL.

Reality: Text columns cannot be used with arithmetic operators; you must use string functions or concatenation.

Why it matters: Misusing operators causes syntax errors and confusion about data types.

Expert Zone

1

Expressions in SELECT are evaluated after FROM and JOIN but before ORDER BY and LIMIT, affecting how you write queries.

2

Using expressions with NULL values can produce unexpected results unless you handle NULLs explicitly with functions like COALESCE.

3

Some databases optimize repeated expressions by calculating them once per row, but others recalculate each time, impacting performance.

When NOT to use

Avoid complex calculations in SELECT when working with huge datasets without proper indexing or when calculations can be precomputed and stored. Instead, use materialized views or computed columns for better performance.

Production Patterns

In real systems, SELECT with expressions is used for dynamic pricing, tax calculations, data formatting, and conditional logic in reports. Often combined with views or stored procedures for reuse and maintainability.

Connections

Spreadsheet formulas

Similar pattern of calculating new values from existing data in cells or columns.

Understanding how spreadsheet formulas work helps grasp SQL expressions as both transform raw data into meaningful results dynamically.

Functional programming

Both use expressions to compute new values without changing original data.

Knowing functional programming concepts clarifies why SQL expressions produce results on the fly without side effects.

Data visualization

Calculated fields in SQL queries often feed into charts and dashboards.

Recognizing this connection shows how SQL expressions prepare data for clear visual insights.

Common Pitfalls

#1 Trying to use a column alias in WHERE clause causing errors.

Wrong approach:

SELECT price, price * 0.9 AS discounted FROM products WHERE discounted > 100;

Correct approach:

SELECT price, price * 0.9 AS discounted FROM products WHERE price * 0.9 > 100;

Root cause:

Aliases are not recognized in WHERE because filtering happens before SELECT expressions are evaluated.

#2 Assuming SELECT expressions update the table data.

Wrong approach:

SELECT price, price * 2 AS double_price FROM products; -- expecting table to change

Correct approach:

UPDATE products SET price = price * 2; -- to actually change data

Root cause:

SELECT only reads and calculates data for output; it does not modify stored data.

#3 Using arithmetic operators on text columns causing syntax errors.

Wrong approach:

SELECT first_name + last_name FROM employees;

Correct approach:

SELECT first_name || last_name FROM employees;

Root cause:

Text concatenation uses different operators or functions than arithmetic.

Key Takeaways

SELECT with expressions lets you calculate new values from existing data instantly within your query.

You cannot use column aliases from SELECT in WHERE clauses because filtering happens earlier in query processing.

Expressions do not change stored data; they only affect the query’s output.

Combining arithmetic, string, and conditional expressions makes queries powerful and flexible.

Understanding performance impacts helps you write efficient queries for real-world applications.