Write Complex Subqueries in SQL

Table of Contents

1. Introduction to complex subqueries
2. The two basic types of subqueries

• 2.1 Non-correlated subqueries
• 2.2 Correlated subqueries
• 2.3 Comparison non-correlated vs correlated

3. Demo: Filtering and comparison with correlated subqueries

• 3.1 Database context and schema
• 3.2 Show total orders per customer (non-correlated)
• 3.3 Count orders per individual customer (correlated)

4. Demo: Subqueries in the SELECT clause

• 4.1 Database context and schema
• 4.2 Total quantity sold per product
• 4.3 Total revenue per product
• 4.4 Percentage of total sales by product
• 4.5 Product ranking by total income

5. Demo: Subqueries in the FROM clause

• 5.1 Total quantity sold per product via subquery in FROM
• 5.2 Sales summary by category via subquery in FROM
• 5.3 Total and average sales per product via subquery in FROM

6. Demo: Subqueries in the WHERE clause

• 6.1 Database context and schema
• 6.2 Orders exceeding the global average
• 6.3 Orders exceeding customer average by 20%
• 6.4 Customers whose order total exceeds the general average

7. Comparison of subqueries according to SQL platforms

• 7.1 Common support for all platforms
• 7.2 SQL Server
• 7.3 PostgreSQL
• 7.4 MySQL
• 7.5 Scalar subqueries and multi-line error handling

8. Key Takeaways
9. Demo files

1. Introduction to complex subqueries

subqueries are essential for performing complex filtering, aggregation, and comparisons that go beyond the capabilities of basic SQL JOIN. They allow queries to be nested inside other queries, providing a flexible way to break down complex logic into manageable steps.

Unlike JOIN, subqueries allow you to:

• Calculate aggregated values dynamically for each row in the external query.
• Filter results based on on-the-fly calculations.
• Organize complex calculations as temporary tables.
• Perform comparisons between an individual row and aggregated values ​​of the entire dataset.

2. The two basic types of subqueries

Before writing complex subqueries, it is crucial to understand the two main types: non-correlated subqueries and correlated subqueries.

2.1 Non-correlated subqueries

A non-correlated subquery is a subquery that executes independently of the main query. It acts as a temporary dataset whose result is calculated once, then reused for each row of the external query.

Features:

• Runs once for the entire main query.
• Produces the same result for all rows in the external query.
• Makes no reference to the columns in the external query.
• Useful for calculating overall values ​​(grand average, grand total, etc.).

Conceptual example: Calculate the total number of orders in the system and display this constant value next to each customer.

2.2 Correlated subqueries

A correlated subquery is a subquery that depends on the external query data to produce its results. It references at least one column of the external query, which makes it dynamic.

Features:

• Executes once for each row of the external query.
• Produces a unique result for each row.
• References the columns of the external query in its WHERE or SELECT clause.
• More powerful but potentially more expensive in performance.

Conceptual example: Count the number of orders for each individual customer by dynamically filtering orders based on the current customer’s ID.

2.3 Comparison non-correlated vs correlated

3. Demo: Filtering and comparison with correlated subqueries

3.1 Database context and schema

The demonstration uses the SalesDemo database composed of two tables:

Customers table:

Table Orders:

Creation and initialization script:

-- Création de la base de données
CREATE DATABASE SalesDemo;
USE SalesDemo;

-- Création de la table Customers
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    City VARCHAR(100)
);

-- Création de la table Orders
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    OrderTotal DECIMAL(10, 2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Insertion des données dans Customers
INSERT INTO Customers (CustomerID, CustomerName, City)
VALUES 
    (1, 'John Doe', 'New York'),
    (2, 'Jane Smith', 'Los Angeles'),
    (3, 'Alice Johnson', 'Chicago'),
    (4, 'Mike Brown', 'Houston');

-- Insertion des données dans Orders
INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderTotal)
VALUES 
    (1, 1, '2023-08-01', 250.00),
    (2, 1, '2023-08-10', 150.00),
    (3, 2, '2023-08-05', 400.00),
    (4, 2, '2023-09-12', 350.00),
    (5, 3, '2023-09-10', 600.00),
    (6, 4, '2023-09-15', 700.00);

