At first, I thought of SQL as just a data acquisition tool. Complex calculations and logic were handled by the program, and SQL was merely a supporting tool behind the scenes. But that perception completely changed at some point. When I actually encountered a situation where I had to express complex queries and logic using only SQL, I realized that this was not just extraction, and that the very design of the process could be changed depending on how I thought about it.

SQL is a language that can give data behavior by encapsulating logic and automating tasks. Although I have only just scratched the surface, I am now completely fascinated by its vast possibilities. In this blog, I will introduce some SQL features that I have actually used and found useful. Even if you are not a technical expert, I am sure your perspective on SQL will change as you read on.

### SQL Functions

#### 1. View

A view is a virtual table based on an SQL SELECT query (Query A). Views do not store data, only the query logic. When you run another query (Query B) against a view, the database actually runs Query A first and then uses the results as a temporary table for Query B to process. Although a view may appear to be an actual table in your SQL tool or schema browser, it does not physically exist in the database.

| Pros | Cons |
| -------- | -------- |
| Simplifies complex queries | No data storage (no pre-computation) |
|Reusable logic|Cannot accept parameters|
| Provides abstractions (e.g., read-only APIs) | Can be slow with too many joins|

Example:
CREATE VIEW active_users AS
SELECT * FROM users WHERE is_active = 1;

// Then use it as follows: 
SELECT * FROM active_users;

#### 2. Trigger

A Trigger is a logic that runs automatically in response to data changes, such as an INSERT, UPDATE, or DELETE.
One such case is when inserting a new row into table A required updating related columns in table B. I used a Trigger to automatically update table B whenever table A was inserted or updated, without having to remember to update it manually in my code.

| Pros | Cons |
| -------- | -------- |
| Automates auditing, validation, and logging | Difficult to debug (runs in the background) |
| Ensures data integrity | Do not overuse as it can degrade write performance|

// Example:
CREATE TRIGGER log_delete
AFTER DELETE ON users
FOR EACH ROW
INSERT INTO user_deletions (user_id, deleted_at) VALUES (OLD.id, NOW());

#### 3. Stored Procedure

A Stored Procedure is a predefined SQL block that you can call with parameters to perform an operation or return a result.
It's like a custom function like `findById()` in your code, but written directly in SQL. The main difference is that you define the logic yourself.

| Pros | Cons |
| -------- | -------- |
| Handles complex multi-step logic | Cannot be used within SELECT |
| Reusable and maintainable | More verbose than functions. |
| Data can be modified (INSERT/UPDATE/DELETE). | |

// Example:
CREATE PROCEDURE deactivate_user(IN user_id INT)
BEGIN
UPDATE users SET is_active = 0 WHERE id = user_id;
END;

// Call this as follows: 
CALL deactivate_user(101)

#### 4. Function

A Function is a reusable SQL block that returns a value and can be used within a query. They are commonly used for calculations and formatting. If a Function always returns the same result for the same input, it should be declared as DETERMINISTIC. This is required for use with generated columns and some optimizations.
If your users table only contains a birthday column, but you want a query to return a user's age, you can create a function that calculates and returns the age based on the birthdate.

| Pros | Cons |
| -------- | -------- |
| Allows logic reuse within SELECT, WHERE, etc. | Must be deterministic to be used in functions |
| Great for business rules and formatting | Cannot perform side effects (no INSERT, etc.)|

Example:
CREATE FUNCTION add_vat(price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
RETURN price * 1.1;

→ SELECT name, add_vat(price) FROM products;

#### 5. Generated Column

A generated column is a column whose value is calculated automatically based on other columns in the same row. Depending on your needs, this column can be a virtual column or it can actually be physically stored in the database.

| Pros | Cons |
| -------- | -------- |
| Keeps logic in the schema | Limited to simple expressions or deterministic functions |
| Automatically updates when source columns change | Cannot contain subqueries or expressions such as NOW() |
| Available in indexes (if STORED) | |

// Example:
CREATE TABLE order_items (
  unit_price DECIMAL(10,2),
  quantity INT,
  total_price DECIMAL(10,2) 
    GENERATED ALWAYS AS (unit_price * quantity) STORED
  INDEX idx_total_price (total_price)
);

#### 6. Common Table Expression

Common Table Expressions are temporary named result sets defined using WITH. They are used to simplify complex queries and enable recursion. They are easier to read than subqueries and keep their structure clear.

| Pros | Cons |
| -------- | -------- |
| Breaking queries into steps improves readability. | Slightly slower than subqueries in some engines (due to the use of temporary tables).|
| Deeply nested queries are easier to manage. | Cannot be reused across multiple queries unless manually materialized. |
| Supports recursive queries (e.g., organizational charts, hierarchies). | Limited to the lifetime of a single query. |

// Example:
WITH high_value_orders AS (
SELECT * FROM orders WHERE total > 1000
)
SELECT customer_id, COUNT(*) FROM high_value_orders GROUP BY customer_id;

// → Break down complex queries into readable chunks.

#### 7. Window Function

A SQL feature (unlike GROUP BY) that performs calculations across rows related to the current row without collapsing them, useful for things like ranking within departments.

| Pros | Cons |
| -------- | -------- |
| Allows for advanced analysis: ranking, moving average, cumulative total, lag/lead | May be difficult to read at first |
| Maintains row-by-row structure (no grouping required) | Not supported in older database versions (MySQL 8 and earlier) |
| Perfect for partitioning (by user, category, etc.) | Can be slow to query large datasets if not properly indexed |

// Example:
SELECT
employee_id,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

// → Rank employees by salary within each department without grouping or losing details.

### Conclusion

SQL is more than just a data extraction language; it can also build a logic layer that can standardize, reuse, and automate processes. The features introduced here are just a small part of it, but they will greatly expand its scope of use. You don't need to master everything. I myself haven't used all but the important thing is to know what you can do and be able to choose the optimal method when needed.

<small>
Published date: 2025-06-26 <br/>
Also published <a href="https://josysnavi.jp/2025/sql-useful-features">here</a>.
</small>