LEFT JOIN WHERE Filter: Missing Customers (SQL Interview)

SQL interviews trip up even experienced developers — not because the syntax is hard, but because interviewers probe whether you understand what happens inside the database engine when your query runs. The difference between a candidate who gets hired and one who doesn't usually comes down to three words: 'and why that?'

I've interviewed over 60 candidates for backend and data engineering roles. The pattern is always the same: 80% can write a SELECT with a JOIN. Maybe 30% can explain why their JOIN is slow. Maybe 10% can look at an execution plan and tell me the optimizer chose a Hash Join when it should have used a Nested Loop. That 10% gets the offer.

Most people can write a SELECT statement; very few can explain why their query is scanning 2 million rows when it should be touching 50. I once inherited a reporting service that took 45 seconds to load a dashboard. The fix was one index and a rewritten subquery. The previous developer had been 'optimizing' by adding more RAM to the server for six months. The query was doing a full table scan on a 14-million-row orders table because someone wrapped an indexed column in YEAR() — making it non-SARGable. One line of SQL, six months of wasted infrastructure spend.

This matters because slow queries cost money. A poorly written JOIN on a 10-million-row orders table can turn a 20ms API response into a 12-second timeout. Knowing the difference between a clustered and non-clustered index, or understanding why a LEFT JOIN returns NULLs you didn't expect, is the difference between shipping reliable software and debugging production fires at 2am.

By the end of this article you'll be able to explain JOINs, NULLs, window functions, indexes, subqueries vs CTEs, GROUP BY, and query optimization at a level that earns the interviewer's respect — not just recognition. Every concept comes with a real schema, runnable SQL, actual output, and the exact reasoning an interviewer wants to hear.

Why LEFT JOIN with WHERE Filters Silently Drops Rows

A LEFT JOIN returns all rows from the left table, with matching rows from the right table — or NULLs where no match exists. The trap: placing a WHERE condition on a column from the right table converts the LEFT JOIN into an INNER JOIN, because NULL comparisons (e.g., WHERE right.id = 5) evaluate to UNKNOWN and filter out non-matching rows. This is not a bug; it's how SQL's three-valued logic works. The filter is applied after the join, so any row that didn't match gets a NULL in that column, and the WHERE clause rejects it. To preserve left-side rows while filtering the right side, move the condition into the ON clause: ON left.id = right.id AND right.status = 'active'. This keeps unmatched rows intact with NULLs for right-side columns. In practice, this mistake causes silent data loss — reports show fewer rows than expected, and debugging takes hours because the query looks correct at first glance. Senior engineers internalize that WHERE filters on the outer table's columns are always applied post-join, and the ON clause is the correct place for pre-join filters on the inner table.

Understanding JOINs: Beyond the Venn Diagram

Interviewers often start with JOINs. While Venn diagrams are a popular starting point, they're misleading for senior candidates because SQL JOINs are not set operations — they're row-combination operations. A LEFT JOIN doesn't 'subtract' anything; it preserves every row from the left table and fills in NULLs when there's no match on the right.

Senior candidates must discuss the logical processing of a JOIN. The database engine decides between three physical operators: Nested Loop (best for small tables), Hash Join (best for large unsorted tables), and Merge Join (best when both inputs are sorted on the join key). Knowing when each is chosen and why shows you understand the engine, not just the syntax.

The most common interview trap: putting a filter in the ON clause vs the WHERE clause of a LEFT JOIN. They produce completely different results. A filter in ON controls which right-side rows participate in the join. A filter in WHERE eliminates rows from the final result — including left-side rows that had no match. This distinction is the single most missed concept in SQL interviews.

I once caught a production bug where a developer filtered on the right table's status column in the WHERE clause of a LEFT JOIN. It silently converted the LEFT JOIN into an INNER JOIN — every customer without an active order disappeared from the report. The finance team made quarterly projections based on that report. The projections were wrong for three months before anyone noticed.

