SQL UPSERT, MERGE, LATERAL Joins and Materialized Views

Four advanced features that come up in senior SQL interviews and real data pipelines: UPSERT (insert or update in one statement), MERGE, LATERAL joins for "top N per row" queries, and materialized views that store a query's result for fast reads.

UPSERT: Insert or Update

MySQL: INSERT ... ON DUPLICATE KEY UPDATE

-- MySQL UPSERT: if primary key/unique key conflicts, update instead
INSERT INTO product_inventory (product_id, stock, last_updated)
VALUES (101, 50, NOW())
ON DUPLICATE KEY UPDATE
    stock        = stock + VALUES(stock),  -- add to existing stock (MySQL 8.0.20+ prefers a row alias: VALUES (...) AS new ... stock = stock + new.stock)
    last_updated = NOW();

-- Insert or update user login stats
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (42, 1, NOW())
ON DUPLICATE KEY UPDATE
    login_count = login_count + 1,
    last_login  = NOW();

-- Batch upsert (very efficient)
INSERT INTO prices (product_id, price, updated_at) VALUES
    (1, 999.00, NOW()),
    (2, 1499.00, NOW()),
    (3, 599.00, NOW())
ON DUPLICATE KEY UPDATE
    price = VALUES(price), updated_at = NOW();

PostgreSQL: INSERT ... ON CONFLICT

-- PostgreSQL UPSERT
INSERT INTO product_inventory (product_id, stock)
VALUES (101, 50)
ON CONFLICT (product_id) DO UPDATE
    SET stock = product_inventory.stock + EXCLUDED.stock,
        last_updated = NOW();
-- EXCLUDED refers to the row that was attempted to be inserted

-- Do nothing on conflict (ignore duplicates)
INSERT INTO events (event_id, event_type, user_id)
VALUES ('evt_123', 'click', 42)
ON CONFLICT (event_id) DO NOTHING;

SQL Server / Oracle: MERGE Statement

-- MERGE (SQL Server / Oracle) — the most powerful upsert
MERGE INTO dim_customers AS target
USING (SELECT 101 AS customer_id, 'Hyderabad' AS city) AS source
ON (target.customer_id = source.customer_id)

WHEN MATCHED THEN
    UPDATE SET target.city = source.city, target.updated_at = GETDATE()

WHEN NOT MATCHED BY TARGET THEN
    INSERT (customer_id, city, created_at)
    VALUES (source.customer_id, source.city, GETDATE())

WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET target.is_active = 0;  -- deactivate removed records

-- MERGE is extremely useful for ETL: full sync from staging to production

LATERAL Joins and CROSS APPLY

-- PostgreSQL / MySQL 8: LATERAL
-- For each customer, get their 3 most recent orders
SELECT c.customer_id, c.name,
       o.order_id, o.amount, o.order_date
FROM customers c
CROSS JOIN LATERAL (
    SELECT order_id, amount, order_date
    FROM orders
    WHERE customer_id = c.customer_id   -- ← references outer table column!
    ORDER BY order_date DESC
    LIMIT 3
) o;

-- Without LATERAL, you cannot use c.customer_id inside the FROM subquery:
-- This FAILS:
SELECT c.name, o.amount FROM customers c
JOIN (SELECT amount FROM orders WHERE customer_id = c.customer_id LIMIT 3) o ON TRUE;
-- ERROR: column "c.customer_id" does not exist (c is not in scope)

-- SQL Server: CROSS APPLY (equivalent to LATERAL)
SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
CROSS APPLY (
    SELECT TOP 3 order_id, amount FROM orders
    WHERE customer_id = c.customer_id
    ORDER BY order_date DESC
) o;

-- OUTER APPLY = LEFT LATERAL JOIN (includes customers with no orders)
SELECT c.name, o.order_id
FROM customers c
OUTER APPLY (
    SELECT TOP 1 order_id FROM orders
    WHERE customer_id = c.customer_id
    ORDER BY order_date DESC
) o;
-- o.order_id = NULL for customers with no orders

Materialized Views

Materialized Views in PostgreSQL

-- Create a materialized view: pre-compute monthly revenue
CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(DISTINCT order_id)         AS total_orders,
    COUNT(DISTINCT customer_id)      AS unique_customers,
    SUM(amount)                      AS revenue,
    AVG(amount)                      AS avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Add index on the MV for faster filtering
CREATE INDEX idx_mv_month ON mv_monthly_revenue(month);

-- Query the MV — extremely fast (reads pre-computed data)
SELECT * FROM mv_monthly_revenue WHERE month >= '2025-01-01';

-- Refresh the MV (re-run underlying query, update stored data)
REFRESH MATERIALIZED VIEW mv_monthly_revenue;

-- Refresh WITHOUT locking (allows reads during refresh) — PostgreSQL 9.4+
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;
-- CONCURRENTLY requires a UNIQUE index on the MV
CREATE UNIQUE INDEX idx_mv_month_uniq ON mv_monthly_revenue(month);

-- Drop MV
DROP MATERIALIZED VIEW IF EXISTS mv_monthly_revenue;

Simulating Materialized Views in MySQL

-- MySQL has no native MV. Simulate with a regular table + scheduled refresh.

-- Step 1: Create a summary table (acts as MV)
CREATE TABLE mv_monthly_revenue (
    month          DATE PRIMARY KEY,
    total_orders   INT,
    unique_customers INT,
    revenue        DECIMAL(14,2),
    avg_order_value DECIMAL(10,2),
    refreshed_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Step 2: Refresh procedure (truncate + re-insert)
CREATE PROCEDURE refresh_monthly_revenue()
BEGIN
    TRUNCATE TABLE mv_monthly_revenue;

    INSERT INTO mv_monthly_revenue
        (month, total_orders, unique_customers, revenue, avg_order_value, refreshed_at)
    SELECT
        DATE_FORMAT(order_date, '%Y-%m-01'),
        COUNT(DISTINCT order_id),
        COUNT(DISTINCT customer_id),
        SUM(amount),
        AVG(amount),
        NOW()
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m-01');
END;

-- Step 3: Schedule via MySQL Event Scheduler (runs every day at 2 AM)
CREATE EVENT ev_refresh_revenue
ON SCHEDULE EVERY 1 DAY STARTS '2025-01-01 02:00:00'
DO CALL refresh_monthly_revenue();

When to Use Materialized Views — Interview Answer

FAQs

How do you write an UPSERT in MySQL?

INSERT … ON DUPLICATE KEY UPDATE col = …, which updates the existing row when the insert hits a primary or unique key.

What is the PostgreSQL equivalent of ON DUPLICATE KEY UPDATE?

INSERT … ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col, or DO NOTHING to skip duplicates.

What is the difference between a view and a materialized view?

A view stores only the query and runs it each time; a materialized view stores the result and must be refreshed, trading freshness for speed.