Mastering DML & Basic Queries: Comprehensive SQL Architectural Guide

Architectural Mental Model: Understanding DML & Basic Queries

SQL DML (INSERT, UPDATE, DELETE, SELECT) operates on a relational engine that first parses the statement, then optimizes a logical plan, and finally executes a physical plan. The SELECT statement is a pipeline: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. The WHERE clause is the first filter that reduces the row set before any aggregation or projection occurs. Because it executes early, predicates placed in WHERE can be pushed down to index scans, dramatically reducing I/O. Understanding this order is essential for predicting performance and for avoiding logical errors that arise from mis‑ordered predicates.

Technical Deep Dive: WHERE Clauses & Predicates

The WHERE clause accepts a Boolean expression composed of predicates. Predicates can be simple comparisons (e.g., salary > 50000), range checks (BETWEEN), pattern matches (LIKE), set membership (IN), or sub‑query existence checks (EXISTS). SQL evaluates predicates using three‑valued logic (TRUE, FALSE, UNKNOWN). Any comparison with NULL yields UNKNOWN, which the WHERE clause treats as FALSE, thereby discarding the row. Proper handling of NULLs—using IS NULL or COALESCE—prevents unintended data loss.

-- Example 1: Simple comparison and NULL handling
SELECT employee_id, salary
FROM employees
WHERE salary > 75000 AND department_id IS NOT NULL; -- -> expected output: rows with salary > 75000 and a defined department

Demonstrates a basic numeric predicate combined with an explicit NULL check to avoid discarding rows where department_id is NULL.

-- Example 2: Range and pattern predicates
SELECT product_id, name, price
FROM products
WHERE price BETWEEN 20 AND 100
  AND name LIKE 'A%'; -- -> expected output: products priced 20‑100 whose names start with 'A'

Shows BETWEEN for inclusive range and LIKE for prefix matching; both predicates are evaluated together.

-- Example 3: Set membership and EXISTS sub‑query
SELECT c.customer_id, c.email
FROM customers c
WHERE c.country IN ('US','CA','UK')
  AND EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.status = 'shipped'); -- -> expected output: customers in listed countries who have at least one shipped order

Combines IN with a correlated EXISTS to filter on relational existence, illustrating predicate push‑down potential.

Predicate Composition and Short‑Circuit Evaluation

SQL does not guarantee short‑circuit evaluation; the optimizer may reorder predicates for cost efficiency. Consequently, side‑effects in user‑defined functions can appear unpredictable if developers assume left‑to‑right execution. To write deterministic logic, avoid embedding state‑changing operations inside predicates. Instead, compute derived columns in a CTE or sub‑query, then filter on the stable result.

-- Example 4: Using a CTE to isolate complex logic
WITH enriched AS (
    SELECT order_id,
           total_amount,
           CASE WHEN discount_code IS NULL THEN 0 ELSE 1 END AS has_discount
    FROM orders
)
SELECT order_id, total_amount
FROM enriched
WHERE has_discount = 1 AND total_amount > 500; -- -> expected output: discounted orders over $500

The CTE materializes the discount flag, guaranteeing that the subsequent WHERE clause sees a deterministic column.

Edge Cases: NULL, UNKNOWN, and Three‑Valued Logic

When a predicate involves NULL, the result is UNKNOWN. UNKNOWN behaves like FALSE in the WHERE clause, but it propagates differently in HAVING, CHECK constraints, and CASE expressions. For instance, WHERE column 5 excludes rows where column is NULL, even though the intention might be to keep them. Explicitly test for NULL with IS NULL or coalesce to a sentinel value.

-- Example 5: Demonstrating UNKNOWN handling
SELECT employee_id, bonus
FROM payroll
WHERE bonus <> 0; -- -> expected output: rows where bonus is non‑zero; rows with NULL bonus are omitted

SELECT employee_id, COALESCE(bonus,0) AS bonus
FROM payroll
WHERE COALESCE(bonus,0) <> 0; -- -> expected output: same as above but includes rows where bonus was NULL and treated as 0

First query unintentionally drops NULL bonuses; second query normalizes NULLs before comparison.

Performance Considerations and Predicate Push‑Down

Modern query optimizers attempt to push predicates as close to the data source as possible. Indexes on columns used in WHERE predicates enable index seeks instead of full scans. However, functions applied to indexed columns (e.g., UPPER(name) = 'ALICE') inhibit push‑down unless a functional index exists. Similarly, predicates that reference volatile functions (RAND()) prevent deterministic push‑down.