-- io.thecodeforge: LEFT JOIN — ON vs WHERE filter behavior
-- Schema: customers(customer_id, customer_name)
--          orders(order_id, customer_id, order_date, total_amount, status)

-- QUERY 1: Filter in ON clause
-- Preserves ALL customers. Only joins with orders after 2025-01-01.
-- Customers with no recent orders still appear (with NULL order data).
SELECT
    c.customer_name,
    o.order_date,
    COALESCE(o.total_amount, 0) AS amount
FROM io_thecodeforge.customers c
LEFT JOIN io_thecodeforge.orders o
    ON c.customer_id = o.customer_id
    AND o.order_date > '2025-01-01';

-- QUERY 2: Filter in WHERE clause
-- Eliminates any row where order_date is NULL (no match or filtered out).
-- This effectively converts the LEFT JOIN into an INNER JOIN.
SELECT
    c.customer_name,
    o.order_date,
    COALESCE(o.total_amount, 0) AS amount
FROM io_thecodeforge.customers c
LEFT JOIN io_thecodeforge.orders o
    ON c.customer_id = o.customer_id
WHERE o.order_date > '2025-01-01';

-- QUERY 3: Find customers with NO orders after 2025-01-01
-- Classic pattern: LEFT JOIN + WHERE IS NULL
SELECT
    c.customer_name,
    COALESCE(o.total_amount, 0) AS amount
FROM io_thecodeforge.customers c
LEFT JOIN io_thecodeforge.orders o
    ON c.customer_id = o.customer_id
    AND o.order_date > '2025-01-01'
WHERE o.order_id IS NULL;

NULL Handling: The Landmine Nobody Practices

NULL is not zero. NULL is not an empty string. NULL is not 'false.' NULL is the absence of a value — it's the database saying 'I don't know.' And SQL's treatment of NULL is the source of more interview failures and production bugs than any other single concept.

The three rules that govern NULL: 1. NULL != NULL. NULL is not equal to anything, including itself. WHERE column = NULL returns zero rows. Always use IS NULL or IS NOT NULL. 2. Any arithmetic operation with NULL produces NULL. 5 + NULL = NULL. If your revenue column has NULLs, SUM(revenue) ignores them — but revenue * 1.1 produces NULL for those rows. 3. Comparison operators (<, >, =) with NULL produce UNKNOWN, not TRUE or FALSE. This means rows with NULL values are excluded from both WHERE column > 100 and WHERE column <= 100. They fall through both filters.

COALESCE is your safety net. It returns the first non-NULL value from its arguments. Use it to provide defaults: COALESCE(discount, 0) means 'use the discount if it exists, otherwise assume zero.' NVL (Oracle), IFNULL (MySQL), and ISNULL (SQL Server) are database-specific alternatives.

Production story: a revenue report was undercounting by 15%. The query was SELECT SUM(price quantity) FROM order_items. Some rows had NULL quantity (items pending confirmation). Multiplying by NULL produced NULL, and SUM skips NULLs — so those items silently disappeared from the revenue total. The fix was SUM(COALESCE(price, 0) COALESCE(quantity, 0)). One line. Three months of wrong numbers.

-- io.thecodeforge: NULL Handling Patterns
-- Schema: products(product_id, product_name, discount_percent, category)

-- WRONG: This returns ZERO rows even if NULLs exist
-- Because NULL = NULL evaluates to UNKNOWN, not TRUE
SELECT * FROM io_thecodeforge.products
WHERE discount_percent = NULL;

-- RIGHT: Use IS NULL
SELECT * FROM io_thecodeforge.products
WHERE discount_percent IS NULL;

-- WRONG: This excludes NULLs from both sides
-- Products with NULL discount appear in NEITHER result
SELECT * FROM io_thecodeforge.products WHERE discount_percent > 10;
SELECT * FROM io_thecodeforge.products WHERE discount_percent <= 10;
-- Together they don't cover all rows — NULL rows are missing from both.

