Arithmetic and Computed Expressions

In this lesson, you will learn how to perform math directly inside your SELECT statements to transform stored data into meaningful information.

Beyond Storing Data

Up until now, we have focused on retrieving data exactly as it is stored in a table. However, raw data often needs a little "processing" before it becomes useful. SQL allows you to use standard arithmetic operators—such as + (addition), - (subtraction), * (multiplication), and / (division)—directly within your query.

Imagine you have a table called products with the columns price and discount. If you want to see the final price for each item, you don't need to manually calculate it or update your database. You can perform the math on the fly:

SELECT 
    product_name, 
    price - discount AS final_price
FROM products;

The Power of Computed Expressions

When you use an expression like price - discount, the database calculates the result for every row as it fetches the data. Note that this does not change the actual data stored in your table; it simply creates a temporary, computed result for your current query.

You can also use these expressions to perform projections or to compare values. For example, if you wanted to see the total inventory value for each item:

SELECT 
    product_name, 
    price * quantity_in_stock AS inventory_value
FROM products;

Tips for Success

1. Use Aliasing: Because computed columns don't have a name in the original table, always use the AS keyword to give your result a descriptive label (like inventory_value). This makes your output much easier to read.
2. Order of Operations: SQL follows standard mathematical rules (PEMDAS/BODMAS). Use parentheses () if you need to force a specific order of calculation (e.g., (price - discount) * quantity).
3. Nulls in Math: Remember your lesson on NULL values: any arithmetic operation performed on a NULL will result in NULL. If the discount is unknown for a product, the final_price will also return as NULL.

Key Takeaway: Arithmetic expressions allow you to transform raw data into calculated insights during retrieval without altering the underlying table structure.