Database Management SystemsSELECT Basics

SELECT Basics: Mastering Data Retrieval

LevelBeginner

Duration60 mins

TopicSELECT Basics

5 / 5

Expressions: Computing Values in SELECT

Beyond Simple Retrieval

So far, we've focused on retrieving data as it exists in tables—selecting columns and returning their stored values. But the SELECT clause is far more powerful than a simple column picker. It is a computation engine capable of transforming, combining, and deriving new values from existing data.

Expressions in SELECT allow you to:

Perform arithmetic calculations (totals, percentages, differences)
Manipulate strings (concatenation, extraction, formatting)
Transform dates and times (age calculation, period extraction, formatting)
Apply conditional logic (CASE statements, COALESCE, NULLIF)
Call built-in and user-defined functions
Embed scalar subqueries for correlated computations

This computational capability transforms SELECT from a retrieval mechanism into a powerful data processing tool, often eliminating the need for application-level post-processing.

What You Will Learn

By the end of this page, you will master expressions in SELECT—understanding every expression type, their evaluation rules, NULL handling, type coercion, and performance implications. You'll be equipped to compute virtually any derived value directly in your queries.

Expression Fundamentals

An expression in SQL is any construct that evaluates to a value. Expressions can appear in many SQL contexts, but the SELECT clause is where they're most visibly powerful—each expression defines a column in the result set.

Expression components:

Literals: Constant values (42, 'hello', DATE '2024-01-15')
Column references: Placeholders for row values (first_name, e.salary)
Operators: Combine values (+, -, *, /, ||, AND, OR)
Functions: Transform values (UPPER, ROUND, COALESCE)
Subqueries: Nested queries returning scalar values
CASE expressions: Conditional value selection

Evaluation model:

Expressions in SELECT are evaluated once per row in the result set. If your query processes 1000 rows, each expression computes its value 1000 times (once per row).

expression_fundamentals.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- Literals: Constant values in every row
SELECT 
    'Employee' AS record_type,     -- String literal
    42 AS answer,                   -- Numeric literal
    TRUE AS is_active,              -- Boolean literal
    DATE '2024-01-01' AS start_date -- Date literal
FROM employees;
-- Every row contains these same constant values
 
-- Column references: Different for each row
SELECT 
    first_name,        -- Simple column reference
    e.last_name,       -- Qualified column reference
    salary             -- Another column reference
FROM employees AS e;
 
-- Operator expressions: Combining values
SELECT 
    first_name || ' ' || last_name AS full_name,    -- Concatenation
    salary * 12 AS annual_salary,                    -- Multiplication
    salary + COALESCE(commission, 0) AS total_comp,  -- Addition
    hire_date + INTERVAL '1 year' AS anniversary     -- Date arithmetic
FROM employees;
 
-- Function expressions: Transforming values
SELECT 
    UPPER(first_name) AS upper_name,
    LENGTH(last_name) AS name_length,
    ROUND(salary / 12, 2) AS monthly_salary,
    EXTRACT(YEAR FROM hire_date) AS hire_year
FROM employees;
 
-- Compound expressions: Combining multiple operations
SELECT 
    UPPER(SUBSTRING(first_name, 1, 1)) || LOWER(SUBSTRING(first_name, 2)) AS proper_name,
    ROUND((salary * 12) * (1 + COALESCE(commission_pct, 0) / 100), 2) AS annual_total
FROM employees;

Every Expression Needs a Name

Expressions without aliases receive auto-generated names that vary by database and are often unusable in application code. Always alias computed expressions: salary * 12 AS annual_salary provides a clear, stable column name.

Arithmetic Expressions

Arithmetic expressions perform mathematical operations on numeric values. These are among the most common expressions in business applications—calculating totals, percentages, averages, and derived metrics.

Standard arithmetic operators:

Operator	Operation	Example
+	Addition	`price + tax`
-	Subtraction	`total - discount`
*	Multiplication	`quantity * price`
/	Division	`total / count`
% or MOD	Modulo (remainder)	`id % 10`

arithmetic_expressions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- Basic arithmetic
SELECT 
    product_name,
    price,
    quantity,
    price * quantity AS line_total
FROM order_items;
 
-- Multiple operations with precedence
SELECT 
    product_name,
    price,
    quantity,
    discount_pct,
    price * quantity AS subtotal,
    price * quantity * (1 - discount_pct / 100) AS discounted_total,
    price * quantity * (1 - discount_pct / 100) * 1.08 AS total_with_tax
FROM order_items;
 
-- Division considerations (integer vs decimal)
SELECT 
    5 / 2 AS integer_division,      -- Result depends on database
    5.0 / 2 AS decimal_division,    -- 2.5 (explicit decimal)
    CAST(5 AS DECIMAL) / 2 AS cast_division  -- 2.5 (cast to decimal)
FROM dual;  -- Use appropriate single-row source for your DB
 
-- Avoiding division by zero
SELECT 
    department_id,
    total_salary,
    employee_count,
    CASE 
        WHEN employee_count = 0 THEN NULL
        ELSE total_salary / employee_count 
    END AS avg_salary,
    -- Or using NULLIF
    total_salary / NULLIF(employee_count, 0) AS avg_salary_v2
FROM department_stats;
 
-- Modulo for grouping/partitioning
SELECT 
    id,
    name,
    id % 10 AS shard_key,           -- Distribute across 10 shards
    id % 2 AS even_odd              -- 0 for even, 1 for odd
FROM records;
 
-- Percentage calculations
SELECT 
    department_name,
    department_budget,
    total_budget,
    ROUND(department_budget * 100.0 / total_budget, 2) AS budget_pct
FROM (
    SELECT 
        d.department_name,
        d.budget AS department_budget,
        (SELECT SUM(budget) FROM departments) AS total_budget
    FROM departments d
) AS budget_data;
 
-- Compound interest / growth calculations
SELECT 
    account_id,
    principal,
    annual_rate,
    years,
    ROUND(principal * POWER(1 + annual_rate / 100, years), 2) AS future_value
FROM investments;

Arithmetic Expression Behavior with NULL
Expression	Result	Explanation
`5 + NULL`	NULL	Any arithmetic with NULL is NULL
`NULL * 1000`	NULL	Multiplication with NULL is NULL
`10 / NULL`	NULL	Division by NULL is NULL (not error)
`COALESCE(NULL, 0) + 5`	5	COALESCE provides default
`5 + IFNULL(NULL, 0)`	5	IFNULL (MySQL) also works

Integer Division Varies by Database

