Select jobs that user has not applied

Question

I am working on a project. I have 2 tables

Table 1: "jobs" with fields 1. job_id 2. company_id 3. title

Table 2: "job_applications" with fields 1. job_app_id 2. job_id 3. user_id

I want to list the jobs, but do not want to list those jobs that a particular user has applied. (for example, There are total 3 jobs. a user with user_id 2 has applied for job with job_id 1. So jobs with job_id 2 and 3 should be displayed)

I tried to use this:

SELECT * FROM jobs j
LEFT JOIN job_applications ja
ON j.job_id = ja.job_id
WHERE ja.user_id !=2

it shows repetitive data for table 1 when multiple users has applied for the same job and eliminates jobs that no one has applied because of having NULL value

Answer 1

How about using the NOT IN operator?

SELECT * 
FROM   jobs j
WHERE  job_id NOT IN (SELECT job_id
                      FROM   job_applications
                      WHERE  user_id = 2)