Join and Combine Data with SQL in PostgreSQL

Database: PostgreSQL (hr schema, Docker container)

Table of Contents

1. Fundamentals of Data Combination

• 1.1 Introduction to combining data with PostgreSQL
• 1.2 Course database mining
• 1.3 First examples of JOIN and SET Operations

2. Working with JOINs

• 2.1 Introduction to JOIN operations
• 2.2 INNER JOIN in practice
• 2.3 Two-part Naming
• 2.4 Identify OUTER JOIN mismatches
• 2.5 Other JOIN types
• 2.6 LATERAL JOIN — line-by-line correlated joins
• 2.7 How PostgreSQL executes JOINs — EXPLAIN
• 2.8 Common JOIN Errors

3. Combine results with set operations

• 3.1 Introduction to set operations
• 3.2 UNION — combine two result sets
• 3.3 UNION vs UNION ALL — performance difference
• 3.4 INTERSECT — find common elements
• 3.5 EXCEPT — directional set difference
• 3.6 Summary of set operations

4. Subqueries and CTE (Common Table Expressions)

• 4.1 Introduction to subqueries and CTEs
• 4.2 Current subquery patterns
• 4.3 Subqueries as table sources and expressions
• 4.4 Modify data with subqueries
• 4.5 Other uses of subqueries
• 4.6 Introduction to CTE
• 4.7 CTE to clean a complex query
• 4.8 CTE to respect the DRY principle
• 4.9 CTE recursive
• 4.10 Advanced recursive CTE options

5. Combine and validate results

• 5.1 Query combination techniques
• 5.2 Multi-stage pipeline
• 5.3 JOIN with aggregation
• 5.4 Validation of complex queries
• 5.5 PostgreSQL execution plans — EXPLAIN and EXPLAIN ANALYZE

6. Setting up the course database

1. Fundamentals of Data Combination

1.1 Introduction to combining data with PostgreSQL

Why combine data?

Business data never lives in a single table. In an HR application, for example:

• The employee files come from the HRIS system
• The remuneration comes from the payroll system
• Engagement scores come from survey data

Real business questions always cross these boundaries: comparing commitment by seniority, salaries by department. Relational databases are designed precisely for this.

The relational model

Each table in a relational database represents a single entity: employees, departments, survey results. primary keys uniquely identify each record, and foreign keys link records between tables. This standardized design avoids duplication and keeps data clean and consistent.

When it comes time to analyze, JOIN brings these tables together, rebuilding a unified and reliable view.

SQL Standards Compliance

PostgreSQL follows the ANSI SQL-92 standard for JOINs and set operations — the syntax learned here works in almost all other database engines. PostgreSQL also adds advanced features (data types, functions), but the fundamentals remain faithful to the SQL standard. Some improvements, such as the USING clause, come from the SQL-1999 standard.

1.2 Exploring the course database

The course database is an HR database containing demographic data, payroll and engagement survey results. It runs in a Docker container and is accessible from the PostgreSQL extension of Visual Studio Code.

Structure of schema hr

-- Lister toutes les tables du schéma
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema = 'hr'
ORDER BY table_name;

-- Afficher les colonnes et types de données de la table 'hris'
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'hr' AND table_name = 'hris'
ORDER BY ordinal_position;

-- Afficher les relations de clés étrangères dans le schéma 'hr'
SELECT
  tc.table_name,
  kcu.column_name,
  ccu.table_name AS referenced_table,
  ccu.column_name AS referenced_column
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema = 'hr';

-- Aperçu des données des tables clés
SELECT * FROM hr.hris LIMIT 5;
SELECT * FROM hr.demographics LIMIT 5;

All tables connect via employee_id, which ensures referential integrity.

1.3 First examples of JOIN and SET Operations

Here are two fundamentally different ways to combine data in SQL.

JOIN — combine columns side by side

-- Joindre les tables pour afficher les détails avec données démographiques et scores d'engagement
SELECT h.employee_id, h.first_name, h.last_name, d.city, e.satisfaction_score
FROM hr.hris h
JOIN hr.demographics d USING (employee_id)
JOIN hr.engagement_results e USING (employee_id)
LIMIT 5;

A JOIN enriches each row with related data from other tables. The USING clause is a feature of the SQL-1999 standard.

SET Operation — stack rows vertically

-- Lister les employés actifs et terminés en utilisant UNION ALL
SELECT
  employee_id,
  first_name,
  last_name,
  department,
  'Active' AS employment_status
FROM hr.hris
WHERE termination_date IS NULL

UNION ALL

SELECT
  employee_id,
  first_name,
  last_name,
  department,
  'Terminated' AS employment_status
FROM hr.hris
WHERE termination_date IS NOT NULL
ORDER BY employment_status, employee_id;

• JOIN: expands data horizontally — more details per employee.
• SET Operations: Combine data vertically — more rows from multiple queries.

2. Working with JOINs

2.1 Introduction to JOIN operations

Basic mechanism

When a JOIN is issued, PostgreSQL matches rows in one table with rows in another when the join columns have matching values. You can think of this as aligning two worksheets to a common column — each matching pair produces a single combined row in the result.

Conformant Columns

For a JOIN to be correct, the join columns must be compliant:

• Same data type
• Same meaning in real world
• Ideally same name

Two columns can both be called id, but if one refers to employees and the other to suppliers, that is an invalid relationship. PostgreSQL will not block this JOIN, but the results will be meaningless.

Key Terminology

2.2 INNER JOIN in practice

The INNER JOIN returns only rows where matches exist in both tables. This is the most common form of join — it filters out unmatched records.

-- Récupérer les employés actifs avec leur ville de résidence
SELECT
    h.employee_id,
    h.first_name,
    h.last_name,
    d.city,
    h.hire_date
FROM hr.hris AS h
JOIN hr.demographics AS d
  ON h.employee_id = d.employee_id
WHERE h.termination_date IS NULL
ORDER BY h.employee_id;

-- Variante avec USING (quand les colonnes ont le même nom) :
-- JOIN hr.demographics USING (employee_id);

Each line represents a successful key match. Only employees present in both tables appear in the result.

2.3 Two-part Naming

Two-part naming is a good practice in professional SQL, particularly in multi-schema databases:

• For tables: schema.table (e.g.: hr.hris) — ensures access to the desired object
• For columns: alias_table.column (e.g.: h.employee_id) — specifies exactly which table provides the column

