All joined subquery results return null

Question

I am trying to get all customers with their latest payment transaction, including customers without any transaction:

SELECT c.customer_id, c.phone_number, c.email
     , p.transaction_no, p.amount, p.transaciton_datetime
FROM tbl_customers c 
LEFT JOIN (
   SELECT customer_id, transaction_no, amount, transaciton_datetime
   FROM tbl_payment_transactions
   ORDER BY payment_transaction_id DESC
   LIMIT 1
) p
ON c.customer_id = p.customer_id

The above query returns NULL for p.transaction_no, p.amount, p.transaciton_datetime in every row. But I can make sure that there are transactions made by customers in tbl_payment_transactions.

Something like that: `SELECT c.customer_id, c.phone_number, c.email, p.transaction_no, p.amount, p.transaciton_datetime FROM tbl_customers c LEFT JOIN tbl_payment_transactions p ON c.customer_id = p.customer_id LEFT JOIN ( SELECT customer_id, max(payment_transaction_id) maxtransid FROM tbl_payment_transactions GROUP BY customer_id ) p2 ON p.customer_id = p2.customer_id AND p.payment_transaction_id = p2.maxtransid` — juergen d, Feb 12 '23 at 12:46
the record that the limit command selects from the p.customer_id <> c.customer_id field. But you state:- "But I can make sure that there are transactions made by customers in the tbl_payment_transactions." Show us these records — максим ильин, Feb 12 '23 at 12:48

score 2 · Answer 1 · answered Feb 12 '23 at 13:31

You want the subquery to be run once per each different row of the driving table tbl_customers. This is called a lateral subquery and takes the form:

SELECT
  c.customer_id, c.phone_number, c.email,
  p.transaction_no, p.amount, p.transaciton_datetime
FROM tbl_customers c 
LEFT JOIN LATERAL (
  SELECT customer_id, transaction_no, amount, transaciton_datetime
  FROM tbl_payment_transactions t
  WHERE c.customer_id = t.customer_id
  ORDER BY payment_transaction_id DESC 
  LIMIT 1
) p
ON true

score 1 · Answer 2 · answered Feb 12 '23 at 15:18

The Impaler provided the correct form with a LATERAL subquery.

Alternatively, you can use DISTINCT ON in a subquery and a plain LEFT JOIN.

Performance of the latter can be better while retrieving all (or most) customers, and if there are only few transactions per customer and/or you don't have a multicolumn index on (customer_id, payment_transaction_id) or (customer_id, payment_transaction_id DESC):

SELECT c.customer_id, c.phone_number, c.email
     , p.transaction_no, p.amount, p.transaciton_datetime
FROM   tbl_customers c 
LEFT   JOIN (
   SELECT DISTINCT ON (customer_id)
          customer_id, transaction_no, amount, transaciton_datetime
   FROM   tbl_payment_transactions
   ORDER  BY customer_id, payment_transaction_id DESC
   ) p USING (customer_id);

About performance aspects:

All joined subquery results return null

2 Answers2