How to do a Postgresql subquery in select clause with join in from clause like SQL Server?

sql

subqueries

join

performance

byAlex Kataev·Jan 2, 2025

You can execute a PostgreSQL subquery within a SELECT using either a correlated subquery or a derived table. Here are the quick solutions:

Correlated Subquery:

SELECT 
  o.id, 
  /* Remember, COUNT is like taking attendance in class */
  (SELECT COUNT(*) FROM details d WHERE d.order_id = o.id) as detail_count
FROM orders o;

Derived Table:

SELECT 
  o.id, 
  /* Derived tables are like Batman's utility belt, so useful! */
  dt.detail_count
FROM orders o
JOIN (
  SELECT order_id, COUNT(*) as detail_count 
  FROM details 
  GROUP BY order_id
) dt ON dt.order_id = o.id;

In the correlated subquery, make sure to replace o.id with your primary key, and ensure the subquery references this key accurately. The derived table (a.k.a. subquery in the FROM clause) pre-aggregates your data then joins it efficiently with the main orders table.

Advanced practices to level up your SQL skills

1. Leveraging `LATERAL` joins

For complex scenarios where you need to reference columns of an earlier table in the FROM list, enter the magic keyword LATERAL:

SELECT
  o.id,
  /* d is your SQL wingman here */
  d.detail_count
FROM orders o
CROSS JOIN LATERAL (
  SELECT COUNT(*) as detail_count
  FROM details
  WHERE details.order_id = o.id
  /* "LATERAL to base. Alien CONTACT made!"*/
) d;

LATERAL allows the details subquery to reference columns from orders which is extremely useful when you can't express your subquery as a single value.

2. `INDEX`ing for turbo speed

Proper indexing of your columns used in join conditions and where clauses is like adding nitro boosters to your SQL query:

CREATE INDEX idx_order_id ON details(order_id);
/* "I feel the need, the need for speed!"*/

This creates an index on order_id in the details table, speeding up your queries.

Errors are like nasty bugs, they love to crawl into complex queries. Make sure your join conditions in the ON clause are correct and specific columns are used when employing COUNT() in subqueries. Watch out for aggregation mistakes which sneak in without correct GROUP BY command.

4. Clean, clear, and coded to perfection

The key to readability lies in the structuring of your queries with clear aliases and selecting precise columns rather than using SELECT *. Your future self (and your team) will thank you for maintaining clean and clear code.

Mastering subqueries and aggregation

A. Handling multiple subqueries

Running multiple subqueries or inner joins can slow things down. That's where the DISTINCT clause becomes your best mate:

SELECT DISTINCT
  o.id,
  /* "dt, how many times do I have to COUNT you?" */
  dt.detail_count,
  /* "pt, stay DISTINCT just like the James Bond martini!" */
  pt.product_type_count
FROM orders o
JOIN (
  SELECT order_id, COUNT(*) as detail_count
  FROM details
  GROUP BY order_id
) dt ON dt.order_id = o.id
JOIN (
  SELECT order_id, COUNT(DISTINCT product_type) as product_type_count
  FROM details
  GROUP BY order_id
) pt ON pt.order_id = o.id;

Joining two subqueries to the orders table gives a comprehensive analysis while preserving high performance.

B. GROUP BY for better aggregation

The GROUP BY keyword is essential when aggregating data in subqueries. Ensure to group the necessary columns to match the join condition:

SELECT 
  o.id, 
  dt.detail_sum
FROM orders o
JOIN (
  SELECT order_id, SUM(quantity) as detail_sum 
  FROM details 
  GROUP BY order_id
) dt ON dt.order_id = o.id;

C. Safeguarding data type consistency

Sometimes, you may need to cast values to a specific data type:

SELECT 
  o.id, 
  /* "You're a wizard Harry, CAST your spell!" */
  CAST(dt.detail_count AS INTEGER) as detail_count
FROM orders o
JOIN (
  SELECT order_id, COUNT(*) as detail_count 
  FROM details 
  GROUP BY order_id
) dt ON dt.order_id = o.id;