Why it matters:

• Avoid ambiguities when multiple tables have columns with the same name
• Make queries immediately readable when reviewing or debugging
• Simple queries scale often; explicit naming from the start avoids correcting unqualified references retrospectively
• Translates cleanly between database systems

2.4 Identify mismatches with OUTER JOINs

OUTER JOINs preserve unmatched rows — they are as much about finding gaps as they are about finding matches.

LEFT JOIN

Keep all rows from left table, replace missing right columns with NULL.

-- Trouver tous les employés en affichant les résultats de sondage s'ils existent
SELECT
    h.employee_id,
    h.first_name,
    h.last_name,
    e.satisfaction_score
FROM hr.hris AS h
LEFT JOIN hr.engagement_results AS e
  ON h.employee_id = e.employee_id
WHERE h.termination_date IS NULL
ORDER BY h.employee_id;

NULLs in satisfaction_score indicate that no matching record exists in the engagement table.

FULL JOIN

Returns all rows from both tables, with NULL where a match does not exist.

-- Inclure tout le monde : employés et répondants au sondage sans correspondance
SELECT
    h.employee_id,
    h.first_name,
    e.satisfaction_score
FROM hr.hris AS h
FULL JOIN hr.engagement_results AS e
  ON h.employee_id = e.employee_id
ORDER BY employee_id;

Useful for reconciliation and data quality checks — e.g. : Survey responses submitted under an obsolete ID.

RIGHT JOIN

Keep all rows from the right table. Functionally equivalent to a LEFT JOIN with the tables in reverse order.

-- Toutes les réponses de sondage, employés s'il y a correspondance
SELECT
    h.employee_id,
    h.first_name,
    h.last_name,
    e.satisfaction_score
FROM hr.engagement_results AS e
RIGHT JOIN hr.hris AS h
  ON h.employee_id = e.employee_id
WHERE h.termination_date IS NULL
ORDER BY h.employee_id;

2.5 Other JOIN types

CROSS JOIN — Cartesian product

Returns the Cartesian product: each line on the left combined with each line on the right. If one table has 100 rows and the other does too, the result is 10,000 rows.

-- Créer une table temporaire de dates de rapport
CREATE TEMP TABLE report_dates (target_date DATE);

INSERT INTO report_dates (target_date)
VALUES
  (DATE '2025-01-01'),
  (DATE '2025-01-15'),
  (DATE '2025-02-01');

-- Générer un CROSS JOIN entre les employés actifs et les dates
SELECT
    h.employee_id,
    h.first_name,
    h.last_name,
    r.target_date
FROM hr.hris AS h
CROSS JOIN report_dates AS r
WHERE h.termination_date IS NULL
ORDER BY h.employee_id, r.target_date;

-- Nettoyer la table temporaire
DROP TABLE IF EXISTS report_dates;

The CROSS JOIN does not have an ON or USING clause. Use with caution — result set may explode.

SELF JOIN — compare rows in the same table

-- Faire correspondre les employés à d'autres dans le même département
SELECT
    e.first_name  AS employee,
    p.first_name  AS peer,
    e.department
FROM hr.hris AS e
JOIN hr.hris AS p
  ON e.department = p.department
WHERE e.employee_id <> p.employee_id
ORDER BY e.department, e.employee_id;

ANTI JOIN — find mismatches

The anti JOIN is one of the most useful patterns in practice: LEFT JOIN + WHERE IS NULL to find unmatched rows.

-- Employés n'ayant jamais complété de formation
SELECT
    h.employee_id,
    h.first_name,
    h.last_name,
    h.department
FROM hr.hris AS h
LEFT JOIN hr.talent_development AS t
  ON h.employee_id = t.employee_id
WHERE t.employee_id IS NULL
ORDER BY h.department, h.employee_id;

Efficient and readable pattern to locate gaps: employees without training, customers without orders, etc.

NATURAL JOIN — avoid

A NATURAL JOIN automatically uses all columns with the same name in both tables as join keys — hidden and fragile behavior.

-- NATURAL JOIN utilise toutes les colonnes en commun (à utiliser avec précaution)
SELECT
    employee_id,
    first_name,
    last_name,
    department,
    training_hours
FROM hr.hris
NATURAL JOIN hr.talent_development
ORDER BY employee_id
LIMIT 10;

-- Vérifier les colonnes en commun entre deux tables
SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'hr'
  AND table_name IN ('hris', 'talent_development')
GROUP BY column_name
HAVING COUNT(DISTINCT table_name) = 2
ORDER BY column_name;

Problem: If the two tables share 8 columns with the same name, PostgreSQL uses all 8 as join conditions, which may produce zero results or behave differently after adding a new column.

Principle of Python Zen: Explicit is better than implicit. A NATURAL JOIN hides its logic. Always write join conditions explicitly.

2.6 LATERAL JOIN — row-by-row correlated joins

Fundamental concept

In all traditional joins, the right side is logically independent of the left side — the joined table cannot depend on the values ​​of the current left row. The LATERAL removes precisely this restriction.

Introduced in the ANSI SQL-1999 standard, LATERAL allows the right side of a JOIN to reference columns of the current row on the left side. This turns the JOIN into something more expressive — an operation aware of the current line.

Top-N per row — best alternative to window functions

Traditional approach with window function:

-- Approche traditionnelle (window function)
SELECT *
FROM (
    SELECT
        h.employee_id,
        h.first_name,
        e.satisfaction_score,
        e.work_life_balance_score,
        ROW_NUMBER() OVER (
            PARTITION BY h.employee_id
            ORDER BY e.employee_id DESC
        ) AS rn
    FROM hr.hris AS h
    JOIN hr.engagement_results AS e
      ON h.employee_id = e.employee_id
) AS ranked
WHERE rn <= 3;

Preferable approach with LATERAL:

-- Version LATERAL (préférée)
SELECT
    h.employee_id,
    h.first_name,
    le.satisfaction_score,
    le.work_life_balance_score
FROM hr.hris AS h
JOIN LATERAL (
    SELECT
        satisfaction_score,
        work_life_balance_score
    FROM hr.engagement_results AS e
    WHERE e.employee_id = h.employee_id
    ORDER BY e.employee_id DESC
    LIMIT 3
) AS le ON true;

Advantage: the LIMIT 3 clause executes inside a correlated subquery that executes once per employee. No global ranking stage, no bulky intermediate result.

