sqldatabasepostgresql

Changing a query from subqueries to joins

I am trying to optimize a query and I believe I can do it with joins as opposed to subqueries but I am not sure how to. Bellow is my query:

SELECT * 
FROM   accounts 
WHERE  LOWER(firstname) = LOWER('Sam') 
       AND id IN (SELECT account_id 
                  FROM   addresses 
                  WHERE  LOWER(name) = LOWER('Street1')) 
       AND id IN (SELECT account_id 
                  FROM   alternate_ids 
                  WHERE  alternate_id_glbl = '5');

I have 3 tables: accounts, addresses, and alternate ID's. When I do a search I want to return the set of accounts that have a first name of Sam, an address of Street 1, and an alternate ID of 5.

There is an account_id column in the address and alternate ID tables that has the ID of the account it is associated with.

Do you have any ideas on how I could turn this into query that uses joins or possibly a more efficient query?

Oh and this is a postgres DB

Solution

  • Try this:

    SELECT acc.* 
FROM accounts acc 
JOIN addresses addr ON acc.id = addr.account_id
JOIN alternate_ids ids ON acc.id = ids.account_id
WHERE LOWER(acc.firstname) = LOWER('Sam') 
AND LOWER(addr.name) = LOWER('Street1') 
AND ids.alternate_id_glbl = '5';

    However, it is not guaranteed that this version will work better, as the query optimizer usually automatically tunes the execution plan.