{"id":615,"date":"2024-12-04T15:18:40","date_gmt":"2024-12-04T08:18:40","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=615"},"modified":"2025-01-02T16:05:06","modified_gmt":"2025-01-02T09:05:06","slug":"postgresql-correlated-subquery","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-correlated-subquery\/","title":{"rendered":"PostgreSQL Correlated Subquery"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use a PostgreSQL correlated subquery to select data that depends on the values of the outer query.<\/p>\n\n\n\n

Getting Started with PostgreSQL Correlated Subquery #<\/a><\/h2>\n\n\n\n

A correlated subquery is a subquery<\/a> that uses values from an outer query.<\/p>\n\n\n\n

Unlike a regular subquery that can execute independently, PostgreSQL may have to execute a correlated subquery for every row in the outer query.<\/p>\n\n\n\n

For this reason, you should avoid using the correlated subquery as much as possible to improve the query performance.<\/p>\n\n\n\n

PostgreSQL Correlated Subquery example #<\/a><\/h2>\n\n\n\n

Suppose we have the following products<\/code> table that include id, name, price, and brand:<\/p>\n\n\n\n

Script for creating the products table and inserting data into it<\/summary>
CREATE<\/span> TABLE<\/span> products (\n  id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  name<\/span> VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n  price DEC<\/span>(11<\/span>, 2<\/span>) NOT<\/span> NULL<\/span> CHECK<\/span> (price > 0<\/span>),\n  brand VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>\n);\nINSERT<\/span> INTO<\/span>\n  products (name<\/span>, price, brand)\nVALUES<\/span>\n  ('Galaxy S24'<\/span>, 799.99<\/span>, 'Samsung'<\/span>),\n  ('iPhone 16'<\/span>, 1099.99<\/span>, 'Apple'<\/span>),\n  ('iPhone 16 Pro Max'<\/span>, 1399.99<\/span>, 'Apple'<\/span>),\n  ('iPhone 16 Plus'<\/span>, 1199.99<\/span>, 'Apple'<\/span>),\n  ('Galaxy S24 Ultra'<\/span>, 1299.99<\/span>, 'Samsung'<\/span>),\n  ('Galaxy S24 Plus'<\/span>, 1119.99<\/span>, 'Samsung'<\/span>);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre><\/details>\n\n\n\n
id<\/th>name<\/th>price<\/th>brand<\/th><\/tr><\/thead>
1<\/td>Galaxy S24<\/td>799.99<\/td>Samsung<\/td><\/tr>
2<\/td>iPhone 16<\/td>1099.99<\/td>Apple<\/td><\/tr>
3<\/td>iPhone 16 Pro Max<\/td>1399.99<\/td>Apple<\/td><\/tr>
4<\/td>iPhone 16 Plus<\/td>1199.99<\/td>Apple<\/td><\/tr>
5<\/td>Galaxy S24 Ultra<\/td>1299.99<\/td>Samsung<\/td><\/tr>
6<\/td>Galaxy S24 Plus<\/td>1119.99<\/td>Samsung<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
The following example shows how to use a correlated subquery to get the product name, brand, and price of the most expensive product in each brand:<\/p>\n\n\n
SELECT<\/span>\n  name<\/span>,\n  brand,\n  price\nFROM<\/span>\n  products p1\nWHERE<\/span>\n  price = (\n    SELECT<\/span>\n      MAX(price)\n    FROM<\/span>\n      products p2\n    WHERE<\/span>\n      p2.brand = p1.brand\n  );<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Try it<\/a><\/p>\n\n\n\n
Output:<\/p>\n\n\n
       name        |  brand  |  price\n-------------------+---------+---------\n iPhone 16 Pro Max | Apple   | 1399.99\n Galaxy S24 Ultra  | Samsung | 1299.99<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, the outer query selects the name<\/code>, brand<\/code>, and price<\/code> from the products<\/code> table with the alias p1<\/code>:<\/p>\n\n\n
SELECT<\/span>\n  name<\/span>,\n  brand,\n  price\nFROM<\/span>\n  products p1<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, PostgreSQL executes the correlated subquery for each row in the outer query:<\/p>\n\n\n
SELECT<\/span>\n  MAX(price)\nFROM<\/span>\n  products p2\nWHERE<\/span>\n  p2.brand = p1.brand<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The correlated subquery selects the maximum price from the products<\/code> table with the alias p2<\/code>, where the brand of p2<\/code> matches the brand of the current row in the outer query (p1.brand<\/code>).<\/p>\n\n\n\n
Third, the WHERE<\/code> clause in the outer query checks the price of the current row (p1.price<\/code>) equals the maximum price of the brand of the current row. If yes, PostgreSQL includes the row in the result set.<\/p>\n\n\n\n
Here’s the detail:<\/p>\n\n\n\n
Row id 1<\/p>\n\n\n\n
id<\/th>name<\/th>price<\/th>brand<\/th><\/tr><\/thead>
1<\/td>Galaxy S24<\/td>799.99<\/td>Samsung<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
The subquery finds the maximum price of the Samsung brand (1299.99<\/code>).<\/p>\n\n\n\n
The WHERE<\/code> clause compares the price (799.99<\/code>) with the maximum price (1299.99<\/code>). It returns false. PostgreSQL does not include row 1 in the result set.<\/p>\n\n\n\n
Row id 2:<\/p>\n\n\n\n
id<\/th>name<\/th>price<\/th>brand<\/th><\/tr><\/thead>
2<\/td>iPhone 16<\/td>1099.99<\/td>Apple<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
The subquery finds the maximum price of the Apple brand (1399.99<\/code>).<\/p>\n\n\n\n
The WHERE<\/code> clause compares the price (1099.99<\/code>) with the maximum price (1399.99<\/code>), which returns false<\/code>. Hence, PostgreSQL does not include row id 2 in the result set.<\/p>\n\n\n\n
Row id 3<\/p>\n\n\n\n
id<\/th>name<\/th>price<\/th>brand<\/th><\/tr><\/thead>
3<\/td>iPhone 16 Pro Max<\/td>1399.99<\/td>Apple<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
The subquery finds the maximum price of the Apple brand (1399.99<\/code>).<\/p>\n\n\n\n
The WHERE<\/code> clause compares the price (1399.99<\/code>) with the maximum price (1399.99<\/code>),which returns true<\/code>. PostgreSQL includes row id 3 in the result set.<\/p>\n\n\n\n
Row id 4<\/p>\n\n\n\n
id<\/th>name<\/th>price<\/th>brand<\/th><\/tr><\/thead>
4<\/td>iPhone 16 Plus<\/td>1199.99<\/td>Apple<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
The subquery finds the maximum price of the Apple brand (1399.99<\/code>).<\/p>\n\n\n\n
The WHERE<\/code> clause compares the price (1199.99<\/code>) with the maximum price (1399.99<\/code>). It returns false<\/code>. PostgreSQL does not include row id 4 in the result set.<\/p>\n\n\n\n
Row id 5<\/p>\n\n\n\n
id<\/th>name<\/th>price<\/th>brand<\/th><\/tr><\/thead>
5<\/td>Galaxy S24 Ultra<\/td>1299.99<\/td>Samsung<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
The subquery finds the maximum price of the Samsung brand (1299.99<\/code>).<\/p>\n\n\n\n
The WHERE<\/code> clause compares the price (1299.99<\/code>) with the maximum price (1299.99<\/code>), which returns true. Therefore, PostgreSQL does not include row id 5 in the result set.<\/p>\n\n\n\n
Row id 6<\/p>\n\n\n\n
id<\/th>name<\/th>price<\/th>brand<\/th><\/tr><\/thead>
6<\/td>Galaxy S24 Plus<\/td>1119.99<\/td>Samsung<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
The subquery finds the maximum price of the Samsung brand (1299.99<\/code>).<\/p>\n\n\n\n
The WHERE<\/code> clause compares the price (1119.99<\/code>) with the maximum price (1299.99<\/code>), which returns false. PostgreSQL does not include row id 6 in the result set.<\/p>\n\n\n\n
The final result set will be as follows:<\/p>\n\n\n\n
name<\/th>price<\/th>brand<\/th><\/tr><\/thead>
iPhone 16 Pro Max<\/td>1399.99<\/td>Apple<\/td><\/tr>
Galaxy S24 Ultra<\/td>1299.99<\/td>Samsung<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
This example shows that PostgreSQL has to execute the correlated subquery to find the maximum price of Apple brand for every row in the outer query. It is redundant and inefficient.<\/p>\n\n\n\n
Rewriting a correlated subquery using join #<\/a><\/h2>\n\n\n\n
The following example uses a JOIN<\/a><\/code><\/code> with a subquery to make the query faster:<\/p>\n\n\n
SELECT<\/span>\n  p1.name,\n  p1.brand,\n  p1.price\nFROM<\/span>\n  products p1\n  JOIN<\/span> (\n    SELECT<\/span>\n      brand,\n      MAX(price) AS<\/span> max_price\n    FROM<\/span>\n      products\n    GROUP<\/span> BY<\/span>\n      brand\n  ) p2 ON<\/span> p1.brand = p2.brand\n  AND<\/span> p1.price = p2.max_price;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, select the brand and maximum product price by brand using GROUP BY<\/a><\/code> with the MAX<\/code> aggregate function:<\/p>\n\n\n
SELECT<\/span>\n  brand,\n  MAX(price) AS<\/span> max_price\nFROM<\/span>\n  products\nGROUP<\/span> BY<\/span>\n  brand;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, join the products<\/code> table with the result set returned by the subquery on the brand<\/code> and price<\/code> columns. This will retrieve the products with the highest price for each selected brand.<\/p>\n\n\n\n
This approach reduces the number of times PostgreSQL executes the subquery, improving the query performance, especially when the products<\/code> table is large.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n