Does using a query inside a select statement execute that query for every row?

Question

Using Postgres I've written the following query:

SELECT 
    *,
    CASE 
        WHEN
            (SELECT count(*) FROM applications WHERE applications.user_id = 774 AND applications.job_id = jobs.id) > 0
        THEN 1 
        ELSE 0 
    END 
    AS has_applied 
FROM jobs

It selects all of the jobs from my database and runs a check to see if the currently logged in user (with an ID of 774 in this case) has applied to the job.

I see only one query run in my database GUI, but doesn't this have to run the nested query for each row from the jobs table?

Is there a better way to write this? Or does this seem like something that should be done by comparing the datasets from two separate queries?

"doesn't this have to run the nested query for each row" No, it just has to return the rows described by the SQL. Read about basics of relational query implementation/optimization generically & for your DBMS. These are faqs. Before considering posting please read the manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Apr 06 '20 at 04:18
It's not clear what you mean by "see only one query run in my database GUI". But there's one query expression here that contains a subquery as subexpression so only one query is going to be run regardless of how often any pieces of implementation code are repeatedly run after the query expression is transfomed to code. Learn about SQL EXPLAIN, query plans etc. — philipxy, Apr 06 '20 at 04:26
Does this answer your question? [In which sequence are queries and sub-queries executed by the SQL engine?](https://stackoverflow.com/questions/2263186/in-which-sequence-are-queries-and-sub-queries-executed-by-the-sql-engine) — philipxy, Apr 06 '20 at 04:35
Check out the execution plan. I wouldn't be surprised if this was rewritten to a left join — , Apr 06 '20 at 05:00

Tim Biegeleisen · Accepted Answer · 2020-04-06T03:32:40.153

1

You may write this using a left join:

SELECT
    j.*,
    CASE WHEN COUNT(CASE WHEN a.user_id = 774 THEN 1 END) > 0
         THEN 1 ELSE 0 END AS has_applied
FROM jobs j
LEFT JOIN applications a
    ON j.id = a.job_id
GROUP BY
    j.id;

Note that the above GROUP BY syntax is actually valid on Postgres, assuming that jobs.id be the primary key of that table. In that case, jobs.* are columns which are all functionally dependent on jobs.id.

edited Apr 06 '20 at 03:32

answered Apr 06 '20 at 03:01

Tim Biegeleisen

502,043
27
286
360

This took me a bit to understand but now I understand it. Awesome! The only adjustment I had to make was changing `COUNT(...) > 1` to greater than `0`. In my case, a user can only apply to a job once so I supposed `COUNT(...) = 1` would work too. – Timothy Fisher Apr 06 '20 at 03:27
Nvm, now I see for my case that part can just be shortened to `CASE WHEN a.user_id = 774`. Good stuff to know all around, excellent solution! – Timothy Fisher Apr 06 '20 at 03:36

score 1 · Answer 2 · answered Apr 06 '20 at 03:43

1

You can simply do the LEFT JOIN :

SELECT j.*, (CASE WHEN a.job_id IS NULL THEN 0 ELSE 1 END) AS has_applied
FROM jobs j LEFT JOIN
     applications a
     ON a.job_id = j.id AND a.user_id = 774;

answered Apr 06 '20 at 03:43

Yogesh Sharma

49,870
5
26
52

1

Note that this solution assumes that a given user would apply to a certain job at most one time. Otherwise, this approach could generate duplicate records for a given job. – Tim Biegeleisen Apr 06 '20 at 03:52
Looks like this works too. Probably the one I will end up using as we're enforcing only applying to a job once in the business rules for the application, but, I didn't specify that in my question. If something changes, good to have both solutions. – Timothy Fisher Apr 06 '20 at 03:56
@TimothyFisher . . . If people can apply for the same job more than once, then this can return duplicates. I strongly recommend `exists` for what you want to do. – Gordon Linoff Apr 06 '20 at 12:04

score 0 · Answer 3 · answered Apr 06 '20 at 11:59

I strongly recommend using EXISTS. You can just return the value as a boolean value in Postgres, so:

SELECT j.*,
       (EXISTS (SELECT 1
                FROM applications a
                WHERE a.job_id = j.id AND a.user_id = 774 
       ) as has_applied
FROM jobs j;

With an index on applications(job_id, user_id) there should be no faster way to process the query.

Does using a query inside a select statement execute that query for every row?

3 Answers3