Condition access to a linked table

-- Approche JOIN traditionnelle
SELECT
    h.employee_id,
    h.first_name,
    a.hours_absent,
    a.length_of_service
FROM hr.hris AS h
JOIN hr.employee_absence AS a
  ON a.employee_id = h.employee_id
WHERE h.termination_date IS NULL
  AND date_part('year', age(current_date, h.hire_date)) >= 5;

-- Version LATERAL — la valeur dérivée conditionne le JOIN
SELECT
    h.employee_id,
    h.first_name,
    la.hours_absent,
    la.length_of_service
FROM hr.hris AS h
JOIN LATERAL (
    SELECT
        a.hours_absent,
        a.length_of_service
    FROM hr.employee_absence AS a
    WHERE a.employee_id = h.employee_id
      AND date_part('year', age(current_date, h.hire_date)) >= 5
) AS la ON true
WHERE h.termination_date IS NULL;

With LATERAL, the eligibility condition is evaluated first. If an employee does not meet the criterion, the absence table is never accessed for him.

Reusable intermediate subcalculations

-- Plusieurs LATERAL JOIN comme sous-calculs nommés
SELECT
    h.employee_id,
    h.first_name,
    svc.years_of_service,
    eng.avg_engagement_score,
    cls.engagement_category
FROM hr.hris AS h

-- Sous-calcul 1 : années de service
JOIN LATERAL (
    SELECT
        date_part('year', age(current_date, h.hire_date)) AS years_of_service
) AS svc ON true

-- Sous-calcul 2 : score d'engagement moyen
LEFT JOIN LATERAL (
    SELECT
        ROUND(AVG(
            (e.satisfaction_score +
             e.work_life_balance_score +
             e.career_growth_score +
             e.communication_score +
             e.teamwork_score) / 5.0
        ),2) AS avg_engagement_score
    FROM hr.engagement_results AS e
    WHERE e.employee_id = h.employee_id
) AS eng ON true

-- Sous-calcul 3 : classification basée sur les résultats précédents
JOIN LATERAL (
    SELECT
        CASE
            WHEN eng.avg_engagement_score >= 7 THEN 'High'
            WHEN eng.avg_engagement_score >= 5 THEN 'Moderate'
            ELSE 'Low'
        END AS engagement_category
) AS cls ON true

WHERE h.termination_date IS NULL;

Keeps the query DRY, makes the intent obvious, and exposes intermediate values ​​directly in the final result.

Impossible case with a standard JOIN

-- Pour chaque employé actif, trouver la seule formation la plus récente dans les 6 derniers mois
SELECT
    h.employee_id,
    h.first_name,
    h.last_name,
    c.base_salary,
    t_match.hire_date     AS training_date,
    t_match.job_title     AS training_job_title
FROM hr.hris AS h

LEFT JOIN hr.radford_compensation AS c
  ON c.employee_id = h.employee_id

LEFT JOIN LATERAL (
    SELECT
        t.hire_date,
        t.job_title
    FROM hr.talent_development AS t
    WHERE t.employee_id = h.employee_id
      AND t.hire_date < CURRENT_DATE
      AND t.hire_date >= CURRENT_DATE - INTERVAL '6 months'
    ORDER BY t.hire_date DESC
    LIMIT 1
) AS t_match ON true

WHERE h.termination_date IS NULL;

The time window depends on the current employee line. The training lines must be ordered relative to this line, and the result must be limited to a single match. Impossible with a standard JOIN.

Common theme of LATERAL JOIN: scope (scope). LATERAL allows you to write queries where part of the JOIN depends on the current row, and to run this logic in the per-row context rather than after constructing a large result set.

2.7 How PostgreSQL executes JOINs — EXPLAIN

PostgreSQL must decide how to combine rows each time a JOIN executes: which table to read first, how to find matches, whether to build an in-memory hash table or do a linear scan. The keyword EXPLAIN exposes this decision process.

-- Voir le plan d'exécution pour récupérer les employés actifs
EXPLAIN --ANALYSE
SELECT
    h.employee_id,
    h.first_name,
    h.last_name,
    d.city,
    e.satisfaction_score
FROM hr.hris AS h
JOIN hr.demographics AS d
  ON h.employee_id = d.employee_id
LEFT JOIN hr.engagement_results AS e
  ON h.employee_id = e.employee_id
WHERE h.termination_date IS NULL;

Join strategies PostgreSQL can choose

The scheduler automatically chooses the method with the lowest estimated cost. By adding ANALYZE, PostgreSQL actually executes the query and reports the actual timings.

2.8 Common errors with JOINs

-- Clause ON manquante : chaque ligne rejoint chaque ligne (produit cartésien non intentionnel)
SELECT
    h.employee_id,
    d.city
FROM hr.hris AS h, hr.demographics AS d;

-- Mauvaise colonne de jointure : syntaxiquement valide, logiquement absurde
SELECT
    h.employee_id,
    h.first_name,
    h.last_name,
    d.city
FROM hr.hris AS h
JOIN hr.demographics AS d
  ON h.department = d.city;

-- Filtrage après la jointure au lieu de pendant — convertit le LEFT JOIN en INNER JOIN
SELECT
    h.employee_id,
    h.first_name,
    d.city
FROM hr.hris AS h
LEFT JOIN hr.demographics AS d
  ON h.employee_id = d.employee_id
WHERE d.city IS NOT NULL;

-- Version corrigée : filtrer dans la condition JOIN pour préserver les lignes sans correspondance
SELECT
    h.employee_id,
    h.first_name,
    d.city
FROM hr.hris AS h
LEFT JOIN hr.demographics AS d
  ON h.employee_id = d.employee_id
 AND d.city IS NOT NULL;

-- Référence ambiguë — toujours utiliser le nommage en deux parties
SELECT employee_id, city 
FROM hr.hris 
JOIN hr.demographics USING (employee_id);

Summary of common errors:

1. ON clause missing → accidental Cartesian product (2500 rows from tables of 250 rows)
2. Non-compliant columns → meaningless results because the domains do not match
3. WHERE filter instead of AND in ON → the LEFT JOIN behaves like an INNER JOIN
4. Ambiguous references → always prefix with table alias

3. Combine the results with set operations

3.1 Introduction to set operations

Set operations are not like JOINs — they do not match rows across tables. They combine entire result sets, line by line.

