How to select the relative complement of a table B in a table A (A \ B) in an SQL-Query?

Question

I've got two tables:

1. subjects: [id, ...]

2. categories: [subject.id, ...]

I want to select all subjects from table #1 without the entries in #2 (categories).

Any tips appreciated (:

best regards

Answer 1

Sachin's already provided a correct answer, but you can do it with join syntax as well:

SELECT
  subjects.*
FROM
  subjects
LEFT OUTER JOIN
  categories
ON
  subjects.id = categories.subject_id
WHERE
  categories.subject_id IS NULL

Answer 2

select * from subjects where id not in (select subject.id from categories )