-- RIGHT: Explicitly handle NULLs with COALESCE
SELECT
    product_name,
    discount_percent,
    COALESCE(discount_percent, 0) AS safe_discount,
    CASE
        WHEN discount_percent IS NULL THEN 'No discount set'
        WHEN discount_percent > 20 THEN 'Heavy discount'
        ELSE 'Standard'
    END AS discount_category
FROM io_thecodeforge.products;

-- PRODUCTION PATTERN: Safe aggregation with NULLs
-- Without COALESCE, NULL quantity items vanish from revenue totals
SELECT
    SUM(COALESCE(unit_price, 0) * COALESCE(quantity, 0)) AS total_revenue,
    COUNT(*) AS total_line_items,
    COUNT(quantity) AS items_with_quantity,  -- COUNT(column) skips NULLs
    COUNT(*) AS all_items                    -- COUNT(*) counts everything
FROM io_thecodeforge.order_items;

Window Functions: The Interview Differentiator

Window functions are the topic that separates mid-level candidates from senior ones. GROUP BY collapses rows. Window functions keep every row but add computed columns based on a 'window' of related rows. If GROUP BY gives you a summary, window functions give you a summary attached to every detail row.

The interview pattern I see most: 'Find the top 3 orders per customer' or 'Find the second-highest salary per department.' Both require ROW_NUMBER() or DENSE_RANK() with PARTITION BY. Candidates who reach for a subquery with LIMIT or a correlated subquery are showing they don't know window functions — and interviewers notice.

Production use case: I used LAG() to detect anomalies in a time-series metrics table. The query compared each hour's error rate to the previous hour's. If the error rate jumped by more than 200%, it flagged the row. That query ran against 50 million rows in 3 seconds because the window function avoided a self-join that would have taken 40+ seconds.

-- io.thecodeforge: Window Functions — Interview Patterns
-- Schema: orders(order_id, customer_id, order_date, total_amount)
--          employees(employee_id, name, department, salary)

-- PATTERN 1: Top N per group (top 2 orders per customer by amount)
-- This is the #1 window function interview question.
SELECT *
FROM (
    SELECT
        customer_id,
        order_id,
        total_amount,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY total_amount DESC
        ) AS rn
    FROM io_thecodeforge.orders
) ranked
WHERE rn <= 2;