Each request is self-contained. PostgreSQL aligns the columns, stacks the results, and applies the chosen rule: UNION, INTERSECT, or EXCEPT.

These operations come directly from classical set theory. EF Codd treated a query result as a relation — essentially a set of tuples with a defined structure. Because of this design, SQL can apply set theory operations directly to query results, as long as both sides return the same column structure.

3.2 UNION — combine two result sets

Typical use case: take two separate queries returning the same structure and get a unified list.

-- Combiner actifs et terminés en une seule liste
SELECT *
FROM (
    SELECT employee_id, first_name, last_name, termination_date
    FROM hr.hris
    WHERE termination_date IS NULL

    UNION

    SELECT employee_id, first_name, last_name, termination_date
    FROM hr.hris
    WHERE termination_date IS NOT NULL
) AS combined
ORDER BY random();

Compliance rule: Before both queries can be combined, they must return the same number of columns with compatible data types.

The query is wrapped in a subquery because PostgreSQL allows ORDER BY only on the final combined result, not inside the UNION itself.

The column name of the combined result set always comes from first SELECT. The second SELECT should match the structure, but its column names are ignored.

3.3 UNION vs UNION ALL — performance difference

Both operators combine two sets of results, but in fundamentally different ways.

-- Démonstration de la différence entre UNION et UNION ALL
SELECT employee_id, first_name, last_name
FROM hr.hris
WHERE department = 'Finance'

UNION --ALL    -- basculer entre UNION et UNION ALL

SELECT employee_id, first_name, last_name
FROM hr.hris
WHERE employee_id < 105
ORDER BY employee_id;

Practical rule: if one is certain that the two sets cannot overlap, there is no practical reason to use UNION. We pay for a deduplication step which adds nothing. Even when duplicates are possible, UNION ALL is often still appropriate if downstream logic does not care.

3.4 INTERSECT — find common elements

INTERSECT returns rows that appear in both queries. This is the operator to use when the question is: Who appears in both sets of results?

-- Trouver les employés ayant participé à la fois à la formation et au sondage
SELECT td.employee_id
FROM hr.talent_development td

INTERSECT --ALL    -- utiliser ALL pour inclure les doublons

SELECT er.employee_id
FROM hr.engagement_results er
ORDER BY employee_id;

-- Ceci échouera si les définitions de colonnes ne correspondent pas
SELECT * FROM hr.talent_development
INTERSECT
SELECT * FROM hr.engagement_results
ORDER BY employee_id;

Features:

• Compare entire rows positionally — full row must match
• Remove duplicates by definition (follows mathematical semantics of sets)
• INTERSECT ALL variant available to keep duplicates

Typical HR use cases: training compliance AND survey participation, benefits enrollment AND payroll sync, mandatory certification AND background check.

3.5 EXCEPT — directional set difference

EXCEPT does the subtraction: returns the rows of the first set after deleting those appearing in the second. This is the operator to use when the question is: Who is in A but not in B?

-- Trouver les employés n'ayant pas rempli le sondage d'engagement
SELECT employee_id
FROM hr.hris

EXCEPT --ALL    -- utiliser ALL pour inclure les doublons

SELECT employee_id
FROM hr.engagement_results
ORDER BY employee_id;

-- Ceci échouera si les définitions de colonnes ne correspondent pas
SELECT * FROM hr.hris
EXCEPT
SELECT * FROM hr.engagement_results;

Important: EXCEPT is directional — the order of queries matters. A EXCEPT B means give me the lines from A that don’t appear in B. Reversing the order gives a different answer.

Use case: Who did not participate in the survey? Who has not completed the training? Who is never registered for benefits?

3.6 Summary of set operations

Universal rules:

1. Queries must return the same number of columns
2. Column types must be compatible
3. Column names always come from first SELECT

Challenge: Using only UNION, INTERSECT, and EXCEPT, write a query to get the symmetric difference — elements in either set, but not both.

4. Subqueries and CTE (Common Table Expressions)

4.1 Introduction to subqueries and CTEs

Concept of subqueries

Subqueries are simply queries inside other queries. When one question depends on another, a subquery handles that dependency directly, without temporary tables or additional steps. They keep complex logic contained and readable.

Basic structure:

-- Structure d'une sous-requête
SELECT colonne_externe
FROM table_externe
WHERE colonne IN (
    SELECT colonne
    FROM autre_table
    WHERE condition
);  -- parenthèses obligatoires

• parentheses are required
• Subquery can be any valid SELECT query
• An alias is required when the subquery acts as a table (in FROM or JOIN)
• A list of column aliases can be provided to rename output columns

4.2 Common subquery patterns

Scalar subquery

Returns a single value that the external query uses as an expression.

-- Comparer le score de chaque employé à la moyenne générale de l'entreprise
SELECT 
    employee_id,
    employee_name,
    satisfaction_score,
    (SELECT ROUND(AVG(satisfaction_score), 2)
     FROM hr.engagement_results) AS company_avg_score
FROM hr.engagement_results
ORDER BY employee_id
LIMIT 10;

Membership subquery with IN

-- Récupérer les employés qui sont aussi dans le programme de développement des talents
SELECT 
    employee_id,
    first_name,
    last_name,
    department
FROM hr.hris
WHERE employee_id IN (
    SELECT employee_id
    FROM hr.talent_development
)
ORDER BY employee_id
LIMIT 10;

Existence subquery with EXISTS

-- Trouver les employés qui font partie du programme de développement des talents
SELECT 
    h.employee_id,
    h.first_name,
    h.last_name,
    h.department
FROM hr.hris h
WHERE EXISTS (
    SELECT 1
    FROM hr.talent_development t
    WHERE t.employee_id = h.employee_id
)
ORDER BY h.employee_id
LIMIT 10;

EXISTS performs a correlated search and stops when a match is found. This is often the safer and more predictable choice in PostgreSQL, especially when the subquery may contain duplicates or NULLs.

HAVING clause with subquery

-- Départements avec satisfaction supérieure à la moyenne
SELECT 
    department,
    AVG(satisfaction_score) AS dept_avg
FROM hr.engagement_results
GROUP BY department
HAVING AVG(satisfaction_score) >
       (SELECT AVG(satisfaction_score)
        FROM hr.engagement_results)
ORDER BY dept_avg DESC;

