Postgres outer apply

by

PostgreSQL OUTER APPLY

The OUTER APPLY operator in PostgreSQL allows you to perform a table-valued function for each row of a table, and include both rows from the original table and the resulting function rows in the output. This operator is useful when you need to make calculations or retrieve additional information based on each row of a table.

Here is an example to explain its usage:

  
    -- Let's say we have two tables: Orders and OrderDetails
    CREATE TABLE Orders (
      order_id SERIAL PRIMARY KEY,
      order_date DATE,
      customer_id INTEGER
    );
    
    CREATE TABLE OrderDetails (
      detail_id SERIAL PRIMARY KEY,
      order_id INTEGER,
      product_name VARCHAR(50),
      quantity INTEGER
    );
    
    -- Insert some sample data
    INSERT INTO Orders (order_date, customer_id) VALUES
      ('2022-01-01', 1),
      ('2022-01-02', 2),
      ('2022-01-03', 1);
    
    INSERT INTO OrderDetails (order_id, product_name, quantity) VALUES
      (1, 'Product A', 5),
      (1, 'Product B', 3),
      (2, 'Product C', 2),
      (3, 'Product A', 1);
    
    -- Retrieve the total quantity of each order, including orders with no details
    SELECT o.order_id, o.order_date, od.total_quantity
    FROM Orders o
    OUTER APPLY (
      SELECT SUM(quantity) AS total_quantity
      FROM OrderDetails od
      WHERE od.order_id = o.order_id
    ) od;

The above query retrieves the total quantity of each order, including orders that have no corresponding details in the OrderDetails table. The OUTER APPLY operator is used to perform a subquery for each row of the Orders table, and the resulting total quantity is included in the output.

Leave a comment