Advanced SQL Concepts: Pivots, JSON, Trees, and Lateral Joins

1. Pivot and Unpivot (Rows to Columns)

Reporting applications frequently require restructuring tabular data between vertical and horizontal orientations:

PIVOT: Rotates unique values from a single column into multiple horizontal column headers, aggregating numerical metrics across categories.
UNPIVOT: Converts multiple column headers back into normalized key-value row pairs.

While SQL Server and Oracle implement proprietary PIVOT syntax, the universal ANSI SQL pattern uses Conditional Aggregation with CASE WHEN:

Pivoting Categorical Rows into Metric Columns

SQL

Aggregate employee counts and payrolls horizontally across departments

2. JSON Functions in SQL

Modern relational database engines function as hybrid document stores by supporting native JSON parsing, generation, and querying.

Extracting Values: json_extract(data, '$.key') in MySQL/SQLite, or the arrow operator data->>'key' in PostgreSQL.
Creating JSON Objects: json_object('name', name, 'salary', salary) bundles relational rows into structured payloads for REST APIs.
Expanding Arrays to Rows: json_each() (SQLite) or jsonb_array_elements() (PostgreSQL) transforms JSON array items into relational rows.

Querying Semi-Structured JSON Data

SQL

Parse nested JSON attributes directly within standard SQL queries

3. Working with Arrays

Engines like PostgreSQL feature first-class array data types (e.g., INT[], VARCHAR(50)[]):

Array Construction: ARRAY[10, 20, 30] or aggregating rows using array_agg(column).
Membership Filtering: WHERE 'admin' = ANY(roles) tests whether a value exists inside an array.
Unnesting Elements: UNNEST(array_column) unpacks an array into individual table rows for joining.

-- PostgreSQL Array Example
SELECT user_id, array_agg(skill_name) AS skill_list
FROM user_skills
GROUP BY user_id
HAVING 'SQL' = ANY(array_agg(skill_name));

4. Full-Text Search

Full-Text Search (FTS) indexes process unstructured text using linguistic analysis:

Tokenization: Breaking paragraphs into individual words while stripping punctuation.
Stop Word Filtering: Removing common words (like "the", "is", "at") that provide zero search value.
Stemming: Reducing words to their base linguistic root (e.g., "databases" and "database" map to the same root).
Relevance Ranking: Scoring documents based on term frequency and document density (TF-IDF / BM25).

5. Generated (Computed) Columns

Generated columns are special table fields whose values are calculated automatically from other columns in the same row.

VIRTUAL (Default): Computed on-the-fly whenever the row is read. Consumes zero disk storage.
STORED: Evaluated during INSERT and UPDATE, and written to disk pages alongside other columns. Enables direct B-Tree indexing.

Using STORED Generated Columns

SQL

Automatically evaluate line item totals without application math

6. Sequences

A sequence is an independent database object that generates sequential numbers according to a configured step rule:

CREATE SEQUENCE invoice_num_seq START WITH 5000 INCREMENT BY 1;

-- Fetch the next unique integer
SELECT nextval('invoice_num_seq');

`Sequence vs AUTO_INCREMENT`

Cross-Table Sharing:A single sequence can be shared across multiple tables to enforce unique identifiers globally.
Transaction Gaps:If a transaction fetches nextval and then rolls back, the number is not returned to the pool, producing gap intervals.

7. LATERAL JOIN and CROSS APPLY

A LATERAL join (PostgreSQL / MySQL 8+) or CROSS APPLY (SQL Server) allows a subquery in the FROM clause to reference columns from preceding tables in the join order.

Without the LATERAL keyword, subqueries operate in isolation and cannot see outer table columns. LATERAL functions like a forEach loop over rows:

-- PostgreSQL: Retrieve top 2 highest paid employees for each department
SELECT d.name AS department, top_emp.name, top_emp.salary
FROM departments d
LEFT JOIN LATERAL (
    SELECT name, salary
    FROM employees e
    WHERE e.department_id = d.id
    ORDER BY salary DESC
    LIMIT 2
) top_emp ON TRUE;

8. Gaps and Islands Problem

The Gaps and Islands problem is a classic analytical pattern:

Gaps: Identifying missing sequence numbers or absent date ranges in a series (e.g., missing invoice numbers).
Islands: Grouping continuous runs of sequential values into single start/end ranges (e.g., consecutive login days).

The standard technique calculates the difference between the sequence value and ROW_NUMBER(). For continuous rows, this difference remains identical, forming a grouping identifier.

9. Hierarchical and Tree Queries

Hierarchical data structures (such as employee reporting lines, category trees, and bill-of-materials) are navigated using Recursive Common Table Expressions (WITH RECURSIVE).

Navigating an Organization Hierarchy

SQL

Recursively traverse management reporting tiers from executive to staff

10. Bulk Insert and Importing CSV Files

Importing millions of records using individual INSERT statements generates excessive transaction log overhead. Database engines provide bulk loader utilities:

-- PostgreSQL high-speed COPY import
COPY products (id, name, category, price, stock)
FROM '/data/products.csv'
WITH (FORMAT csv, HEADER true);

-- MySQL high-speed LOAD DATA
LOAD DATA INFILE '/data/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
IGNORE 1 ROWS;

11. Exporting Query Results to CSV

To stream query outputs into clean CSV text files for business stakeholders or external analytical pipelines:

-- PostgreSQL export
\\copy (SELECT name, salary FROM employees WHERE salary >= 100000) TO 'high_earners.csv' WITH CSV HEADER;

-- MySQL export
SELECT name, salary
FROM employees
INTO OUTFILE '/var/lib/mysql-files/salaries.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\\n';

Knowledge Check: Advanced SQL Concepts

1. What does a PIVOT operation do to data?

2. How do VIRTUAL generated columns store data?

3. What unique ability does a LATERAL join provide?

4. What technique solves the Gaps and Islands problem?

5. Which clause enables hierarchical tree traversal in standard SQL?

6. How does a sequence differ from AUTO_INCREMENT?