-- PATTERN 2: Running total (cumulative revenue by date)
SELECT
    order_date,
    total_amount,
    SUM(total_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM io_thecodeforge.orders
ORDER BY order_date;

-- PATTERN 3: DENSE_RANK for 'second-highest salary per department'
-- DENSE_RANK handles ties correctly. ROW_NUMBER would arbitrarily pick one.
SELECT *
FROM (
    SELECT
        department,
        name,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS dept_rank
    FROM io_thecodeforge.employees
) ranked
WHERE dept_rank = 2;

-- PATTERN 4: LAG for detecting day-over-day anomalies
SELECT
    metric_date,
    error_count,
    LAG(error_count, 1) OVER (ORDER BY metric_date) AS prev_day_errors,
    CASE
        WHEN error_count > LAG(error_count, 1) OVER (ORDER BY metric_date) * 2
        THEN 'SPIKE ALERT'
        ELSE 'Normal'
    END AS status
FROM io_thecodeforge.daily_metrics
ORDER BY metric_date;

GROUP BY and HAVING: Aggregation Done Right

GROUP BY collapses multiple rows into summary rows. HAVING filters those summary rows after aggregation. WHERE filters individual rows before aggregation. Mixing them up is a classic interview mistake — and a common production bug.

The logical processing order matters: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. This means: - WHERE cannot use aggregate functions (SUM, COUNT, AVG) because it runs before GROUP BY. - HAVING can use aggregate functions because it runs after GROUP BY. - You can filter on non-aggregated columns in WHERE (faster) or HAVING (slower, but sometimes necessary).

The trap interviewers love: 'Show me departments where the average salary exceeds 50,000.' That's HAVING AVG(salary) > 50000. Candidates who write WHERE AVG(salary) > 50000 get an immediate syntax error — and the interviewer watches to see if you know why.

Another trap: selecting columns not in the GROUP BY clause. In strict SQL mode (MySQL) or PostgreSQL, SELECT department, name, AVG(salary) FROM employees GROUP BY department fails because 'name' is not in the GROUP BY and is not aggregated. In older MySQL modes, it silently returns an arbitrary name from each group — which is almost never what you want. Always ensure every non-aggregated column in SELECT is in the GROUP BY.

-- io.thecodeforge: GROUP BY and HAVING Patterns
-- Schema: employees(employee_id, name, department, salary, hire_date)
--          orders(order_id, customer_id, order_date, total_amount, status)

-- PATTERN 1: Basic aggregation with HAVING
-- 'Departments with average salary above 50,000'
SELECT
    department,
    COUNT(*) AS headcount,
    AVG(salary) AS avg_salary,
    MAX(salary) AS highest_salary
FROM io_thecodeforge.employees
GROUP BY department
HAVING AVG(salary) > 50000
ORDER BY avg_salary DESC;

-- PATTERN 2: WHERE vs HAVING — use WHERE for non-aggregate filters
-- 'Departments with avg salary > 50k, but only for employees hired after 2020'
-- WRONG: HAVING hire_date > '2020-01-01' — works but inefficient
-- RIGHT: Filter rows first with WHERE, then aggregate
SELECT
    department,
    AVG(salary) AS avg_salary
FROM io_thecodeforge.employees
WHERE hire_date > '2020-01-01'   -- Filters BEFORE grouping (faster)
GROUP BY department
HAVING AVG(salary) > 50000;      -- Filters AFTER grouping

-- PATTERN 3: Multi-column GROUP BY with COUNT DISTINCT
-- 'Revenue per customer per year, only for completed orders'
SELECT
    customer_id,
    EXTRACT(YEAR FROM order_date) AS order_year,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_revenue,
    COUNT(DISTINCT EXTRACT(MONTH FROM order_date)) AS active_months
FROM io_thecodeforge.orders
WHERE status = 'completed'
GROUP BY customer_id, EXTRACT(YEAR FROM order_date)
HAVING SUM(total_amount) > 10000
ORDER BY total_revenue DESC;

Subqueries vs CTEs vs Temp Tables: When to Use What

Interviewers ask: 'What's the difference between a subquery, a CTE, and a temp table?' The answer is about readability, reusability, and performance.

A subquery is a query nested inside another query. It works, but deeply nested subqueries become unreadable fast. If you have a subquery inside a subquery inside a WHERE clause, nobody — including future you — can debug it at 2am during an incident.

A CTE (Common Table Expression) with the WITH clause gives a subquery a name. It reads top-to-bottom, can be referenced multiple times, and makes complex queries self-documenting. The optimizer in PostgreSQL and SQL Server treats CTEs as inline views (not materialized temp tables) unless you explicitly use MATERIALIZED — so there's usually no performance difference from a subquery.

A temp table physically stores intermediate results. Use it when you need to reference the same intermediate result many times, when the intermediate result is expensive to compute, or when you need to index the intermediate result. The downside: it's more I/O and the optimizer can't push predicates through it.

My rule of thumb: CTEs for readability. Subqueries for simple one-off filters. Temp tables for heavy multi-step transformations where you reference the intermediate result 5+ times or need to index it.

-- io.thecodeforge: Subquery vs CTE — Same Query, Different Styles
-- Task: Find customers whose total spend exceeds the overall average

-- APPROACH 1: Correlated Subquery (harder to read)
SELECT
    c.customer_name,
    (SELECT SUM(o.total_amount)
     FROM io_thecodeforge.orders o
     WHERE o.customer_id = c.customer_id) AS total_spend
FROM io_thecodeforge.customers c
WHERE (SELECT SUM(o.total_amount)
       FROM io_thecodeforge.orders o
       WHERE o.customer_id = c.customer_id)
      > (SELECT AVG(customer_total)
         FROM (SELECT SUM(total_amount) AS customer_total
               FROM io_thecodeforge.orders
               GROUP BY customer_id) sub);

-- APPROACH 2: CTE (clean, readable, self-documenting)
WITH customer_spending AS (
    SELECT
        customer_id,
        SUM(total_amount) AS total_spend
    FROM io_thecodeforge.orders
    GROUP BY customer_id
),
average_spending AS (
    SELECT AVG(total_spend) AS avg_spend
    FROM customer_spending
)
SELECT
    c.customer_name,
    cs.total_spend
FROM io_thecodeforge.customers c
JOIN customer_spending cs ON c.customer_id = cs.customer_id
CROSS JOIN average_spending av
WHERE cs.total_spend > av.avg_spend
ORDER BY cs.total_spend DESC;

-- APPROACH 3: Temp table (best when intermediate result is reused many times)
CREATE TEMP TABLE customer_spending_tmp AS
SELECT
    customer_id,
    SUM(total_amount) AS total_spend,
    COUNT(*) AS order_count
FROM io_thecodeforge.orders
GROUP BY customer_id;

-- Now reference it multiple times without recomputing
SELECT
    c.customer_name,
    cs.total_spend,
    cs.order_count
FROM io_thecodeforge.customers c
JOIN customer_spending_tmp cs ON c.customer_id = cs.customer_id
WHERE cs.total_spend > (SELECT AVG(total_spend) FROM customer_spending_tmp)
ORDER BY cs.total_spend DESC;

DROP TABLE IF EXISTS customer_spending_tmp;

Indexing and SARGability: Why Your Query is Slow

A common interview question is: 'Why isn't my index being used?' This usually leads to the concept of SARGable (Search ARgument ABLE) queries. If you wrap an indexed column in a function (like WHERE YEAR(created_at) = 2026), the database engine cannot use the index effectively, leading to a full table scan. Instead, provide a range the engine can 'seek' into.

I've seen this exact pattern kill production performance. A reporting query on a 14-million-row orders table was taking 45 seconds. The developer had WHERE YEAR(order_date) = 2025 AND MONTH(order_date) = 3. The index on order_date was completely ignored because YEAR() and MONTH() wrapped the column. The fix: WHERE order_date >= '2025-03-01' AND order_date < '2025-04-01'. Same result, 12 milliseconds instead of 45 seconds. The index seek touched 40,000 rows instead of scanning all 14 million.

Beyond SARGability, understand covering indexes. If an index contains every column referenced in the query (both in WHERE and SELECT), the engine never touches the actual table — it reads everything from the index. This is called an 'index-only scan' and it's the fastest possible query path.

Clustered vs non-clustered: a clustered index IS the table — data is stored in the order of the clustered index (usually the primary key). A non-clustered index is a separate structure with pointers back to the data. Each table has exactly one clustered index but can have many non-clustered indexes.

-- io.thecodeforge: SARGable vs Non-SARGable Queries
-- Schema: logs(log_id, created_at, level, message, service_name)
-- Index: idx_logs_created_at ON logs(created_at)

-- BAD: Index on created_at is IGNORED due to function wrapping
-- The engine must call DATE() on every row — full table scan
SELECT * FROM io_thecodeforge.logs
WHERE DATE(created_at) = '2026-03-06';

-- GOOD: Index seek enabled — engine jumps directly to matching range
SELECT * FROM io_thecodeforge.logs
WHERE created_at >= '2026-03-06 00:00:00'
  AND created_at < '2026-03-07 00:00:00';

-- BAD: Function on both sides — index cannot be used
SELECT * FROM io_thecodeforge.logs
WHERE LOWER(service_name) = 'payment-service';

-- GOOD: Store data in consistent case, or use a functional index
-- PostgreSQL: CREATE INDEX idx_service_lower ON logs(LOWER(service_name));
-- Then the LOWER() query becomes SARGable on that functional index.

-- COVERING INDEX example:
-- If your query is:
SELECT customer_id, order_date, total_amount
FROM io_thecodeforge.orders
WHERE customer_id = 101 AND order_date > '2025-01-01';

-- Create a covering index that includes ALL referenced columns:
CREATE INDEX idx_orders_covering
ON io_thecodeforge.orders(customer_id, order_date)
INCLUDE (total_amount);
-- Now the engine reads ONLY the index — never touches the table.
-- This is an 'index-only scan' — the fastest possible path.

Query Optimization: Reading Execution Plans and Avoiding Common Traps

The ultimate interview question: 'How would you optimize a slow query?' Here's the systematic approach I use in production, and the one I want to hear from candidates.

Step 1: Run EXPLAIN. Look for Seq Scan on large tables, Nested Loop on large joins, and high row estimates. If the estimated rows are wildly different from actual rows, your table statistics are stale — run ANALYZE.

Step 2: Check for non-SARGable predicates. Functions on indexed columns, implicit type conversions, LIKE with leading wildcards.

Step 3: Check for missing indexes. If the WHERE clause filters on columns without indexes, add them. If the JOIN uses columns without indexes, add them.

Step 4: Check for SELECT *. You're pulling every column from disk when you need three. Use a covering index or select only required columns.

Step 5: Check for N+1 queries. If your application runs one query to get IDs, then one query per ID, that's N+1. Rewrite as a single JOIN or IN clause.

Step 6: Check for DISTINCT abuse. DISTINCT is expensive — it requires sorting or hashing all results to remove duplicates. If you're using DISTINCT to hide duplicate rows caused by a bad JOIN, fix the JOIN instead.

The implicit conversion trap is worth calling out explicitly: comparing a VARCHAR column to an integer literal (WHERE varchar_col = 123) forces the engine to convert every row's value to an integer before comparing. This prevents index usage and causes a full table scan. Always match types: WHERE varchar_col = '123'.

-- io.thecodeforge: Query Optimization Patterns

-- TRAP 1: Implicit type conversion — index ignored
-- phone_number is VARCHAR, but we compare to an integer
-- The engine converts EVERY row's phone_number to int before comparing
SELECT * FROM io_thecodeforge.customers
WHERE phone_number = 9876543210;  -- BAD: implicit conversion

SELECT * FROM io_thecodeforge.customers
WHERE phone_number = '9876543210';  -- GOOD: type match, index used

-- TRAP 2: DISTINCT hiding a bad JOIN
-- If you're getting duplicates and adding DISTINCT to 'fix' it,
-- your JOIN condition is wrong.
SELECT DISTINCT c.customer_name, o.total_amount
FROM io_thecodeforge.customers c
JOIN io_thecodeforge.orders o ON c.customer_id = o.customer_id;
-- If this returns duplicates, check: are there multiple orders per customer?
-- If yes, DISTINCT is removing legitimate rows. If no, the JOIN is wrong.

-- TRAP 3: Subquery in WHERE instead of JOIN
-- Correlated subquery runs once PER ROW — O(n²) behavior
SELECT * FROM io_thecodeforge.customers c
WHERE c.customer_id IN (
    SELECT customer_id FROM io_thecodeforge.orders
    WHERE total_amount > 1000
);
-- Better: use EXISTS (short-circuits on first match)
SELECT * FROM io_thecodeforge.customers c
WHERE EXISTS (
    SELECT 1 FROM io_thecodeforge.orders o
    WHERE o.customer_id = c.customer_id
    AND o.total_amount > 1000
);
-- Best: use JOIN with DISTINCT if you only need customer data
SELECT DISTINCT c.*
FROM io_thecodeforge.customers c
JOIN io_thecodeforge.orders o ON c.customer_id = o.customer_id
WHERE o.total_amount > 1000;

-- TRAP 4: LIKE with leading wildcard — index cannot be used
SELECT * FROM io_thecodeforge.logs
WHERE message LIKE '%timeout%';  -- Full table scan, every time

-- If you need full-text search, use a proper full-text index:
-- PostgreSQL: CREATE INDEX idx_message_fts ON logs USING gin(to_tsvector('english', message));
-- Then: WHERE to_tsvector('english', message) @@ to_tsquery('timeout');

Why Your Interviewer Cares About SQL Dialects (And You Should Too)

Interviewers ask about dialects not because they want a vocabulary test, but because they've been burned by someone who only knew MySQL's LIMIT syntax and crashed a Postgres migration. Every major RDBMS — PostgreSQL, SQL Server, Oracle, MySQL — has its own quirks in date functions, string operations, pagination, and window function support.

The real test isn't 'name three dialects,' it's 'can you adapt to our production stack in two weeks?' When I interview candidates, I ask about Oracle's CONNECT BY or SQL Server's TOP because those are the landmines I've seen wipe out entire sprints. If you've only written queries in one dialect, you're not a SQL developer — you're a user of that specific tool.

Know which dialect your target company uses, and at minimum understand how their date truncation, LIMIT/OFFSET alternatives, and transaction isolation levels differ from the ANSI standard. That's the difference between 'I can write a query' and 'I won't break prod on my second day.'

// io.thecodeforge — interview tutorial

// PostgreSQL dialect: Date truncation
SELECT date_trunc('month', order_date) AS month_start,
       COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY date_trunc('month', order_date);

// SQL Server equivalent: DATEFROMPARTS
SELECT DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1) AS month_start,
       COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1);