In some databases (MySQL, PostgreSQL), 5/2 returns 2 (integer division). In others (SQL Server with certain settings, Oracle), it returns 2.5. For consistent decimal results, always cast at least one operand: CAST(5 AS DECIMAL) / 2 or 5.0 / 2.

String Expressions

String expressions manipulate text data—concatenating, extracting, transforming, and formatting character values. These are essential for creating display-ready output, parsing stored data, and implementing search functionality.

Key string operations:

Concatenation (combining strings)
Substring extraction (getting parts of strings)
Case transformation (UPPER, LOWER, proper case)
Trimming (removing whitespace)
Pattern matching (LIKE, REGEXP)
Length and position operations

string_expressions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- Concatenation (syntax varies by database)
SELECT 
    -- Standard SQL (PostgreSQL, Oracle)
    first_name || ' ' || last_name AS full_name_std,
    
    -- CONCAT function (MySQL, SQL Server, most databases)
    CONCAT(first_name, ' ', last_name) AS full_name_concat,
    
    -- CONCAT_WS: Concat with separator (MySQL, PostgreSQL)
    CONCAT_WS(' ', title, first_name, middle_name, last_name) AS formal_name
FROM employees;
 
-- Case transformation
SELECT 
    UPPER(first_name) AS upper_name,
    LOWER(email) AS lower_email,
    -- Proper case (varies by DB)
    CONCAT(UPPER(SUBSTRING(first_name, 1, 1)), 
           LOWER(SUBSTRING(first_name, 2))) AS proper_name,
    -- PostgreSQL INITCAP
    INITCAP(first_name || ' ' || last_name) AS proper_full_name
FROM employees;
 
-- Substring extraction
SELECT 
    phone,
    SUBSTRING(phone, 1, 3) AS area_code,        -- First 3 characters
    SUBSTRING(phone, 5, 3) AS exchange,          -- Characters 5-7
    SUBSTRING(phone, 9) AS line_number,          -- From position 9 to end
    LEFT(phone, 3) AS left_3,                    -- First 3 (shorthand)
    RIGHT(phone, 4) AS right_4                   -- Last 4
FROM contacts;
 
-- Trimming whitespace
SELECT 
    TRIM(name) AS trimmed,                       -- Both sides
    LTRIM(name) AS left_trimmed,                 -- Leading spaces
    RTRIM(name) AS right_trimmed,                -- Trailing spaces
    TRIM(BOTH '-' FROM code) AS trim_dashes      -- Trim specific character
FROM messy_data;
 
-- Length and position
SELECT 
    name,
    LENGTH(name) AS name_length,                 -- Character count
    CHAR_LENGTH(name) AS char_count,             -- Same, more explicit
    POSITION('@' IN email) AS at_position,       -- Find character position
    LOCATE('@', email) AS at_pos_mysql           -- MySQL syntax
FROM users;
 
-- String replacement and formatting
SELECT 
    phone,
    REPLACE(phone, '-', '') AS digits_only,      -- Remove dashes
    REPLACE(REPLACE(phone, '(', ''), ')', '') AS no_parens,
    LPAD(id::text, 8, '0') AS padded_id,         -- Left-pad with zeros
    RPAD(name, 20, '.') AS dotted_name           -- Right-pad with dots
FROM contacts;
 
-- Pattern-based operations (REGEXP)
SELECT 
    email,
    REGEXP_REPLACE(email, '@.*', '') AS username,  -- Extract before @
    CASE 
        WHEN email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}$'
        THEN 'Valid'
        ELSE 'Invalid'
    END AS email_validity
FROM users;

Concatenation Syntax by Database
Database	operator	CONCAT function	NULL behavior
PostgreSQL	`\|\|`	CONCAT()	`\|\|` returns NULL if any operand NULL; CONCAT ignores NULL
MySQL	N/A	CONCAT()	CONCAT returns NULL if any operand NULL
SQL Server	`+`	CONCAT()	`+` returns NULL; CONCAT ignores NULL
Oracle	`\|\|`	CONCAT() (2 args only)	`\|\|` treats NULL as empty string
SQLite	`\|\|`	N/A	`\|\|` returns NULL if any operand NULL

NULL-Safe Concatenation

To safely concatenate values that might be NULL, use CONCAT() with COALESCE: CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')). Or in SQL Server/PostgreSQL, CONCAT_WS(' ', first_name, middle_name, last_name) skips NULL values automatically.

Date and Time Expressions

Date and time expressions are essential for business applications—calculating ages, durations, periods, scheduling, and temporal analysis. Date handling varies significantly across database systems, making this one of the least portable areas of SQL.

Common date/time operations:

Current date/time retrieval
Date arithmetic (adding/subtracting intervals)
Component extraction (year, month, day, hour)
Date difference calculation
Formatting for display
Timezone conversion

datetime_expressions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
-- Current date/time (varies by database)
SELECT 
    CURRENT_DATE AS today,                    -- Standard SQL
    CURRENT_TIMESTAMP AS now,                 -- Standard SQL with time
    CURRENT_TIME AS current_time,             -- Just time portion
    NOW() AS now_func,                        -- MySQL, PostgreSQL
    GETDATE() AS sql_server_now,              -- SQL Server
    SYSDATE AS oracle_now                     -- Oracle
FROM ...;
 
-- Date arithmetic: Adding intervals
SELECT 
    order_date,
    -- PostgreSQL / Standard SQL
    order_date + INTERVAL '30 days' AS due_date,
    order_date + INTERVAL '1 month' AS next_month,
    order_date + INTERVAL '1 year' AS next_year,
    
    -- MySQL
    DATE_ADD(order_date, INTERVAL 30 DAY) AS due_date_mysql,
    
    -- SQL Server
    DATEADD(day, 30, order_date) AS due_date_sqlserver
FROM orders;
 
-- Date arithmetic: Calculating differences
SELECT 
    order_date,
    ship_date,
    -- PostgreSQL (returns interval)
    ship_date - order_date AS shipping_interval,
    -- Extract days
    EXTRACT(DAY FROM (ship_date - order_date)) AS shipping_days,
    
    -- MySQL
    DATEDIFF(ship_date, order_date) AS days_mysql,
    TIMESTAMPDIFF(HOUR, order_date, ship_date) AS hours_mysql,
    
    -- SQL Server
    DATEDIFF(day, order_date, ship_date) AS days_sqlserver
FROM orders;
 