-- Example 6: Index‑friendly predicate vs. function-wrapped predicate
SELECT id, username
FROM users
WHERE username = 'jdoe'; -- -> expected output: uses index on username for fast lookup

SELECT id, username
FROM users
WHERE LOWER(username) = 'jdoe'; -- -> expected output: may trigger full scan unless a functional index on LOWER(username) exists

Illustrates how wrapping a column in a function can degrade performance.

Data Modification Statements: INSERT, UPDATE, and DELETE

While data retrieval via SELECT is the primary focus of query optimization, complete mastery of SQL Data Manipulation Language (DML) requires understanding how relational engines execute mutations (INSERT, UPDATE, and DELETE). Mutations operate under the same ACID transaction semantics and row filtration mechanics as queries, meaning that all predicate rules, sargability constraints, and three-valued logic apply directly to UPDATE and DELETE operations.

1. Row Insertion: Atomic and Bulk Operations

The INSERT statement creates new row tuples within a table. Modern SQL engines optimize multi-row inserts by batching transaction log writes and index updates rather than firing individual disk operations per row. In engines supporting standard SQL extensions (such as PostgreSQL and SQLite), the RETURNING clause allows capturing generated keys or default timestamps without issuing a secondary lookup query.

-- Example 7: Multi-row insert with column mapping
INSERT INTO employees (employee_id, name, department_id, salary, hire_date)
VALUES 
    (101, 'Alex Rivera', 3, 82000, '2024-01-15'),
    (102, 'Morgan Chen', 3, 79000, '2024-02-01');
-- -> expected output: INSERT 0 2 (two rows inserted atomically)

Batching values in a single statement reduces round-trips and transaction logging overhead.

2. Row Modification: Targeted UPDATE Operations

An UPDATE statement modifies existing column values across rows that satisfy its WHERE clause. Crucially, omitting the WHERE clause updates every row in the table—a catastrophic production error. Optimizers evaluate the WHERE predicate to locate target rows using existing indexes before applying new expressions.

-- Example 8: Targeted update using arithmetic expression and predicate filtering
UPDATE employees
SET salary = salary * 1.05,
    updated_at = CURRENT_TIMESTAMP
WHERE department_id = 3
  AND salary < 85000;
-- -> expected output: UPDATE 2 (only rows matching both department and salary threshold are updated)

Demonstrates atomic column recalculation scoped strictly by indexable filter predicates.

3. Row Deletion: Predicate-Driven DELETE vs TRUNCATE

The DELETE statement removes rows that satisfy a WHERE filter while preserving the table schema, constraints, and triggers. In contrast to the DDL command TRUNCATE (which deallocates data pages rapidly without row logging), DELETE records each deleted tuple in the transaction log, checks foreign key constraints, and fires active triggers.

-- Example 9: Safe row deletion using sub-query criteria
DELETE FROM payroll_adjustments
WHERE status = 'processed'
  AND processed_date < '2023-01-01';
-- -> expected output: DELETE 14 (removes only archived adjustments meeting retention rules)

Removes aged records while enforcing relational integrity and firing active DELETE triggers.

Common Pitfalls & Diagnostic Gotchas

When preparing for technical assessments, pay close attention to these documented candidate misconceptions and edge cases:

Evaluation Semantics: Column Aliases and Clause Precedence

In standard SQL-92 and relational database architectures, column aliases defined within the SELECT projection list are strictly inaccessible inside the WHERE and GROUP BY clauses because the engine processes row filtration and grouping prior to evaluating projection expressions. For instance, executing SELECT price * 1.08 AS total_price FROM products WHERE total_price > 100; triggers an immediate compilation error in strict relational engines like PostgreSQL and Oracle. To resolve this correctly, engineers must either repeat the raw arithmetic predicate in the WHERE clause or encapsulate the projected expression inside a Common Table Expression (CTE) or sub-query. In contrast, the ORDER BY clause executes after the SELECT list, meaning column aliases can be referenced directly for result sorting. Mastering this logical boundary prevents common syntax regressions and clarifies how relational query optimizers generate physical execution plans.

Key Takeaways & Summary

• WHERE is the earliest row‑filtering stage; predicates placed here affect all downstream operations
• Three‑valued logic requires explicit NULL handling to avoid accidental row loss
• Predicate push‑down and index utilization are critical for scalable DML; avoid wrapping indexed columns in non‑sargable functions