3.2 Show total orders per customer (non-correlated)

Purpose: Display the total number of orders in the system next to each customer. This number is the same for all clients because the subquery runs only once.

Key points:

• The non-correlated subquery displays the order total next to each customer.
• Subquery executes only once; the value is the same for all lines.

-- ===============================================
-- Total Number of Orders Alongside Each Customer
-- ===============================================

-- Key Takeaways:
-- - Non-correlated subquery displays total orders alongside each customer.
-- - Subquery runs once; same value for all rows.

SELECT 
    CustomerID, 
    CustomerName, 
    City,
    (SELECT COUNT(*) FROM Orders) AS TotalNumberOfOrders
FROM Customers;

Expected result: Each row in the Customers table displays the same number in the TotalNumberOfOrders column, because the subquery calculates the overall total only once.

3.3 Count orders per individual customer (correlated)

Objective: Count the number of orders for each customer individually. The subquery references the CustomerID of the external query, making it a correlated subquery.

Key points:

• The correlated subquery counts orders per customer using COUNT.
• The subquery references the CustomerID of the external query and demonstrates the difference with a non-correlated subquery.

-- ===============================================
-- Number of Orders per Customer
-- ===============================================

-- Key Takeaways:
-- - Correlated subquery counts orders per customer using COUNT.
-- - Subquery references outer query's CustomerID;
--   demonstrates difference from non-correlated subquery.

SELECT 
    C.CustomerID, 
    C.CustomerName, 
    C.City,
    (SELECT COUNT(*)
     FROM Orders O
     WHERE O.CustomerID = C.CustomerID) AS NumberOfOrders
FROM Customers C;

Expected result: Each customer displays its own number of orders. The subquery runs for each row of Customers, filtering orders with WHERE O.CustomerID = C.CustomerID.

Environment cleanup:

-- Supprimer la base de données de démonstration
DROP DATABASE SalesDemo;

4. Demo: Subqueries in the SELECT clause

In this demonstration, we explore how subqueries in the SELECT clause allow us to analyze product sales and revenue.

4.1 Database context and schema

The demonstration uses the SalesAnalytics database composed of two tables:

Table Products:

Table Sales:

Creation and initialization script:

-- Création de la base de données
CREATE DATABASE SalesAnalytics;
USE SalesAnalytics;

-- Création de la table Products
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Category VARCHAR(50),
    Price DECIMAL(10, 2)
);

-- Création de la table Sales
CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    ProductID INT,
    SaleDate DATE,
    Quantity INT,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Insertion des données dans Products
INSERT INTO Products (ProductID, ProductName, Category, Price)
VALUES 
    (1, 'Laptop', 'Electronics', 1200.00),
    (2, 'Smartphone', 'Electronics', 800.00),
    (3, 'Desk Chair', 'Furniture', 150.00),
    (4, 'Coffee Table', 'Furniture', 200.00),
    (5, 'Headphones', 'Electronics', 150.00);

-- Insertion des données dans Sales
INSERT INTO Sales (SaleID, ProductID, SaleDate, Quantity)
VALUES 
    (1, 1, '2023-08-01', 5),
    (2, 2, '2023-08-03', 10),
    (3, 1, '2023-08-10', 3),
    (4, 3, '2023-09-01', 7),
    (5, 4, '2023-09-15', 4),
    (6, 2, '2023-09-20', 6),
    (7, 5, '2023-10-05', 15),
    (8, 5, '2023-10-10', 10);

4.2 Total quantity sold per product

Objective: Calculate the total quantity sold for each product using a correlated subquery in the SELECT clause.

Key points:

• The correlated subquery in SELECT calculates the total quantity per product using SUM.
• It allows comparative analysis by calculating totals specific to each product.

-- ===============================================
-- Total Quantity Sold Per Product
-- ===============================================

-- Key Takeaways:
-- - Correlated subquery in SELECT computes total quantity per product using SUM.
-- - Enables comparative analysis by calculating totals specific to each product.

SELECT 
    P.ProductID,
    P.ProductName,
    P.Category,
    P.Price,
    (SELECT SUM(S.Quantity)
     FROM Sales S
     WHERE S.ProductID = P.ProductID) AS TotalQuantitySold