-- Component extraction
SELECT 
    order_date,
    -- Standard SQL EXTRACT
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    EXTRACT(DAY FROM order_date) AS order_day,
    EXTRACT(DOW FROM order_date) AS day_of_week,      -- 0=Sunday
    EXTRACT(QUARTER FROM order_date) AS quarter,
    
    -- MySQL functions  
    YEAR(order_date) AS year_mysql,
    MONTH(order_date) AS month_mysql,
    DAYNAME(order_date) AS day_name_mysql,
    
    -- SQL Server functions
    YEAR(order_date) AS year_ss,
    DATENAME(weekday, order_date) AS day_name_ss
FROM orders;
 
-- Age/duration calculations
SELECT 
    employee_name,
    hire_date,
    -- PostgreSQL AGE function
    AGE(CURRENT_DATE, hire_date) AS tenure,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date)) AS years_employed,
    
    -- General approach: Calculate in days then convert
    (CURRENT_DATE - hire_date) / 365 AS approx_years,
    
    -- Birth date to age
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) AS age
FROM employees;
 
-- Date formatting for display
SELECT 
    order_date,
    -- PostgreSQL TO_CHAR
    TO_CHAR(order_date, 'YYYY-MM-DD') AS iso_format,
    TO_CHAR(order_date, 'Mon DD, YYYY') AS display_format,
    TO_CHAR(order_date, 'Day, Month DD, YYYY') AS full_format,
    
    -- MySQL DATE_FORMAT
    DATE_FORMAT(order_date, '%Y-%m-%d') AS iso_mysql,
    DATE_FORMAT(order_date, '%M %d, %Y') AS display_mysql,
    
    -- SQL Server FORMAT
    FORMAT(order_date, 'yyyy-MM-dd') AS iso_ss,
    FORMAT(order_date, 'MMMM dd, yyyy') AS display_ss
FROM orders;

Date Functions Are Not Portable

Date/time functions vary more than any other SQL feature across databases. DATEDIFF(a, b) in MySQL is different from DATEDIFF(part, a, b) in SQL Server. Always test date logic on your specific database, and consider abstracting date operations in stored procedures or application code for portability.

Conditional Expressions (CASE)

The CASE expression is SQL's primary conditional construct—an inline if-then-else that returns different values based on conditions. It is one of the most powerful and commonly used expressions, enabling dynamic value computation within queries.

Two CASE syntax forms:

Searched CASE: Evaluates arbitrary boolean conditions
Simple CASE: Compares one expression against multiple values

case_expressions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
-- Searched CASE: Arbitrary conditions
SELECT 
    employee_name,
    salary,
    CASE 
        WHEN salary >= 100000 THEN 'Executive'
        WHEN salary >= 75000 THEN 'Senior'
        WHEN salary >= 50000 THEN 'Mid-Level'
        WHEN salary >= 30000 THEN 'Junior'
        ELSE 'Entry Level'
    END AS salary_band
FROM employees;
 
-- Simple CASE: Value comparison
SELECT 
    order_id,
    status_code,
    CASE status_code
        WHEN 'P' THEN 'Pending'
        WHEN 'A' THEN 'Approved'
        WHEN 'S' THEN 'Shipped'
        WHEN 'D' THEN 'Delivered'
        WHEN 'C' THEN 'Cancelled'
        ELSE 'Unknown'
    END AS status_description
FROM orders;
 
-- CASE with NULL handling
SELECT 
    employee_name,
    manager_id,
    CASE 
        WHEN manager_id IS NULL THEN 'Top-Level Executive'
        ELSE 'Has Manager'
    END AS management_status
FROM employees;
 
-- Nested CASE (use sparingly - can become unreadable)
SELECT 
    product_name,
    category,
    price,
    CASE category
        WHEN 'Electronics'THEN 
            CASE 
                WHEN price > 1000 THEN 'Premium Electronics'
                ELSE 'Standard Electronics'
            END
        WHEN 'Clothing' THEN 
            CASE 
                WHEN price > 100 THEN 'Designer'
                ELSE 'Standard'
            END
        ELSE 'Other'
    END AS product_tier
FROM products;
 
-- CASE for conditional aggregation
SELECT 
    department_id,
    COUNT(*) AS total_employees,
    COUNT(CASE WHEN salary >= 75000 THEN 1 END) AS high_earners,
    COUNT(CASE WHEN salary < 50000 THEN 1 END) AS low_earners,
    SUM(CASE WHEN gender = 'F' THEN salary ELSE 0 END) AS female_salary_total,
    SUM(CASE WHEN gender = 'M' THEN salary ELSE 0 END) AS male_salary_total
FROM employees
GROUP BY department_id;
 
-- CASE for pivot-style transformation
SELECT 
    product_id,
    SUM(CASE WHEN quarter = 1 THEN sales ELSE 0 END) AS q1_sales,
    SUM(CASE WHEN quarter = 2 THEN sales ELSE 0 END) AS q2_sales,
    SUM(CASE WHEN quarter = 3 THEN sales ELSE 0 END) AS q3_sales,
    SUM(CASE WHEN quarter = 4 THEN sales ELSE 0 END) AS q4_sales
FROM quarterly_sales
GROUP BY product_id;
 
-- CASE for custom sort order
SELECT 
    task_name,
    priority,
    CASE priority
        WHEN 'Critical' THEN 1
        WHEN 'High' THEN 2
        WHEN 'Medium' THEN 3
        WHEN 'Low' THEN 4
        ELSE 5
    END AS sort_order
FROM tasks
ORDER BY sort_order;

CASE Expression Rules

•Short-circuit evaluation — Conditions are evaluated in order; first TRUE match wins.
•ELSE is optional but recommended — Without ELSE, unmatched conditions return NULL.
•Type consistency — All result expressions should return compatible types.
•END is required — Every CASE must close with END.
•Can appear anywhere — SELECT, WHERE, ORDER BY, GROUP BY, even in aggregate functions.
•Nest with caution — Deeply nested CASE becomes unreadable; consider COALESCE or views.

CASE vs Application Logic

CASE expressions often replace application-level if-then logic. Computing values in the database reduces data transfer and application complexity. Consider CASE when you're doing simple mappings or categorizations; move complex business logic to stored procedures or application code.

NULL Handling Expressions

NULL represents the absence of a value, and handling it correctly is critical for accurate query results. SQL provides several expressions specifically designed for NULL handling.

Key NULL-handling expressions:

Expression	Purpose	Standard?
COALESCE	Return first non-NULL	Yes
NULLIF	Return NULL if equal	Yes
IFNULL/NVL	Two-argument COALESCE	No (DB-specific)
ISNULL	Replace NULL (SQL Server)	No

