Search⌘ K
Home
Courses
Mastering Advanced SQL Techniques with MySQL

Common Table Expressions

Explore how common table expressions (CTEs) simplify complex SQL queries through clearer, more organized code. Understand the syntax and use of CTEs to improve readability and maintainability when working with nested subqueries and derived tables in MySQL.

We'll cover the following...

In our running example, we consider car models and the assembly of their parts. In SQL, we modeled this example through a table called CarModel that we populated with sample cars:

MySQL
-- Generate a table for sample car models.
DROP TABLE IF EXISTS CarModel;
CREATE TABLE CarModel
(
    id           INT auto_increment,
    manufacturer TEXT DEFAULT NULL,
    name         TEXT DEFAULT NULL,
    `power (kW)` INT  DEFAULT NULL,
    PRIMARY KEY (id)
);
-- Populate the generated table with sample car models.
INSERT INTO CarModel (manufacturer, name, `power (kW)`)
VALUES ('Audi', 'A3', 81),
       ('Audi', 'A4', 100),
       ('Audi', 'A5', 110),
       ('Audi', 'A6', 210),
       ('Audi', 'A7', 150),
       ('Audi', 'A8', 210),
       ('Mercedes Benz', 'C63 AMG', 350),
       ('Mercedes Benz', 'S63 AMG', 450),
       ('Volkswagen', 'Golf VII', 92),
       ('Volkswagen', 'Golf VIII', 110);
TABLE CarModel;

Since these cars are assembled from different parts, we also record these in a separate table called CarPart. This also allows us to oversee our assembly progress. As the database engineer, we propose the following data model for the car parts:

MySQL
-- Generate a table for sample car model parts.
DROP TABLE IF EXISTS CarPart;
CREATE TABLE CarPart
(
    model    INT,
    name     TEXT,
    built_at DATE DEFAULT NULL,
    PRIMARY KEY (model, name(256)),
    FOREIGN KEY (model) REFERENCES CarModel (id)
);

The database table CarPart consists of three columns, namely model, name, and built_at. The first column, model, references the car model that this part belongs to as a foreign key (i.e., FOREIGN KEY (model) REFERENCES CarModel (id)). name records the name of the car part and constitutes the primary key of CarPart together with model (i.e., PRIMARY KEY (model, name(256))). For our example, we ...