FROM Products P;

Expected result: For each product, the TotalQuantitySold column displays the sum of all quantities sold for that specific product. The subquery runs for each row of Products and sums the corresponding quantities in Sales.

4.3 Total revenue by product

Objective: Calculate the total revenue generated by each product by multiplying the quantity sold by the product price.

Key points:

• Correlated subquery calculates total revenue per product with SUM and multiplication.
• It combines data from external query and subquery, which is essential for financial analysis.

-- ===============================================
-- Total Revenue Per Product
-- ===============================================

-- Key Takeaways:
-- - Correlated subquery calculates total revenue per product
--   with SUM and multiplication.
-- - Combines outer query and subquery data;
--   essential for financial analysis.

SELECT 
    P.ProductID,
    P.ProductName,
    P.Category,
    P.Price,
    (SELECT SUM(S.Quantity * P.Price)
     FROM Sales S
     WHERE S.ProductID = P.ProductID) AS TotalRevenue
FROM Products P;

Expected result: High priced products like Laptop generate more revenue. The subquery multiplies Quantity (from Sales) by Price (from Products, provided by the external query) for each product.

4.4 Percentage of total sales by product

Objective: Determine the percentage of total sales contributed by each product. This example combines correlated and non-correlated subqueries.

Key points:

• Nested subqueries calculate each product’s percentage of total sales.
• Combines correlated and uncorrelated subqueries for complex metrics like percentages.

-- ===============================================
-- Percentage of Total Sales for Each Product
-- ===============================================

-- Key Takeaways:
-- - Nested subqueries calculate each product's percentage of total sales.
-- - Combines correlated and non-correlated subqueries;
--   demonstrates arithmetic operations.

SELECT 
    P.ProductID,
    P.ProductName,
    P.Category,
    P.Price,
    -- Quantité totale vendue par produit (correlated)
    (SELECT SUM(S.Quantity)
     FROM Sales S
     WHERE S.ProductID = P.ProductID) AS TotalQuantitySold,
    -- Pourcentage des ventes totales (correlated + non-correlated)
    ((SELECT SUM(S.Quantity)
      FROM Sales S
      WHERE S.ProductID = P.ProductID) * 100.0 /
     (SELECT SUM(S2.Quantity)
      FROM Sales S2)) AS PercentageOfTotalSales
FROM Products P;

Expected result: Each product displays its percentage contribution to overall sales. The first subquery (correlated) calculates sales by product, and the second (non-correlated) calculates the overall total. Division produces the percentage.

4.5 Product Ranking by Total Revenue

Objective: Rank products based on their total revenue by comparing each product to the others.

Key points:

• Correlated subqueries rank products by total revenue.
• Implements comparative ranking logic; complexity can impact performance.

-- ===============================================
-- Rank Products Based on Total Revenue
-- ===============================================

-- Key Takeaways:
-- - Correlated subqueries rank products by total revenue.
-- - Implements comparative analysis;
--   complexity may impact performance.

SELECT 
    P.ProductID,
    P.ProductName,
    P.Category,
    P.Price,
    -- Revenu total par produit
    (SELECT SUM(S.Quantity * P.Price)
     FROM Sales S
     WHERE S.ProductID = P.ProductID) AS TotalRevenue,
    -- Rang basé sur le revenu
    (SELECT COUNT(DISTINCT P2.ProductID)
     FROM Products P2
     WHERE (SELECT SUM(S2.Quantity * P2.Price)
            FROM Sales S2
            WHERE S2.ProductID = P2.ProductID) >=
           (SELECT SUM(S.Quantity * P.Price)
            FROM Sales S
            WHERE S.ProductID = P.ProductID)) AS RevenueRank
FROM Products P
ORDER BY TotalRevenue DESC;

Expected result: Products Laptop and Smartphone occupy the top positions in the ranking due to their higher total revenue. Rank is calculated by counting how many products have revenue greater than or equal to the current product.

Environment cleanup:

DROP DATABASE SalesAnalytics;

5. Demo: Subqueries in the FROM clause

In this demonstration, we explore how subqueries in the FROM clause allow us to analyze sales data. The subquery in FROM acts as a temporary table (also called derived table or inline view) on which the main query can perform joins and aggregations.