null_handling.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
-- COALESCE: First non-NULL value (standard SQL)
SELECT 
    customer_name,
    COALESCE(phone_mobile, phone_home, phone_work, 'No phone') AS contact_phone,
    COALESCE(preferred_name, first_name) AS display_name,
    COALESCE(discount_pct, 0) AS effective_discount
FROM customers;
 
-- NULLIF: Return NULL if values equal
SELECT 
    product_name,
    price,
    discount_price,
    -- Avoid division by zero
    price / NULLIF(discount_price, 0) AS price_ratio,
    -- Convert empty strings to NULL for consistency
    NULLIF(TRIM(description), '') AS clean_description,
    -- Convert sentinel values to NULL
    NULLIF(quantity, -1) AS actual_quantity  -- -1 means "unknown"
FROM products;
 
-- IFNULL (MySQL) / NVL (Oracle) / ISNULL (SQL Server)
-- Two-argument shorthand for COALESCE
SELECT 
    order_id,
    -- MySQL
    IFNULL(shipping_fee, 0) AS shipping_mysql,
    -- Oracle
    NVL(shipping_fee, 0) AS shipping_oracle,
    -- SQL Server
    ISNULL(shipping_fee, 0) AS shipping_sqlserver
FROM orders;
 
-- Combining NULL handlers
SELECT 
    employee_name,
    salary,
    commission_pct,
    -- Calculate total compensation
    salary + (salary * COALESCE(commission_pct, 0) / 100) AS total_comp,
    -- Alternative using CASE
    salary + CASE 
        WHEN commission_pct IS NOT NULL THEN salary * commission_pct / 100
        ELSE 0 
    END AS total_comp_case
FROM employees;
 
-- NVL2 (Oracle): Different values for NULL vs non-NULL
SELECT 
    employee_name,
    commission_pct,
    -- If commission exists, show it; otherwise show 'N/A'
    NVL2(commission_pct, TO_CHAR(commission_pct) || '%', 'N/A') AS commission_display
FROM employees;
 
-- Simulating NVL2 in other databases
SELECT 
    employee_name,
    commission_pct,
    CASE 
        WHEN commission_pct IS NOT NULL THEN CAST(commission_pct AS VARCHAR) || '%'
        ELSE 'N/A'
    END AS commission_display
FROM employees;
 
-- NULL-safe comparison (comparing with potential NULLs)
SELECT *
FROM table1 t1
JOIN table2 t2 ON COALESCE(t1.key, '') = COALESCE(t2.key, '');
-- Or better: use IS NOT DISTINCT FROM (PostgreSQL/standard)
SELECT *
FROM table1 t1
JOIN table2 t2 ON t1.key IS NOT DISTINCT FROM t2.key;

NULL Function Comparison
Standard	MySQL	Oracle	SQL Server	PostgreSQL
COALESCE(a,b,...)	COALESCE / IFNULL	COALESCE / NVL	COALESCE / ISNULL	COALESCE
NULLIF(a,b)	NULLIF	NULLIF	NULLIF	NULLIF
N/A	N/A	NVL2(a,b,c)	N/A	N/A
CASE WHEN	CASE WHEN	CASE WHEN	CASE WHEN	CASE WHEN

COALESCE vs ISNULL/IFNULL

While ISNULL (SQL Server) and IFNULL (MySQL) are convenient, COALESCE is standard SQL that works everywhere and accepts multiple arguments. Prefer COALESCE for portability and flexibility.

Scalar Subqueries in SELECT

A scalar subquery is a subquery that returns exactly one row with exactly one column—a single value. When placed in the SELECT clause, it computes a value for each row of the outer query, enabling powerful correlated computations.

Key characteristics:

Must return exactly one value (one row, one column)
Can be correlated (reference outer query) or non-correlated
Evaluated for each row of the outer query (can be expensive)
Returns NULL if subquery returns no rows
Errors if subquery returns multiple rows

scalar_subqueries.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
-- Non-correlated scalar subquery (same value for all rows)
SELECT 
    employee_name,
    salary,
    (SELECT AVG(salary) FROM employees) AS company_avg_salary,
    salary - (SELECT AVG(salary) FROM employees) AS vs_company_avg
FROM employees;
 
-- Correlated scalar subquery (different for each row)
SELECT 
    e.employee_name,
    e.salary,
    e.department_id,
    (SELECT AVG(e2.salary) 
     FROM employees e2 
     WHERE e2.department_id = e.department_id) AS dept_avg_salary,
    e.salary - (SELECT AVG(e2.salary) 
                FROM employees e2 
                WHERE e2.department_id = e.department_id) AS vs_dept_avg
FROM employees e;
 
-- Looking up related values
SELECT 
    o.order_id,
    o.customer_id,
    (SELECT c.customer_name 
     FROM customers c 
     WHERE c.customer_id = o.customer_id) AS customer_name,
    (SELECT COUNT(*) 
     FROM order_items oi 
     WHERE oi.order_id = o.order_id) AS item_count
FROM orders o;
 
-- Ranking/comparison subqueries
SELECT 
    e.employee_name,
    e.salary,
    (SELECT COUNT(*) + 1 
     FROM employees e2 
     WHERE e2.salary > e.salary) AS salary_rank
FROM employees e
ORDER BY salary_rank;
 
-- Conditional subquery result
SELECT 
    d.department_name,
    (SELECT MAX(salary) FROM employees e WHERE e.department_id = d.department_id) AS max_salary,
    COALESCE(
        (SELECT e.employee_name 
         FROM employees e 
         WHERE e.department_id = d.department_id 
         ORDER BY e.salary DESC 
         LIMIT 1),
        'No employees'
    ) AS top_earner_name
FROM departments d;
 
-- Why JOINs are often better than scalar subqueries
-- SLOWER (scalar subquery per row):
SELECT 
    o.order_id,
    (SELECT c.name FROM customers c WHERE c.id = o.customer_id) AS customer_name
FROM orders o;
 
-- FASTER (single join operation):
SELECT 
    o.order_id,
    c.name AS customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

Scalar Subquery Performance

Correlated scalar subqueries execute once per row in the outer query. For 10,000 rows, that's 10,000 subquery executions. Modern optimizers can sometimes decorrelate these into more efficient joins, but not always. Prefer JOINs when possible, especially for large datasets.

When to Use Scalar Subqueries

•Aggregate comparisons — Comparing each row against an aggregate (vs average, vs max).
•Lookup when JOIN is complex — Sometimes clearer than outer/cross joins with filtering.
•Optional related data — When the related value might not exist (NULL acceptable).
•Inline calculation — Computing a derived value that's awkward to express as a join.
•Small datasets — Performance penalty is minimal for small outer result sets.