4.3 Subqueries as table sources and expressions

Subquery in FROM — derived table

-- Sous-requête dans FROM : moyennes par département comme table dérivée
SELECT 
    d.department,
    d.avg_satisfaction,
    COUNT(h.employee_id) AS dept_headcount
FROM (
        SELECT 
            department,
            ROUND(AVG(satisfaction_score),2)
        FROM hr.engagement_results
        GROUP BY department
     ) AS d (department, avg_satisfaction)
JOIN hr.hris h 
    ON h.department = d.department
GROUP BY d.department, d.avg_satisfaction
ORDER BY d.avg_satisfaction DESC;

The column alias list (department, avg_satisfaction) makes the CTE definition readable as an API signature.

Subquery in JOIN

-- Sous-requête dans JOIN : totaliser les heures de formation et joindre aux employés
SELECT
    h.employee_id,
    h.first_name,
    h.last_name,
    t.total_training_hours
FROM hr.hris h
LEFT JOIN (
    SELECT 
        employee_id, 
        SUM(training_hours) AS total_training_hours
    FROM hr.talent_development
    GROUP BY employee_id
) AS t
    ON t.employee_id = h.employee_id
ORDER BY h.employee_id
LIMIT 10;

Subquery in CASE

-- Sous-requête dans CASE : catégoriser les employés au-dessus ou en-dessous de la moyenne
SELECT
    employee_id,
    satisfaction_score,
    CASE 
        WHEN satisfaction_score > (
            SELECT AVG(satisfaction_score)
            FROM hr.engagement_results
        ) THEN 'Above Average'
        ELSE 'At or Below Average'
    END AS score_category
FROM hr.engagement_results
ORDER BY employee_id
LIMIT 10;

Subquery in LATERAL JOIN

-- Sous-requête dans LATERAL JOIN : salaire le plus élevé pour chaque employé
SELECT 
    h.employee_id,
    h.first_name,
    h.last_name,
    c.job_title,
    c.base_salary
FROM hr.hris h
LEFT JOIN LATERAL (
        SELECT *
        FROM hr.radford_compensation c
        WHERE c.employee_id = h.employee_id
        ORDER BY c.base_salary DESC
        LIMIT 1
    ) AS c ON TRUE
ORDER BY h.employee_id;

4.4 Modify data with subqueries

INSERT with subquery

-- Créer et peupler une table des employés à haute satisfaction

DROP TABLE IF EXISTS hr.high_performers;

CREATE TABLE hr.high_performers (
    employee_id INT PRIMARY KEY,
    score DECIMAL(5, 2)
    );

-- Insérer les employés avec des scores supérieurs à la moyenne
INSERT INTO hr.high_performers (employee_id, score)
SELECT DISTINCT employee_id,
       satisfaction_score
FROM hr.engagement_results
WHERE satisfaction_score >=
        (SELECT AVG(satisfaction_score)
         FROM hr.engagement_results);

-- Vérifier les données insérées
SELECT * FROM hr.high_performers;

-- Nettoyage
DROP TABLE IF EXISTS hr.high_performers;

UPDATE with subquery (in a transaction)

-- Mettre à jour HRIS avec le salaire le plus élevé de la table de compensation Radford

BEGIN TRANSACTION;

-- Prévisualiser les valeurs de salaire actuelles
SELECT employee_id, first_name, last_name, salary
FROM hr.hris
ORDER BY employee_id
LIMIT 10;

-- Exécuter la mise à jour avec une sous-requête scalaire
UPDATE hr.hris h
SET salary = (
        SELECT MAX(base_salary)
        FROM hr.radford_compensation c
        WHERE c.employee_id = h.employee_id
    )
WHERE EXISTS (
        SELECT 1
        FROM hr.radford_compensation c
        WHERE c.employee_id = h.employee_id
    );

-- Annuler pour ne pas persister les changements
ROLLBACK;

DELETE with subquery (in a transaction)

-- Supprimer les enregistrements d'engagement sans employé correspondant dans HRIS

BEGIN TRANSACTION;

-- Prévisualiser les lignes qui seront supprimées
SELECT employee_id, satisfaction_score
FROM hr.engagement_results
WHERE employee_id NOT IN (
    SELECT employee_id
    FROM hr.hris
);

-- Exécuter le DELETE avec une sous-requête
DELETE FROM hr.engagement_results e
WHERE NOT EXISTS (
        SELECT 1
        FROM hr.hris h
        WHERE h.employee_id = e.employee_id
    )
RETURNING employee_id, satisfaction_score;

-- Annuler pour éviter les changements permanents
ROLLBACK;

4.5 Other uses of subqueries

-- Trier par la moyenne du département (extension PostgreSQL, pas ANSI standard)
SELECT 
    h.employee_id,
    h.first_name,
    h.last_name
FROM hr.hris h
ORDER BY (
    SELECT AVG(satisfaction_score)
    FROM hr.engagement_results e
    WHERE e.department = h.department
) DESC
LIMIT 10;

-- Prédicats ANY et ALL : score supérieur à TOUS les scores du département Sales
SELECT 
    employee_id,
    satisfaction_score
FROM hr.engagement_results
WHERE satisfaction_score > ALL (
        SELECT satisfaction_score
        FROM hr.engagement_results
        WHERE department = 'Sales'
);

-- Formater le salaire le plus élevé de chaque employé
SELECT 
    h.employee_id,
    to_char(
        (SELECT MAX(base_salary)
         FROM hr.radford_compensation c
         WHERE c.employee_id = h.employee_id),
        'FM999,999'
    ) AS formatted_top_salary
FROM hr.hris h
LIMIT 10;

-- Comparaison de tuples : correspondance sur plusieurs colonnes à la fois
SELECT 
    employee_id, 
    department, 
    "position"
FROM hr.hris
WHERE (department, "position") IN (
        SELECT department, job_title
        FROM hr.engagement_results
        GROUP BY department, job_title
      );

-- Journaliser un snapshot dans une table d'audit (sous-requête scalaire dans VALUES)
CREATE TABLE IF NOT EXISTS hr.audit_log (
    event_time TIMESTAMP,
    event_type VARCHAR(50),
    detail TEXT
);

INSERT INTO hr.audit_log (event_time, event_type, detail)
VALUES (
    NOW(),
    'snapshot_count',
    (SELECT COUNT(*) FROM hr.hris)
);