This demonstration uses the same SalesAnalytics database with the Products and Sales tables described in the previous section.

5.1 Total quantity sold per product via subquery in FROM

Objective: Calculate the total quantity sold for each product using a subquery in the FROM clause as a temporary table.

Key points:

• The subquery in the FROM clause calculates the total quantity per product.
• It treats the subquery result as a temporary table for the main query.

-- ===============================================
-- Total Quantity Sold Per Product
-- Using Subquery in FROM Clause
-- ===============================================

-- Key Takeaways:
-- - Subquery in FROM clause calculates total quantity per product.
-- - Treats subquery result as a temporary table for main query.

SELECT 
    P.ProductID,
    P.ProductName,
    P.Category,
    P.Price,
    SQ.TotalQuantitySold
FROM Products P
JOIN (
    SELECT ProductID, SUM(Quantity) AS TotalQuantitySold
    FROM Sales
    GROUP BY ProductID
) AS SQ ON P.ProductID = SQ.ProductID;

Expected result: The SQ subquery first calculates the sum of the quantities sold by ProductID. The main query then performs a JOIN between Products and this temporary table to display the product name, category, price, and total sold.

5.2 Summary of sales by category via subquery in FROM

Objective: Calculate total sales and total revenue by product category. The subquery aggregates sales data at the product level, then the main query groups by category.

Key points:

• The subquery aggregates sales data grouped by product.
• Main query joins with Products to summarize by category.
• The join between the subquery and the external query is dynamic: each time a row changes, the join updates with the new data produced by the subquery.

-- ===============================================
-- Sales Summary by Category
-- Using Subquery in FROM Clause
-- ===============================================

-- Key Takeaways:
-- - Subquery aggregates sales data grouped by product.
-- - Main query joins with Products to summarize by category.

SELECT 
    P.Category,
    SUM(SQ.TotalQuantitySold) AS TotalQuantitySold,
    SUM(SQ.TotalRevenue) AS TotalRevenue
FROM Products P
JOIN (
    SELECT 
        S.ProductID, 
        SUM(S.Quantity) AS TotalQuantitySold, 
        SUM(S.Quantity * P.Price) AS TotalRevenue
    FROM Sales S
    JOIN Products P ON S.ProductID = P.ProductID
    GROUP BY S.ProductID
) AS SQ ON P.ProductID = SQ.ProductID
GROUP BY P.Category;

Expected result: The results are grouped by category (Electronics, Furniture), displaying for each the total quantity sold and the total revenue generated. This dynamic behavior adds flexibility to perform queries that would be difficult without the correlated subqueries in the FROM clause.

5.3 Total and average sales per product via subquery in FROM

Objective: Calculate both total sales and average sales per product. The subquery performs two calculations simultaneously.

Key points:

• The subquery calculates total sales (by multiplying quantity sold by price) and average sales.
• Demonstrates how the subquery references columns from the subquery itself and the external query.
• As the external query updates, the subquery results change dynamically for each product.

-- ===============================================
-- Total Sales and Average Sales per Product
-- Using Subquery in FROM Clause
-- ===============================================

-- Key Takeaways:
-- - Subquery calculates total and average sales per product.
-- - Demonstrates organizing complex calculations in FROM clause.

SELECT 
    P.ProductID,
    P.ProductName,
    P.Category,
    P.Price,
    SA.TotalSales,
    SA.AverageSales
FROM Products P
JOIN (
    SELECT 
        S.ProductID, 
        SUM(S.Quantity * P.Price) AS TotalSales, 
        AVG(S.Quantity * P.Price) AS AverageSales
    FROM Sales S
    JOIN Products P ON S.ProductID = P.ProductID
    GROUP BY S.ProductID
) AS SA ON P.ProductID = SA.ProductID;

Expected result: For each product, the TotalSales and AverageSales columns display the total sales in monetary value and the average sales per transaction, respectively. The dynamic join between the subquery and the external query allows for sophisticated calculations that reflect up-to-date values ​​for each product.

Environment cleanup:

DROP DATABASE SalesAnalytics;

6. Demo: Subqueries in the WHERE clause

In this demonstration, we explore how subqueries in the WHERE clause allow us to filter and compare data efficiently.