Summary: Expressions in SELECT

Expressions transform SELECT from a simple column picker into a powerful computation engine. Understanding the full range of expression types enables you to perform sophisticated data transformations directly in your queries, reducing application complexity and improving performance.

Let's consolidate the key points:

Key Takeaways

•Expressions evaluate per row — Each expression computes its value for every row in the result set.
•Arithmetic respects NULL — Any arithmetic with NULL returns NULL; use COALESCE for defaults.
•String functions vary by DB — Concatenation, substring, and formatting syntax differs significantly across databases.
•Date handling is least portable — Date arithmetic and function syntax varies most; abstract if portability needed.
•CASE is powerful and flexible — Use for conditional logic, categorization, pivoting, and custom sorting.
•COALESCE handles NULL — Standard, multi-argument NULL handling; prefer over DB-specific functions.
•Scalar subqueries enable correlation — Powerful for aggregation comparisons; watch performance on large datasets.
•Always alias expressions — Computed columns need clear, stable names for application code.

Module Complete:

With this page, you've completed Module 1: SELECT Basics. You now have comprehensive knowledge of the SELECT statement's foundation—from clause syntax through column selection, asterisk behavior, and expression computation. You're equipped to write precise, efficient queries that retrieve exactly the data you need in the form you need it.

Module Complete

Congratulations! You've mastered SELECT basics—the foundation of all SQL data retrieval. You understand SELECT and FROM clauses, column selection strategies, asterisk usage, and expression computation. The next module on WHERE clauses will teach you how to filter the data you've learned to retrieve.

5 / 5

Loading learning content...

Database Management SystemsSELECT Basics

SELECT Basics: Mastering Data Retrieval

LevelBeginner

Duration60 mins

TopicSELECT Basics

5 / 5

Expressions: Computing Values in SELECT

Beyond Simple Retrieval

Expressions in SELECT allow you to:

Perform arithmetic calculations (totals, percentages, differences)
Manipulate strings (concatenation, extraction, formatting)
Transform dates and times (age calculation, period extraction, formatting)
Apply conditional logic (CASE statements, COALESCE, NULLIF)
Call built-in and user-defined functions
Embed scalar subqueries for correlated computations

This computational capability transforms SELECT from a retrieval mechanism into a powerful data processing tool, often eliminating the need for application-level post-processing.

What You Will Learn

Expression Fundamentals

Expression components:

Literals: Constant values (42, 'hello', DATE '2024-01-15')
Column references: Placeholders for row values (first_name, e.salary)
Operators: Combine values (+, -, *, /, ||, AND, OR)
Functions: Transform values (UPPER, ROUND, COALESCE)
Subqueries: Nested queries returning scalar values
CASE expressions: Conditional value selection

Evaluation model:

Expressions in SELECT are evaluated once per row in the result set. If your query processes 1000 rows, each expression computes its value 1000 times (once per row).

expression_fundamentals.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- Literals: Constant values in every row
SELECT 
    'Employee' AS record_type,     -- String literal
    42 AS answer,                   -- Numeric literal
    TRUE AS is_active,              -- Boolean literal
    DATE '2024-01-01' AS start_date -- Date literal
FROM employees;
-- Every row contains these same constant values
 
-- Column references: Different for each row
SELECT 
    first_name,        -- Simple column reference
    e.last_name,       -- Qualified column reference
    salary             -- Another column reference
FROM employees AS e;
 
-- Operator expressions: Combining values
SELECT 
    first_name || ' ' || last_name AS full_name,    -- Concatenation
    salary * 12 AS annual_salary,                    -- Multiplication
    salary + COALESCE(commission, 0) AS total_comp,  -- Addition
    hire_date + INTERVAL '1 year' AS anniversary     -- Date arithmetic
FROM employees;
 
-- Function expressions: Transforming values
SELECT 
    UPPER(first_name) AS upper_name,
    LENGTH(last_name) AS name_length,
    ROUND(salary / 12, 2) AS monthly_salary,
    EXTRACT(YEAR FROM hire_date) AS hire_year
FROM employees;
 
-- Compound expressions: Combining multiple operations
SELECT 
    UPPER(SUBSTRING(first_name, 1, 1)) || LOWER(SUBSTRING(first_name, 2)) AS proper_name,
    ROUND((salary * 12) * (1 + COALESCE(commission_pct, 0) / 100), 2) AS annual_total
FROM employees;

Every Expression Needs a Name

Arithmetic Expressions

Standard arithmetic operators:

Operator	Operation	Example
+	Addition	`price + tax`
-	Subtraction	`total - discount`
*	Multiplication	`quantity * price`
/	Division	`total / count`
% or MOD	Modulo (remainder)	`id % 10`

arithmetic_expressions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- Basic arithmetic
SELECT 
    product_name,
    price,
    quantity,
    price * quantity AS line_total
FROM order_items;
 
-- Multiple operations with precedence
SELECT 
    product_name,
    price,
    quantity,
    discount_pct,
    price * quantity AS subtotal,
    price * quantity * (1 - discount_pct / 100) AS discounted_total,
    price * quantity * (1 - discount_pct / 100) * 1.08 AS total_with_tax
FROM order_items;
 
-- Division considerations (integer vs decimal)
SELECT 
    5 / 2 AS integer_division,      -- Result depends on database
    5.0 / 2 AS decimal_division,    -- 2.5 (explicit decimal)
    CAST(5 AS DECIMAL) / 2 AS cast_division  -- 2.5 (cast to decimal)
FROM dual;  -- Use appropriate single-row source for your DB
 
-- Avoiding division by zero
SELECT 
    department_id,
    total_salary,
    employee_count,
    CASE 
        WHEN employee_count = 0 THEN NULL
        ELSE total_salary / employee_count 
    END AS avg_salary,
    -- Or using NULLIF
    total_salary / NULLIF(employee_count, 0) AS avg_salary_v2
FROM department_stats;
 
-- Modulo for grouping/partitioning
SELECT 
    id,
    name,
    id % 10 AS shard_key,           -- Distribute across 10 shards
    id % 2 AS even_odd              -- 0 for even, 1 for odd
FROM records;
 
-- Percentage calculations
SELECT 
    department_name,
    department_budget,
    total_budget,
    ROUND(department_budget * 100.0 / total_budget, 2) AS budget_pct
