Subquery or Inner Join?

Question

I have this simple query that finds all transactions from accounts belonging to a specific login, with some extra conditions on the transactions.

SELECT t.id, t.date, t.amount, t.description FROM transaction t
INNER JOIN account ac ON t.account = ac.id AND ac.login_id = ${loginId}
WHERE t.processed = false AND t.updated_by_user = false
AND t.category = 'uncategorized' ;

Will this query perform faster with a subquery on accounts, for example:

SELECT t.id, t.date, t.amount, t.description FROM transaction t
INNER JOIN (SELECT id FROM account WHERE login_id = ${loginId}) ac ON t.account = ac.id
WHERE t.processed = false AND t.updated_by_user = false
AND t.category = 'uncategorized' ;

I'd appreciate some insightful comments on this, thanks in advance!

Answer 1

Use the INNER JOIN whenever possible because it's easier to read. It otherwise should not make a difference.

Answer 2

you can also use cte's:

    with definitely_better as (SELECT id FROM account WHERE login_id = ${loginId}) 

    SELECT t.id, t.date, t.amount, t.description FROM transaction t
    INNER JOIN definitely better ac ON t.account = ac.id
    WHERE t.processed = false AND t.updated_by_user = false
    AND t.category = 'uncategorized' ;

Answer 3

You need to be careful on how you gauge performance with sub query vs inner join. Your query optimizer might build cache towards it and give you false speeds. As if you run this from a stored procedure it may not use the cache the same way as you running via your session.

However, I think between subquery, inner join or CTE, I would use inner join for this case. CTE for huge table data sets that require multiple data sets returned and combined. Subqueries I would try to avoid whenever possible in most cases.