// Oracle equivalent: TRUNC
SELECT TRUNC(order_date, 'MM') AS month_start,
       COUNT(*) AS order_count
FROM orders
WHERE order_date >= DATE '2024-01-01'
GROUP BY TRUNC(order_date, 'MM');

DELETE vs TRUNCATE vs DROP: The Nuclear, Conventional, and Surgical Options

Junior devs memorize the syntax differences. Senior devs know when each one will make them the person calling the all-hands-on-deck incident response meeting.

DELETE is surgical — row by row, logged, triggerable, rollbackable. It's for cleaning out those 500 orphaned records from 2019. But it generates transaction log traffic proportional to the number of rows killed. You don't DELETE 50 million rows unless you have an afternoon to burn and your DBA isn't watching.

TRUNCATE is conventional warfare — deallocates data pages, minimal logging, resets identity seeds, and can't be used with a WHERE clause. It's for wiping a staging table before the next ETL run. No triggers fire, no per-row logging. Fast. Lethal. But you can't undo it in most databases without a transaction wrapping it.

DROP is the nuclear option. Table definition, constraints, indexes, permissions — all gone. It's not a data operation; it's schema demolition. I've seen a junior type DROP instead of DELETE on the wrong database and take down a production reporting system for four hours. He still gets reminded about it every sprint retro.