FROM (
    SELECT 
        d.department_name,
        d.budget AS department_budget,
        (SELECT SUM(budget) FROM departments) AS total_budget
    FROM departments d
) AS budget_data;
 
-- Compound interest / growth calculations
SELECT 
    account_id,
    principal,
    annual_rate,
    years,
    ROUND(principal * POWER(1 + annual_rate / 100, years), 2) AS future_value
FROM investments;

Arithmetic Expression Behavior with NULL
Expression	Result	Explanation
`5 + NULL`	NULL	Any arithmetic with NULL is NULL
`NULL * 1000`	NULL	Multiplication with NULL is NULL
`10 / NULL`	NULL	Division by NULL is NULL (not error)
`COALESCE(NULL, 0) + 5`	5	COALESCE provides default
`5 + IFNULL(NULL, 0)`	5	IFNULL (MySQL) also works

Integer Division Varies by Database

String Expressions

Key string operations:

Concatenation (combining strings)
Substring extraction (getting parts of strings)
Case transformation (UPPER, LOWER, proper case)
Trimming (removing whitespace)
Pattern matching (LIKE, REGEXP)
Length and position operations

string_expressions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- Concatenation (syntax varies by database)
SELECT 
    -- Standard SQL (PostgreSQL, Oracle)
    first_name || ' ' || last_name AS full_name_std,
    
    -- CONCAT function (MySQL, SQL Server, most databases)
    CONCAT(first_name, ' ', last_name) AS full_name_concat,
    
    -- CONCAT_WS: Concat with separator (MySQL, PostgreSQL)
    CONCAT_WS(' ', title, first_name, middle_name, last_name) AS formal_name
FROM employees;
 
-- Case transformation
SELECT 
    UPPER(first_name) AS upper_name,
    LOWER(email) AS lower_email,
    -- Proper case (varies by DB)
    CONCAT(UPPER(SUBSTRING(first_name, 1, 1)), 
           LOWER(SUBSTRING(first_name, 2))) AS proper_name,
    -- PostgreSQL INITCAP
    INITCAP(first_name || ' ' || last_name) AS proper_full_name
FROM employees;
 
-- Substring extraction
SELECT 
    phone,
    SUBSTRING(phone, 1, 3) AS area_code,        -- First 3 characters
    SUBSTRING(phone, 5, 3) AS exchange,          -- Characters 5-7
    SUBSTRING(phone, 9) AS line_number,          -- From position 9 to end
    LEFT(phone, 3) AS left_3,                    -- First 3 (shorthand)
    RIGHT(phone, 4) AS right_4                   -- Last 4
FROM contacts;
 
-- Trimming whitespace
SELECT 
    TRIM(name) AS trimmed,                       -- Both sides
    LTRIM(name) AS left_trimmed,                 -- Leading spaces
    RTRIM(name) AS right_trimmed,                -- Trailing spaces
    TRIM(BOTH '-' FROM code) AS trim_dashes      -- Trim specific character
FROM messy_data;
 
-- Length and position
SELECT 
    name,
    LENGTH(name) AS name_length,                 -- Character count
    CHAR_LENGTH(name) AS char_count,             -- Same, more explicit
    POSITION('@' IN email) AS at_position,       -- Find character position
    LOCATE('@', email) AS at_pos_mysql           -- MySQL syntax
FROM users;
 
-- String replacement and formatting
SELECT 
    phone,
    REPLACE(phone, '-', '') AS digits_only,      -- Remove dashes
    REPLACE(REPLACE(phone, '(', ''), ')', '') AS no_parens,
    LPAD(id::text, 8, '0') AS padded_id,         -- Left-pad with zeros
    RPAD(name, 20, '.') AS dotted_name           -- Right-pad with dots
FROM contacts;
 
-- Pattern-based operations (REGEXP)
SELECT 
    email,
    REGEXP_REPLACE(email, '@.*', '') AS username,  -- Extract before @
    CASE 
        WHEN email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}$'
        THEN 'Valid'
        ELSE 'Invalid'
    END AS email_validity
FROM users;

Concatenation Syntax by Database
Database	operator	CONCAT function	NULL behavior
PostgreSQL	`\|\|`	CONCAT()	`\|\|` returns NULL if any operand NULL; CONCAT ignores NULL
MySQL	N/A	CONCAT()	CONCAT returns NULL if any operand NULL
SQL Server	`+`	CONCAT()	`+` returns NULL; CONCAT ignores NULL
Oracle	`\|\|`	CONCAT() (2 args only)	`\|\|` treats NULL as empty string
SQLite	`\|\|`	N/A	`\|\|` returns NULL if any operand NULL

NULL-Safe Concatenation

Date and Time Expressions

Common date/time operations:

Current date/time retrieval
Date arithmetic (adding/subtracting intervals)
Component extraction (year, month, day, hour)
Date difference calculation
Formatting for display
Timezone conversion

datetime_expressions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
-- Current date/time (varies by database)
SELECT 
    CURRENT_DATE AS today,                    -- Standard SQL
    CURRENT_TIMESTAMP AS now,                 -- Standard SQL with time
    CURRENT_TIME AS current_time,             -- Just time portion
    NOW() AS now_func,                        -- MySQL, PostgreSQL
    GETDATE() AS sql_server_now,              -- SQL Server
    SYSDATE AS oracle_now                     -- Oracle
FROM ...;
 
-- Date arithmetic: Adding intervals
SELECT 
    order_date,
    -- PostgreSQL / Standard SQL
    order_date + INTERVAL '30 days' AS due_date,
    order_date + INTERVAL '1 month' AS next_month,
    order_date + INTERVAL '1 year' AS next_year,
    
    -- MySQL
    DATE_ADD(order_date, INTERVAL 30 DAY) AS due_date_mysql,
    
    -- SQL Server
    DATEADD(day, 30, order_date) AS due_date_sqlserver
FROM orders;
 
-- Date arithmetic: Calculating differences
SELECT 
    order_date,
    ship_date,
    -- PostgreSQL (returns interval)
    ship_date - order_date AS shipping_interval,
    -- Extract days
    EXTRACT(DAY FROM (ship_date - order_date)) AS shipping_days,
    
    -- MySQL
    DATEDIFF(ship_date, order_date) AS days_mysql,
    TIMESTAMPDIFF(HOUR, order_date, ship_date) AS hours_mysql,
    
    -- SQL Server
    DATEDIFF(day, order_date, ship_date) AS days_sqlserver
FROM orders;
 