4.6 Introduction to CTE

Nested subqueries problem

Multi-level nested subqueries become difficult to read and maintain. If the same logic needs to change, it must be found and rewritten in several nested places.

What is a CTE?

A CTE (Common Table Expression) is a modern way of writing subqueries that improves readability and reusability. It uses the WITH clause to define temporary named result sets that only exist for the duration of a single query.

Introduced in SQL-1999, supported in PostgreSQL since version 8.4 (2009).

Structure:

WITH nom_cte (colonne1, colonne2) AS (
    -- Corps du CTE : n'importe quel SELECT valide
    SELECT ...
    FROM ...
    WHERE ...
),

autre_cte AS (
    -- Deuxième CTE
    SELECT ...
    FROM nom_cte  -- peut référencer le CTE précédent
)

-- Instruction finale unique : SELECT, INSERT, UPDATE, DELETE, ou MERGE
SELECT *
FROM nom_cte
JOIN autre_cte USING (colonne1);

Important points:

• Column list is optional — if omitted, names are inherited from the query
• Multiple CTEs separated by commas
• The WITH block must be followed by a single final instruction
• A CTE can be seen as a dynamic temporary view

4.7 CTE to clean up a complex query

The query unreadable (before)

-- Trouver les employés dont les heures d'absence dépassent la moyenne de leur département
-- Version extrêmement difficile à lire
SELECT *
FROM (
    SELECT ea.employee_id,
           ea.employee_name,
           ea.department,
           ea.hours_absent,
           (
               /* NIVEAU 2 : sous-requête imbriquée calculant la moyenne */
               SELECT avg_by_dept
               FROM (
                   /* NIVEAU 3 : agrégat complètement enfoui */
                   SELECT AVG(hours_absent) AS avg_by_dept
                   FROM hr.employee_absence ea_inner
                   WHERE ea_inner.department = ea.department
               ) AS dept_calc
           ) AS dept_avg_absence
    FROM hr.employee_absence ea
) AS outer_query
WHERE hours_absent >
      (
          /* NIVEAU 2 répétition de la même logique */
          SELECT avg_by_dept
          FROM (
              /* NIVEAU 3 répété à nouveau */
              SELECT AVG(hours_absent) AS avg_by_dept
              FROM hr.employee_absence ea_inner2
              WHERE ea_inner2.department = outer_query.department
          ) AS dept_calc2
      )
ORDER BY department, hours_absent DESC;

The clear version with CTE (after)

-- Version améliorée pour lisibilité et efficacité avec CTEs
WITH dept_avgs AS (
    /* Étape 1 : calculer les moyennes par département une seule fois */
    SELECT department,
           AVG(hours_absent) AS avg_absent
    FROM hr.employee_absence
    GROUP BY department
),
joined AS (
    /* Étape 2 : attacher la moyenne du département à chaque employé */
    SELECT ea.employee_id,
           ea.employee_name,
           ea.department,
           ea.hours_absent,
           da.avg_absent AS dept_avg_absence
    FROM hr.employee_absence ea
    JOIN dept_avgs da
      ON ea.department = da.department
)
SELECT *
FROM joined
WHERE hours_absent > dept_avg_absence
ORDER BY department, hours_absent DESC;

Core Value of CTE: Taking multi-step logic that would otherwise be hidden in nested subqueries and transforming it into modular, readable SQL that is easy to reason about and modify.

4.8 CTE to respect the DRY principle

DRY = Don’t Repeat Yourself

DRY violation — averaging repeated

-- Violation DRY : la sous-requête scalaire est dupliquée
SELECT e.employee_id,
       (SELECT AVG(satisfaction_score)
        FROM hr.engagement_results) AS avg_score,
       e.satisfaction_score
FROM hr.engagement_results e
WHERE e.satisfaction_score >
      (SELECT AVG(satisfaction_score)
       FROM hr.engagement_results);

Correction with CTE

-- DRY corrigé avec CTE : calculer la moyenne une seule fois
WITH avg_scores AS (
    SELECT AVG(e.satisfaction_score) AS avg_score
    FROM hr.engagement_results e
)

SELECT e.employee_id,
       a.avg_score,
       e.satisfaction_score
FROM hr.engagement_results e
CROSS JOIN avg_scores a
WHERE e.satisfaction_score > a.avg_score;

Reducing repetition of expressions

-- Violation DRY : même calcul répété trois fois
SELECT ea.employee_id,
       SUM(ea.hours_absent) AS total_hours_absent,
       SUM(ea.hours_absent) / COUNT(*) AS avg_hours_absent,
       CASE
           WHEN SUM(ea.hours_absent) / COUNT(*) > 1 THEN 'High'
           ELSE 'Normal'
       END AS absence_category
FROM hr.employee_absence ea
WHERE ea.department = 'Sales'
GROUP BY ea.employee_id
HAVING SUM(ea.hours_absent) / COUNT(*) > 1;

-- DRY corrigé avec CTE
WITH sales AS (
    SELECT employee_id, hours_absent
    FROM hr.employee_absence
    WHERE department = 'Sales'
),

agg AS (
    SELECT employee_id,
           SUM(hours_absent) AS total_hours_absent,
           AVG(hours_absent) AS avg_hours_absent
    FROM sales
    GROUP BY employee_id
)

SELECT employee_id,
       total_hours_absent,
       avg_hours_absent,
       CASE
           WHEN avg_hours_absent > 1 THEN 'High'
           ELSE 'Normal'
       END AS absence_category
FROM agg
WHERE avg_hours_absent > 1
ORDER BY avg_hours_absent DESC;

CTE to isolate a business rule in an UPDATE

-- Logique métier enfouie dans le WHERE du UPDATE
BEGIN TRANSACTION;
UPDATE hr.hris h
SET salary = h.salary * 1.05
WHERE h.employee_id IN (
    SELECT ea.employee_id
    FROM hr.employee_absence ea
    WHERE ea.department = 'Customer Support'
    GROUP BY ea.employee_id
    HAVING SUM(ea.hours_absent) > 1
);
ROLLBACK;

-- Règle métier isolée dans un CTE pour réutilisation
BEGIN TRANSACTION;
WITH high_absence AS (
    SELECT ea.employee_id
    FROM hr.employee_absence ea
    WHERE ea.department = 'Customer Support'
    GROUP BY ea.employee_id
    HAVING SUM(ea.hours_absent) > 1
)