The key insight interviewers probe: 'Can you articulate the operational impact of your choice?' If your answer is just syntax fluff about auto-increment and logging, you're showing you haven't actually managed data in production.

// io.thecodeforge — interview tutorial

-- Safe deletion with explicit transaction for rollback
BEGIN TRANSACTION;

-- DELETE: row-by-row, logged, can be rolled back
DELETE FROM order_errors
WHERE created_at < '2023-01-01'
  AND error_type = 'TIMEOUT';

-- Check count before committing
SELECT COUNT(*) AS remaining_errors FROM order_errors;

-- Commit or rollback based on sanity check
-- COMMIT;  -- uncomment to finalize
-- ROLLBACK; -- uncomment to undo

-- TRUNCATE: fast, minimal logging, no per-row triggers
TRUNCATE TABLE staging_warehouse_snapshot;

-- DROP: schema removal, not data operation
-- DROP TABLE IF EXISTS deprecated_archive;

UNION vs UNION ALL: Performance vs Deduplication

UNION and UNION ALL combine result sets from multiple SELECT queries, but they differ critically in performance. UNION automatically removes duplicate rows, requiring an extra sort or hash operation. UNION ALL returns all rows, including duplicates, without deduplication overhead. In interviews, clarify: use UNION only when you need distinct rows and the datasets are small or moderately sized. For large datasets, UNION ALL with explicit deduplication logic (e.g., using DISTINCT or GROUP BY) often outperforms a raw UNION because you control when deduplication happens. Example: merging customer lists from two regions. If duplicates are impossible by design (e.g., region-specific IDs), UNION ALL is strictly faster. The trap: candidates default to UNION thinking it's safer, but they ignore the cost. Always ask: "Does your data guarantee no duplicates?" If yes, UNION ALL is the correct, faster choice. In production, a slow UNION on a 10M-row table can stall reporting—know when to use each.

