In this lesson, you will learn how to create and filter by data that doesn’t exist in your table yet by performing calculations directly within your query.
The Concept of Derived Attributes
Sometimes, the information you need isn't stored as a single column in your database. Instead, it is a "derived attribute"—a value calculated from existing columns. For example, if you have a table of products with columns for price and discount, you might want to find items where the final price (derived as price - discount) is less than $20.
While you can create an alias to display this calculation, you might also want to limit your results based on that new math.
Filtering with Computed Logic
To filter by a derived attribute, you perform the math directly inside your WHERE clause. SQL will process this calculation for every row before deciding whether to include it in your results.
Imagine a table called employees with columns salary and bonus. If you want to find employees whose total compensation exceeds $50,000, you write your query like this:
SELECT name, salary, bonus
FROM employees
WHERE (salary + bonus) > 50000;
Why Do This?
By using derived attributes in your WHERE clause, you avoid the need to store redundant data in your database (like a permanent total_compensation column), which keeps your schema clean and prevents data synchronization errors.
When you write the math inside the WHERE clause, the database engine calculates the sum (salary + bonus) row by row. If the result is true (greater than 50,000), that row is sent to your result set. If it’s false, the row is ignored. This allows you to perform complex data analysis on the fly without needing to modify the underlying table structure.
Note that because the WHERE clause is processed before the SELECT clause, you cannot use an alias (defined in your SELECT) inside your WHERE clause. You must use the full calculation instead.
Key Takeaway: You can filter your results based on calculated values by performing arithmetic or logical operations directly within your WHERE clause, allowing you to query for data relationships that aren't explicitly saved in your columns.