UPDATE hr.hris h
SET salary = h.salary * 1.05
WHERE h.employee_id IN (SELECT employee_id FROM high_absence);
ROLLBACK;

4.9 Recursive CTE

Concept

A recursive CTE allows you to express hierarchical or iterative logic in SQL: traversing an organization chart, exploding a BOM, generating sequences, or traversing parent-child structures.

Standardized in SQL-1999, fully supported in PostgreSQL.

Mandatory structure of a recursive CTE

WITH RECURSIVE nom_cte AS (
    -- Membre ancre : requête normale SANS auto-référence
    -- Produit les premières lignes (point de départ)
    SELECT ...
    
    UNION ALL  -- UNION ALL typiquement (préserve les doublons, plus efficace)
    
    -- Membre récursif : référence le CTE par son nom
    -- Produit le niveau suivant de lignes
    SELECT ...
    FROM nom_cte  -- auto-référence exactement une fois
    WHERE condition_terminaison  -- OBLIGATOIRE pour arrêter la récursion
)
SELECT * FROM nom_cte;

Structural requirements:

1. WITH RECURSIVE is required in PostgreSQL
2. An anchor member (non-self-referential) and a recursive member (self-referential)
3. Combined with UNION or UNION ALL (UNION ALL typically)
4. The recursive member references the CTE exactly once in its FROM clause
5. A mandatory termination condition — infinite recursive CTEs are not supported

Minimal example — generate a sequence

-- CTE récursif pour générer une séquence de nombres de 1 à 10
WITH RECURSIVE nums AS (

    -- Membre ancre : démarrer la série
    SELECT 1 AS n

    UNION ALL

    -- Membre récursif : générer la valeur suivante
    SELECT n + 1
    FROM nums
    WHERE n < 10
)
SELECT *
FROM nums;

Traverse an organization chart

-- Requête récursive d'organigramme
WITH RECURSIVE org AS (

    -- Ancre : commencer au directeur (aucun manager)
    SELECT
        h.employee_id,
        h.first_name,
        h.last_name,
        h.position,
        h.manager_id,
        0 AS level
    FROM hr.hris h
    WHERE h.manager_id IS NULL

    UNION ALL

    -- Membre récursif : trouver les subordonnés directs de chaque ligne
    SELECT
        e.employee_id,
        e.first_name,
        e.last_name,
        e.position,
        e.manager_id,
        org.level + 1 AS level
    FROM hr.hris e
    JOIN org ON e.manager_id = org.employee_id
)
SELECT *
FROM org
ORDER BY level, employee_id;

PostgreSQL evaluates the anchor first, then repeatedly invokes the recursive member until it no longer produces new rows. The result is a complete traverse of the organization chart — the director at the top, then all the managers, then all their subordinates.

4.10 Advanced Recursive CTE Options

These features of PostgreSQL are not necessary for most hierarchical queries, but are useful to know.

SEARCH clause — control traversal order

• SEARCH BREADTH FIRST: returns the hierarchy level by level (mirror of the natural structure of an organization chart)
• SEARCH DEPTH FIRST: follows a branch to the bottom before moving on to the next

Most database engines do not implement this — it is an advanced feature unique to PostgreSQL.

CYCLE clause — detect loops

Detects circular loops in a hierarchy. In a well-formed flowchart, a cycle should not exist, but in some data sets taken from operational systems, incorrect data can create circular reporting chains. The CYCLE clause allows you to mark or handle these cases properly.

5. Combine and validate results

5.1 Query combination techniques

In real SQL analytical work, one never relies on a single construct. Real HR questions — participation rates, training funnels, engagement patterns — almost always require several steps. JOINs, subqueries, CTEs and set operations end up working together.

The multi-stage pipeline

A multi-stage query pipeline typically follows this pattern:

1. Step 1 — Filtering CTE: truncate the dataset to active or relevant data (carry only what is actually needed)
2. Step 2 — Contextual JOIN: join the filtered dataset to other tables to add the required context
3. Step 3 — Set operation: merge or compare results with UNION, INTERSECT, or EXCEPT

Set operations always require the same number of columns and compatible types on both sides of the operator.

5.2 Multi-stage pipeline

-- Étape 1 : Filtrer dans un CTE
WITH active_employees AS (
    SELECT employee_id, department
    FROM hr.hris
    WHERE termination_date IS NULL
),

-- Étape 2 : Joindre pour ajouter du contexte
trained AS (
    SELECT a.employee_id, a.department
    FROM active_employees a
    JOIN hr.talent_development td
    ON a.employee_id = td.employee_id
    WHERE td.training_hours > 0
)

/* -- SQL comment sandwich pour tester les résultats intermédiaires
SELECT * FROM active_employees;
-- */

-- Étape 3 : Comparer les populations avec une opération ensembliste
SELECT employee_id
FROM trained
INTERSECT    -- Trouver les employés qui sont aussi dans les résultats d'engagement
-- UNION ALL       -- Trouver tous les employés en formation et dans les engagements
-- EXCEPT          -- Trouver les employés en formation mais pas dans les engagements
SELECT employee_id
FROM hr.engagement_results;

Tip — SQL how to sandwich: To test intermediate results, wrap the SELECT of a CTE in a comment block. By unchecking the start of the comment, the SELECT becomes available and can be executed to validate this CTE. This is a useful manual unit testing technique in SQL.

5.3 JOIN with aggregation

Solve a realistic HR request: Summarize training participation and engagement levels by department.

-- Résumé de formation et engagement par département
WITH dept_training AS (
    SELECT 
        h.department,
        COUNT(*) AS employees_in_dept,
        COUNT(*) FILTER (WHERE td.training_hours > 0) AS trained_count
    FROM hr.hris h
    LEFT JOIN hr.talent_development td
      ON h.employee_id = td.employee_id
    WHERE h.termination_date IS NULL
    GROUP BY h.department
),

-- Agréger les scores d'engagement par département
dept_engagement AS (
    SELECT 
        department,
        AVG(satisfaction_score) AS avg_satisfaction,
        AVG(work_life_balance_score) AS avg_work_life,
        AVG(career_growth_score) AS avg_growth
    FROM hr.engagement_results
    GROUP BY department
)

