Search code examples
sqldatabaseamazon-web-servicesamazon-rds

SQL query editor crashes when i run this query

SELECT job.*
FROM job,job_application
WHERE job.id != job_application.jobId
AND job_application.freelancerId = '4ac4bac0-23bf-4ff6-a3a6-61282e87f7bc'

I am trying to select rows from job table where id of job not in job_application table and freelancer id = (i and providing manually) but query editor freezes and crashes when i try to run this.

Solution

  • The database is likely trying to join these tables on your != condition, and as a result is producing (or trying to produce) a cartesian product of the tables. Essentially all records from job are being joined with all records from job_application. Depending on table sizes this is likely a HUGE result.

    Instead use NOT IN in your WHERE clause to filter the records in job:

    SELECT job.*
FROM job
WHERE job.id NOT IN 
    (
         SELECT jobID 
         FROM job_application 
         WHERE freelancerID = '4ac4bac0-23bf-4ff6-a3a6-61282e87f7bc'
    )