Select query with nested relationships

Question

I have the following 3 PostgreSQL tables:

wallet:

+----+--------+-----------+-----------+--------+
| id | name | createdAt | updatedAt | locked |
+----+--------+-----------+-----------+--------+
| 1 | name_1 | ... | ... | f |
| 2 | name_2 | ... | ... | f |
| 3 | name_3 | ... | ... | t |
+----+--------+-----------+-----------+--------+

order:

+-----------------------------------+--------+-----------+-----------+
| id | rate | status | reason | createdAt | updatedAt | 
+-----------------------------------+------- +-----------+-----------+
| 4aface2d | 0.00062231 | accepted | | ... | ... |
| 9289add0 | 0.00062231 | new | | ... | ... |
| b3dfe546 | 0.00062231 | completed | | ... | ... |
| f70ed6be | 0.00062231 | pending | | ... | ... |
+-----------------------------------+--------+-----------+-----------+

amount:

+----+-------+-----------+-----------+-----------+----------+
| id | value | createdAt | updatedAt | orderId | walletId |
+----+-------+-----------+-----------+-----------+----------+
| 1 | 200 | ... | ... | 4aface2d | 1 |
| 2 | 100 | ... | ... | 9289add0 | 3 |
| 3 | 200 | ... | ... | b3dfe546 | 1 |
| 4 | 50 | ... | ... | 9289add0 | 2 |
| 5 | 100 | ... | ... | f70ed6be | 3 |
| 6 | 200 | ... | ... | f70ed6be | 2 |
+----+-------+-----------+-----------+-----------+----------+

Now I am struggling with composing a query that returns all the unlocked wallets (i.e. locked=false) together with all the related amounts, but where the amounts are filtered on status (e.g. only include amounts that have status='new' or status='accepted').

I've tried something like:

SELECT *
FROM "wallet"
LEFT JOIN "amount"
ON "wallet".id="amount"."walletId"
WHERE "amount"."orderId" IN (
 SELECT id FROM "order"
 WHERE "order".status='new' OR "order".status='accepted'
)
AND "wallet".locked=false;

but that doesn't quite do what I want, because all wallet results are also filtered on the order status condition. I only want the amount result to be filtered on the order status condition. Is such a thing even possible? Or do I need two separate queries for this?

Update

I think I have managed to get what I am after by changing my query to:

SELECT *
FROM "wallet"
LEFT JOIN "amount"
ON "wallet".id="amount"."walletId"
AND "amount"."orderId" IN (
 SELECT id
 FROM "order"
 WHERE status='new'
 OR status='accepted'
)
WHERE "wallet".locked=false;

Only thing now I am not sure about is if the IN (SELECT ...) part is efficient enough. I've read that when the table (i.e. "order") is relatively large then an IN based approach becomes slow.

Answer 1

You could use EXISTS instead of IN. That's likely to perform better.

SELECT *
 FROM "wallet"
 LEFT JOIN "amount"
 ON "wallet"."id" = "amount"."walletId"
 AND EXISTS (SELECT *
 FROM "order"
 WHERE "order"."id" = "amount"."orderId"
 AND "order"."status" IN ('new',
 'accepted'))
 WHERE "wallet"."locked" = false;

To further improve the performance of the above maybe try indexes on "wallet" ("locked", "id"), "amount" ("walletid", "orderid") and order ("id", "status"). Unless you have them already, of course.

Check the plan if you're unsure which version performs better and what indexes help.

• Mikey Dee: Nice one. Thanks! Forgot all about EXISTS. I don't do a lot of SQL stuff usually... And yes, was already planning to use indexes for all id's that are used in the different table comparisons.