-- JOIN final pour combiner formation et engagement
SELECT 
    dt.department,
    dt.employees_in_dept,
    dt.trained_count,
    ROUND(dt.trained_count::numeric / NULLIF(dt.employees_in_dept, 0), 3) 
        AS training_rate,
    de.avg_satisfaction,
    de.avg_work_life,
    de.avg_growth
FROM dept_training dt
LEFT JOIN dept_engagement de USING (department)
ORDER BY dt.department;

Structure: training on one side, engagement on the other — each piece of logic isolated. The final JOIN becomes a simple operation that targets a named set.

5.4 Validation of complex queries

When several steps are superimposed (CTEs, JOIN, aggregations), errors are easily hidden. A single bad JOIN predicate or misplaced filter can inflate counts or delete entire rows, and aggregations can make troubleshooting even more difficult.

Structured validation process

/* Étape 1 : Valider le résumé de formation par département */
WITH dept_training (
    department,
    employees_in_dept,
    trained_count
) AS (
    SELECT 
        h.department,
        COUNT(*),
        COUNT(*) FILTER (WHERE td.training_hours > 0)
    FROM hr.hris h
    LEFT JOIN hr.talent_development td
      ON h.employee_id = td.employee_id
    WHERE h.termination_date IS NULL
    GROUP BY h.department
),

/* Décommenter ce bloc pour valider dept_training (supprimer la virgule précédente)
SELECT * FROM dept_training ORDER BY department;
*/

/* Étape 2 : Valider le résumé d'engagement */
dept_engagement (
    department,
    avg_satisfaction,
    avg_work_life,
    avg_growth
) AS (
    SELECT 
        department,
        AVG(satisfaction_score),
        AVG(work_life_balance_score),
        AVG(career_growth_score)
    FROM hr.engagement_results
    GROUP BY department
)

/* Décommenter ce bloc pour valider dept_engagement
SELECT * FROM dept_engagement ORDER BY department;
*/

-- Étape 3 : Valider le JOIN final et les agrégats
SELECT 
    dt.department,
    dt.employees_in_dept,
    dt.trained_count,
    ROUND(dt.trained_count::numeric / NULLIF(dt.employees_in_dept, 0), 3)
        AS training_rate,
    de.avg_satisfaction,
    de.avg_work_life,
    de.avg_growth
FROM dept_training dt
LEFT JOIN dept_engagement de USING (department)
ORDER BY dt.department;

What to check at each step

5.5 PostgreSQL execution plans — EXPLAIN and EXPLAIN ANALYZE

EXPLAIN without ANALYZE

Shows PostgreSQL’s intended plan: its intended path, expected row counts, and relative costs. Useful for teaching and exploring.

-- Analyser la participation à la formation et les scores d'engagement
EXPLAIN --ANALYZE
WITH dept_training (
    department,
    employees_in_dept,
    trained_count
) AS (
    SELECT 
        h.department,
        COUNT(*),
        COUNT(*) FILTER (WHERE td.training_hours > 0)
    FROM hr.hris h
    LEFT JOIN hr.talent_development td
      ON h.employee_id = td.employee_id
    WHERE h.termination_date IS NULL
    GROUP BY h.department
),
dept_engagement (
    department,
    avg_satisfaction,
    avg_work_life,
    avg_growth
) AS (
    SELECT 
        department,
        AVG(satisfaction_score),
        AVG(work_life_balance_score),
        AVG(career_growth_score)
    FROM hr.engagement_results
    GROUP BY department
)
SELECT 
    dt.department,
    dt.employees_in_dept,
    dt.trained_count,
    ROUND(dt.trained_count::numeric / NULLIF(dt.employees_in_dept, 0), 3),
    de.avg_satisfaction,
    de.avg_work_life,
    de.avg_growth
FROM dept_training dt
LEFT JOIN dept_engagement de USING (department)
ORDER BY dt.department;

Read a plan EXPLAIN

The returned plan is an operation tree — each row shows a node in the execution plan, starting with the innermost data access and working back to the final result.

What we are looking for in the plan:

EXPLAIN ANALYZE — real timings

With ANALYZE, PostgreSQL actually executes** the query and reports:

• Actual row counts (vs. estimates)
• Actual execution timings
• Filters applied and rows deleted

Warning signals:

If the estimates and actuals align closely, the JOINs are clean, the grouping is correct, and the pipeline is stable.

Common Errors in CTEs Combining JOIN and SET Operations

Column error in CTE JOIN

-- Corriger un JOIN cassé — noms de colonnes incompatibles entre CTEs
WITH training_summary (
    department,
    trained_count
) AS (
    SELECT 
        h.department,
        COUNT(*)
    FROM hr.hris h
    JOIN hr.talent_development td
      ON h.employee_id = td.employee_id
    GROUP BY h.department
),

engagement_summary (
    dept,            -- nom de colonne incompatible !
    avg_score
) AS (
    SELECT 
        e.department,
        AVG(e.satisfaction_score)
    FROM hr.engagement_results e
    GROUP BY e.department
)

SELECT 
    t.department,
    t.trained_count,
    e.avg_score
FROM training_summary t
JOIN engagement_summary e
  ON t.department = e.department;   -- ❌ la colonne 'dept' n'est pas 'department'

UNION error in a CTE

-- Corriger un UNION cassé — types incompatibles et noms mal assortis
WITH training_summary AS (
    SELECT
        h.department,
        COUNT(*)::numeric  -- cast explicite pour compatibilité de type
    FROM hr.talent_development td
    JOIN hr.hris h
      ON td.employee_id = h.employee_id
    GROUP BY h.department
),

-- NULL sans cast de type causera une erreur
engagement_summary AS (
    SELECT
        e.department,
        NULL --::NUMERIC  -- décommenter pour corriger
    FROM hr.engagement_results e
    GROUP BY e.department
)

SELECT * FROM training_summary
UNION
SELECT * FROM engagement_summary;

6. Setting up the course database

The course database runs in a Docker container. The backup file backup.dump can be restored with the following command:

pg_restore -U <user> -d <database> --clean --create backup.dump

Replace <user> with the PostgreSQL user name and <database> with the name of the target database.

Structure of the hr schema tables

All tables connect via employee_id as shared foreign key.

Documentation generated from training transcripts and SQL demo files.

Search Terms

join · combine · data · sql · postgresql · databases · cte · subquery · set · subqueries · operations · explain · joins · analyze · clause · concept · expressions · query · recursive · union · case · columns · combination · combining