6.1 Database context and schema

The demonstration uses the SalesDemo database with extended Customers and Orders tables:

Creation and initialization script:

-- Création de la base de données
CREATE DATABASE SalesDemo;
USE SalesDemo;

-- Création de la table Customers
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    City VARCHAR(100)
);

-- Création de la table Orders
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    OrderTotal DECIMAL(10, 2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Insertion des données dans Customers
INSERT INTO Customers (CustomerID, CustomerName, City)
VALUES 
    (1, 'John Doe', 'New York'),
    (2, 'Jane Smith', 'Los Angeles'),
    (3, 'Alice Johnson', 'Chicago'),
    (4, 'Mike Brown', 'Houston'),
    (5, 'Emma Davis', 'Phoenix'),
    (6, 'David Wilson', 'New York');

-- Insertion des données dans Orders
INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderTotal)
VALUES 
    (1, 1, '2023-08-01', 2500.00),
    (2, 1, '2023-08-10', 1500.00),
    (3, 2, '2023-08-05', 4000.00),
    (4, 2, '2023-09-12', 3500.00),
    (5, 3, '2023-09-10', 6000.00),
    (6, 4, '2023-09-15', 7000.00),
    (7, 5, '2023-10-01', 2000.00),
    (8, 5, '2023-10-05', 3000.00),
    (9, 6, '2023-08-15', 4500.00);

6.2 Orders whose amount exceeds the overall average

Objective: Retrieve all orders whose total amount exceeds the overall order average. The subquery in WHERE calculates this average.

Key points:

• The subquery in the WHERE clause calculates the average of the order total.
• The main query filters orders whose OrderTotal exceeds this average.

-- ===============================================
-- Orders with Amount Above Average
-- ===============================================

-- Key Takeaways:
-- - Subquery in WHERE clause calculates average order total.
-- - Filters orders where OrderTotal exceeds the average.

SELECT 
    OrderID,
    CustomerID,
    OrderDate,
    OrderTotal
FROM 
    Orders
WHERE 
    OrderTotal > (SELECT AVG(OrderTotal) FROM Orders);

Expected result: Only orders whose OrderTotal is greater than the overall average appear in the results. This is a non-correlated subquery because AVG(OrderTotal) is calculated only once for all orders.

6.3 Orders exceeding customer average by 20%

Objective: Find orders that exceed the applicable customer’s order average by 20%. It is a correlated subquery because it depends on the CustomerID of the external query.

Key points:

• The subquery in the WHERE clause calculates the average of the total orders per customer.
• The query filters orders that exceed this average by 20%.
• This is a correlated subquery: the WHERE condition changes for each customer row according to their individual order history.

-- ===============================================
-- Orders Exceeding Customer's Average by 20%
-- ===============================================

-- Key Takeaways:
-- - Subquery in WHERE clause calculates average order total per customer.
-- - Filters orders exceeding customer's average by 20%.

SELECT 
    O.OrderID,
    O.CustomerID,
    O.OrderDate,
    O.OrderTotal
FROM 
    Orders O
WHERE 
    O.OrderTotal > (
        SELECT AVG(O2.OrderTotal) * 1.2
        FROM Orders O2
        WHERE O2.CustomerID = O.CustomerID
    );

Expected Result: The results show orders that are unusually high in value compared to the customer’s typical spending habits. The dynamic nature of the correlated subquery is fundamental here: for each line of Orders O, the subquery recalculates the average specific to this customer.

6.4 Customers whose total orders exceed the overall average

Objective: Find customers whose cumulative order total exceeds the overall average of totals per customer. This example uses nested subqueries.

Key points:

• The first subquery calculates the overall average order total per customer.
• The main query filters customers whose order totals exceed this average.
• The nested subquery is also correlated because it uses the CustomerID of the external query to dynamically calculate the total orders per customer.
• This approach helps identify high-value customers who regularly place large orders.

-- ===============================================
-- Customers Whose Total Orders Exceed Overall Average
-- ===============================================

-- Key Takeaways:
-- - Subquery in WHERE clause calculates overall average total orders per customer.
-- - Filters customers whose total order amount exceeds this average.

SELECT 
    C.CustomerID,
    C.CustomerName,
    C.City
