0

I'm trying get a query result of empty job positions within the company in a mock-up scheme. The syntax seems fine to me but it returns columns with empty rows and on top of that, these columns are all the columns combined together from 2 tables described below. Here is my code:

SELECT * FROM HR.jobs
Left JOIN HR.employees ON HR.jobs.job_id = HR.employees.job_id
WHERE HR.employees.job_id = NULL;

There are these 2 tables called HR.employees with your regular column names, primary key being employee_id and a foreign key job_id. The other one is HR.jobs and contains primary key job_id and other generic columns like job_title, min/max_salary etc.

I can't tell whether it's the JOIN that is wrong or WHERE clause. I checked out w3schools on where clauses and joins and What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? too and it would seem that I need (A∪B) - B but it just produces different result, than expected.

Tweny8
  • 3
  • 4

1 Answers1

0
  1. Use is null
  2. Use aliases
  3. Specify which columns do you want to output
SELECT j.* -- only columns from hr.jobs
FROM HR.jobs j
Left JOIN HR.employees e 
  ON j.job_id = e.job_id
WHERE e.job_id is NULL;
Sayan Malakshinov
  • 8,492
  • 1
  • 19
  • 27
  • Thanks a lot, it did output only the HR.jobs columns. Wasn't sure whether their entries were empty but this code seems foolproof. I will try to get used to aliases, haven't really used them much. – Tweny8 Apr 22 '21 at 00:57