-- Component extraction
SELECT 
    order_date,
    -- Standard SQL EXTRACT
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    EXTRACT(DAY FROM order_date) AS order_day,
    EXTRACT(DOW FROM order_date) AS day_of_week,      -- 0=Sunday
    EXTRACT(QUARTER FROM order_date) AS quarter,
    
    -- MySQL functions  
    YEAR(order_date) AS year_mysql,
    MONTH(order_date) AS month_mysql,
    DAYNAME(order_date) AS day_name_mysql,
    
    -- SQL Server functions
    YEAR(order_date) AS year_ss,
    DATENAME(weekday, order_date) AS day_name_ss
FROM orders;
 
-- Age/duration calculations
SELECT 
    employee_name,
    hire_date,
    -- PostgreSQL AGE function
    AGE(CURRENT_DATE, hire_date) AS tenure,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date)) AS years_employed,
    
    -- General approach: Calculate in days then convert
    (CURRENT_DATE - hire_date) / 365 AS approx_years,
    
    -- Birth date to age
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) AS age
FROM employees;
 
-- Date formatting for display
SELECT 
    order_date,
    -- PostgreSQL TO_CHAR
    TO_CHAR(order_date, 'YYYY-MM-DD') AS iso_format,
    TO_CHAR(order_date, 'Mon DD, YYYY') AS display_format,
    TO_CHAR(order_date, 'Day, Month DD, YYYY') AS full_format,
    
    -- MySQL DATE_FORMAT
    DATE_FORMAT(order_date, '%Y-%m-%d') AS iso_mysql,
    DATE_FORMAT(order_date, '%M %d, %Y') AS display_mysql,
    
    -- SQL Server FORMAT
    FORMAT(order_date, 'yyyy-MM-dd') AS iso_ss,
    FORMAT(order_date, 'MMMM dd, yyyy') AS display_ss
FROM orders;

Date Functions Are Not Portable

Conditional Expressions (CASE)

Two CASE syntax forms:

Searched CASE: Evaluates arbitrary boolean conditions
Simple CASE: Compares one expression against multiple values

case_expressions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
-- Searched CASE: Arbitrary conditions
SELECT 
    employee_name,
    salary,
    CASE 
        WHEN salary >= 100000 THEN 'Executive'
        WHEN salary >= 75000 THEN 'Senior'
        WHEN salary >= 50000 THEN 'Mid-Level'
        WHEN salary >= 30000 THEN 'Junior'
        ELSE 'Entry Level'
    END AS salary_band
FROM employees;
 
-- Simple CASE: Value comparison
SELECT 
    order_id,
    status_code,
    CASE status_code
        WHEN 'P' THEN 'Pending'
        WHEN 'A' THEN 'Approved'
        WHEN 'S' THEN 'Shipped'
        WHEN 'D' THEN 'Delivered'
        WHEN 'C' THEN 'Cancelled'
        ELSE 'Unknown'
    END AS status_description
FROM orders;
 
-- CASE with NULL handling
SELECT 
    employee_name,
    manager_id,
    CASE 
        WHEN manager_id IS NULL THEN 'Top-Level Executive'
        ELSE 'Has Manager'
    END AS management_status
FROM employees;
 
-- Nested CASE (use sparingly - can become unreadable)
SELECT 
    product_name,
    category,
    price,
    CASE category
        WHEN 'Electronics'THEN 
            CASE 
                WHEN price > 1000 THEN 'Premium Electronics'
                ELSE 'Standard Electronics'
            END
        WHEN 'Clothing' THEN 
            CASE 
                WHEN price > 100 THEN 'Designer'
                ELSE 'Standard'
            END
        ELSE 'Other'
    END AS product_tier
FROM products;
 
-- CASE for conditional aggregation
SELECT 
    department_id,
    COUNT(*) AS total_employees,
    COUNT(CASE WHEN salary >= 75000 THEN 1 END) AS high_earners,
    COUNT(CASE WHEN salary < 50000 THEN 1 END) AS low_earners,
    SUM(CASE WHEN gender = 'F' THEN salary ELSE 0 END) AS female_salary_total,
    SUM(CASE WHEN gender = 'M' THEN salary ELSE 0 END) AS male_salary_total
FROM employees
GROUP BY department_id;
 
-- CASE for pivot-style transformation
SELECT 
    product_id,
    SUM(CASE WHEN quarter = 1 THEN sales ELSE 0 END) AS q1_sales,
    SUM(CASE WHEN quarter = 2 THEN sales ELSE 0 END) AS q2_sales,
    SUM(CASE WHEN quarter = 3 THEN sales ELSE 0 END) AS q3_sales,
    SUM(CASE WHEN quarter = 4 THEN sales ELSE 0 END) AS q4_sales
FROM quarterly_sales
GROUP BY product_id;
 
-- CASE for custom sort order
SELECT 
    task_name,
    priority,
    CASE priority
        WHEN 'Critical' THEN 1
        WHEN 'High' THEN 2
        WHEN 'Medium' THEN 3
        WHEN 'Low' THEN 4
        ELSE 5
    END AS sort_order
FROM tasks
ORDER BY sort_order;

CASE Expression Rules

•Short-circuit evaluation — Conditions are evaluated in order; first TRUE match wins.
•ELSE is optional but recommended — Without ELSE, unmatched conditions return NULL.
•Type consistency — All result expressions should return compatible types.
•END is required — Every CASE must close with END.
•Can appear anywhere — SELECT, WHERE, ORDER BY, GROUP BY, even in aggregate functions.
•Nest with caution — Deeply nested CASE becomes unreadable; consider COALESCE or views.

CASE vs Application Logic

NULL Handling Expressions

NULL represents the absence of a value, and handling it correctly is critical for accurate query results. SQL provides several expressions specifically designed for NULL handling.

Key NULL-handling expressions:

Expression	Purpose	Standard?
COALESCE	Return first non-NULL	Yes
NULLIF	Return NULL if equal	Yes
IFNULL/NVL	Two-argument COALESCE	No (DB-specific)
ISNULL	Replace NULL (SQL Server)	No

null_handling.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
-- COALESCE: First non-NULL value (standard SQL)
SELECT 
    customer_name,
    COALESCE(phone_mobile, phone_home, phone_work, 'No phone') AS contact_phone,
    COALESCE(preferred_name, first_name) AS display_name,
    COALESCE(discount_pct, 0) AS effective_discount
FROM customers;
 
-- NULLIF: Return NULL if values equal
SELECT 
    product_name,
    price,
    discount_price,
    -- Avoid division by zero
    price / NULLIF(discount_price, 0) AS price_ratio,
    -- Convert empty strings to NULL for consistency
    NULLIF(TRIM(description), '') AS clean_description,
    -- Convert sentinel values to NULL
    NULLIF(quantity, -1) AS actual_quantity  -- -1 means "unknown"