FROM 
    Customers C
WHERE 
    (SELECT SUM(O.OrderTotal) 
     FROM Orders O 
     WHERE O.CustomerID = C.CustomerID) > (
        SELECT AVG(TotalOrders) FROM (
            SELECT CustomerID, SUM(OrderTotal) AS TotalOrders
            FROM Orders
            GROUP BY CustomerID
        ) AS CustomerTotals
    );

Query analysis:

1. External subquery (correlated): SELECT SUM(O.OrderTotal) FROM Orders O WHERE O.CustomerID = C.CustomerID — calculates the total orders for the current customer.
2. Intermediate subquery: SELECT CustomerID, SUM(OrderTotal) AS TotalOrders FROM Orders GROUP BY CustomerID — aggregates totals by customer.
3. Deepest Subquery: SELECT AVG(TotalOrders) FROM (...) — calculates the grand average over these totals per customer.

Expected result: Only customers whose cumulative orders exceed the overall average per customer appear in the results, making it possible to identify customers with high commercial value.

Environment cleanup:

DROP DATABASE SalesDemo;

7. Comparison of subqueries according to SQL platforms

7.1 Common support for all platforms

The three major SQL platforms — SQL Server, PostgreSQL and MySQL — support all subqueries in SELECT, FROM and WHERE clauses, as well as correlated subqueries and scalar subqueries.

7.2 SQL Server

SQL Server generally optimizes subqueries more efficiently, which can lead to better query performance, especially for complex operations involving multiple subqueries.

Specific behavior:

• SQL Server raises an error if a scalar subquery returns multiple rows unexpectedly.
• The SQL Server query optimizer can often transform a correlated subquery into an inner JOIN operation for better performance.

7.3 PostgreSQL

PostgreSQL provides more flexibility than SQL Server in certain scenarios.

Specific behavior:

• PostgreSQL allows custom management when a scalar subquery returns multiple rows (more flexible behavior than SQL Server).
• Support subqueries in all standard contexts.

7.4 MySQL

MySQL supports correlated subqueries, but additional performance considerations apply.

Specific behavior:

• On MySQL, additional indexing or optimization techniques may be required to ensure efficient performance, especially for large datasets or complex queries.
• MySQL optimizer may perform worse than SQL Server on complex correlated subqueries without proper indexing.

7.5 Scalar subqueries and multi-line error handling

A scalar subquery is a subquery that is supposed to return exactly a single value (one row, one column). If a scalar subquery returns multiple rows:

• SQL Server: Raises an explicit error (Subquery returned more than 1 value...).
• PostgreSQL: Offers more flexible mechanisms to handle this case.
• MySQL: Variable behavior depending on version and context.

Best practice: Always ensure that a scalar subquery can only return a single value, adding TOP 1, LIMIT 1, or appropriate filter conditions if necessary.

8. Key Takeaways

Subqueries: advantages and use cases

1. Break down the complexity: Subqueries allow you to fragment complex SQL logic into individual, readable steps.

2. Non-correlated vs Correlated:

• Non-correlated subqueries run once and return a constant value for all rows — ideal for global aggregates.
• Correlated subqueries run once per row of the external query — ideal for row-specific calculations.

3. Placement of the subquery:

• In SELECT: To calculate a derived value for each line (eg: total revenue, quantity sold).
• In FROM: To create a temporary table (derived table) on which to perform additional joins and aggregations.
• In WHERE: To filter results based on dynamically calculated values.

4. Nested subqueries: It is possible to nest several levels of subqueries for comparisons at several levels of aggregation.

5. Performance: Complex correlated subqueries with multiple nesting levels can impact performance on large datasets. Proper indexing and using alternatives like CTE (Common Table Expressions) or window functions can improve performance.

6. Multi-platform compatibility: Subqueries are supported on SQL Server, PostgreSQL and MySQL, but optimization and error handling behaviors vary between platforms.

Summary of subqueries patterns seen in this course

9. Demo files

The demo files are located in the 01/demos/ directory:

Search Terms

write · complex · subqueries · sql · fundamentals · databases · total · product · orders · per · average · correlated · clause · comparison · context · customer · database · non-correlated · sales · schema · subquery · via · overall · platforms