CODEWITHSUNDEEP
sqlpostgresqlquery-performance
Timothy Fisher
Timothy Fisher

Reputation: 1129

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

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?

Upvotes: 0

Views: 184

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

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.

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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;

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

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.

Upvotes: 1

Related Questions