FROM products;
 
-- IFNULL (MySQL) / NVL (Oracle) / ISNULL (SQL Server)
-- Two-argument shorthand for COALESCE
SELECT 
    order_id,
    -- MySQL
    IFNULL(shipping_fee, 0) AS shipping_mysql,
    -- Oracle
    NVL(shipping_fee, 0) AS shipping_oracle,
    -- SQL Server
    ISNULL(shipping_fee, 0) AS shipping_sqlserver
FROM orders;
 
-- Combining NULL handlers
SELECT 
    employee_name,
    salary,
    commission_pct,
    -- Calculate total compensation
    salary + (salary * COALESCE(commission_pct, 0) / 100) AS total_comp,
    -- Alternative using CASE
    salary + CASE 
        WHEN commission_pct IS NOT NULL THEN salary * commission_pct / 100
        ELSE 0 
    END AS total_comp_case
FROM employees;
 
-- NVL2 (Oracle): Different values for NULL vs non-NULL
SELECT 
    employee_name,
    commission_pct,
    -- If commission exists, show it; otherwise show 'N/A'
    NVL2(commission_pct, TO_CHAR(commission_pct) || '%', 'N/A') AS commission_display
FROM employees;
 
-- Simulating NVL2 in other databases
SELECT 
    employee_name,
    commission_pct,
    CASE 
        WHEN commission_pct IS NOT NULL THEN CAST(commission_pct AS VARCHAR) || '%'
        ELSE 'N/A'
    END AS commission_display
FROM employees;
 
-- NULL-safe comparison (comparing with potential NULLs)
SELECT *
FROM table1 t1
JOIN table2 t2 ON COALESCE(t1.key, '') = COALESCE(t2.key, '');
-- Or better: use IS NOT DISTINCT FROM (PostgreSQL/standard)
SELECT *
FROM table1 t1
JOIN table2 t2 ON t1.key IS NOT DISTINCT FROM t2.key;

NULL Function Comparison
Standard	MySQL	Oracle	SQL Server	PostgreSQL
COALESCE(a,b,...)	COALESCE / IFNULL	COALESCE / NVL	COALESCE / ISNULL	COALESCE
NULLIF(a,b)	NULLIF	NULLIF	NULLIF	NULLIF
N/A	N/A	NVL2(a,b,c)	N/A	N/A
CASE WHEN	CASE WHEN	CASE WHEN	CASE WHEN	CASE WHEN

COALESCE vs ISNULL/IFNULL

While ISNULL (SQL Server) and IFNULL (MySQL) are convenient, COALESCE is standard SQL that works everywhere and accepts multiple arguments. Prefer COALESCE for portability and flexibility.

Scalar Subqueries in SELECT

Key characteristics:

Must return exactly one value (one row, one column)
Can be correlated (reference outer query) or non-correlated
Evaluated for each row of the outer query (can be expensive)
Returns NULL if subquery returns no rows
Errors if subquery returns multiple rows

scalar_subqueries.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
-- Non-correlated scalar subquery (same value for all rows)
SELECT 
    employee_name,
    salary,
    (SELECT AVG(salary) FROM employees) AS company_avg_salary,
    salary - (SELECT AVG(salary) FROM employees) AS vs_company_avg
FROM employees;
 
-- Correlated scalar subquery (different for each row)
SELECT 
    e.employee_name,
    e.salary,
    e.department_id,
    (SELECT AVG(e2.salary) 
     FROM employees e2 
     WHERE e2.department_id = e.department_id) AS dept_avg_salary,
    e.salary - (SELECT AVG(e2.salary) 
                FROM employees e2 
                WHERE e2.department_id = e.department_id) AS vs_dept_avg
FROM employees e;
 
-- Looking up related values
SELECT 
    o.order_id,
    o.customer_id,
    (SELECT c.customer_name 
     FROM customers c 
     WHERE c.customer_id = o.customer_id) AS customer_name,
    (SELECT COUNT(*) 
     FROM order_items oi 
     WHERE oi.order_id = o.order_id) AS item_count
FROM orders o;
 
-- Ranking/comparison subqueries
SELECT 
    e.employee_name,
    e.salary,
    (SELECT COUNT(*) + 1 
     FROM employees e2 
     WHERE e2.salary > e.salary) AS salary_rank
FROM employees e
ORDER BY salary_rank;
 
-- Conditional subquery result
SELECT 
    d.department_name,
    (SELECT MAX(salary) FROM employees e WHERE e.department_id = d.department_id) AS max_salary,
    COALESCE(
        (SELECT e.employee_name 
         FROM employees e 
         WHERE e.department_id = d.department_id 
         ORDER BY e.salary DESC 
         LIMIT 1),
        'No employees'
    ) AS top_earner_name
FROM departments d;
 
-- Why JOINs are often better than scalar subqueries
-- SLOWER (scalar subquery per row):
SELECT 
    o.order_id,
    (SELECT c.name FROM customers c WHERE c.id = o.customer_id) AS customer_name
FROM orders o;
 
-- FASTER (single join operation):
SELECT 
    o.order_id,
    c.name AS customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

Scalar Subquery Performance

When to Use Scalar Subqueries

•Aggregate comparisons — Comparing each row against an aggregate (vs average, vs max).
•Lookup when JOIN is complex — Sometimes clearer than outer/cross joins with filtering.
•Optional related data — When the related value might not exist (NULL acceptable).
•Inline calculation — Computing a derived value that's awkward to express as a join.
•Small datasets — Performance penalty is minimal for small outer result sets.

Summary: Expressions in SELECT

Let's consolidate the key points:

Key Takeaways

•Expressions evaluate per row — Each expression computes its value for every row in the result set.
•Arithmetic respects NULL — Any arithmetic with NULL returns NULL; use COALESCE for defaults.
•String functions vary by DB — Concatenation, substring, and formatting syntax differs significantly across databases.
•Date handling is least portable — Date arithmetic and function syntax varies most; abstract if portability needed.
•CASE is powerful and flexible — Use for conditional logic, categorization, pivoting, and custom sorting.
•COALESCE handles NULL — Standard, multi-argument NULL handling; prefer over DB-specific functions.
•Scalar subqueries enable correlation — Powerful for aggregation comparisons; watch performance on large datasets.
•Always alias expressions — Computed columns need clear, stable names for application code.

Module Complete:

Module Complete

5 / 5