// io.thecodeforge — interview tutorial

-- UNION eliminates duplicates (slower)
SELECT customer_id FROM region_east
UNION
SELECT customer_id FROM region_west;

-- UNION ALL keeps duplicates (faster)
SELECT customer_id FROM region_east
UNION ALL
SELECT customer_id FROM region_west;

Foreign Keys: Referential Integrity or Performance Tax?

A foreign key (FK) enforces referential integrity: it ensures a column's values exist in another table's primary key. This prevents orphan rows and maintains data consistency. However, FKs impose a performance cost on INSERT, UPDATE, and DELETE operations because the database checks the referenced table on every write. In interviews, show understanding: FKs are not optional for correctness in relational models, but they can be omitted in high-throughput OLTP systems where application-level integrity is preferred. Example: an orders table with customer_id referencing customers.id ensures no order points to a deleted customer. The trap: ignoring FKs leads to silent data corruption; overusing them in high-write environments causes deadlocks. Best practice: enforce FKs during design, disable them during bulk loads, then re-enable. Know your dialect—MySQL's InnoDB checks FKs; MyISAM ignores them silently. The interviewer wants to see you balance correctness with performance.

// io.thecodeforge — interview tutorial

-- Enforce FK: order.customer_id must exist in customer.id
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- This fails if customer 999 doesn't exist
INSERT INTO orders VALUES (1, 999);  -- Error: foreign key constraint fails

SQL Injection: The Interview Non-Negotiable

SQL injection (SQLi) occurs when user input is concatenated directly into SQL queries, allowing attackers to execute arbitrary SQL. This is the most common web vulnerability. Prevention is mandatory: use parameterized queries (prepared statements) or stored procedures—never string interpolation. In interviews, demonstrate the difference: SELECT * FROM users WHERE username = 'admin' OR '1'='1' bypasses authentication. Parameterized queries separate code from data, making injection impossible. Additional layers: least privilege (read-only accounts for reporting), input validation, and ORM frameworks. The trap: many developers think escaping quotes is enough—it isn't. Attackers exploit encoding, Unicode, and second-order injection. Example: an attacker inserts '<script>' into a name field, then that data is used unsafely in another query. Know your database: MySQL's prepared statements prevent most injections, but PostgreSQL's libpq also supports them. Interviewers ask this to gauge security awareness. Never say "we use regex to filter SQL keywords"—that's a red flag.

// io.thecodeforge — interview tutorial

-- Vulnerable (concatenation)
query = "SELECT * FROM users WHERE username = '" + user_input + "'"

-- Safe (parameterized)
import sqlite3
conn = sqlite3.connect('db.sqlite')
cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE username = ?", (user_input,))
results = cursor.fetchall()