How to list non existent items?

Question

the title might be a little bit confusing, let me explain, ;) I have 3 tables:

[names]
n_id;name
1;Jeff
2;Adam

[books]
b_id;title
1;Book1
2;Book2

[read]
n_id;b_id

The table [read] is a table with read books. if Adam reads "Book1" the item in [read] looks like this:

2;1

so far, so good. Now, is there a way to know which books werent read by a person? We know that only Adam read a book "Book1", so the query should output something like this:

n_id;name;b_id;title
1;Jeff;1;Book1
1;Jeff;2;Book2
2;Adam;2;Book2

is it possible to do this in 1 query or do I need some scripting?

Answer 1

You can use a CROSS JOIN to get all possible combinations of names and books and then use a LEFT JOIN on read with IS NULL to remove rows that exist there.

The LEFT JOIN returns NULL for all joined columns where no rows exist, so checking if r.n_id IS NULL removes those rows where the join actually found rows in read.

SELECT n.n_id, n.name, b.b_id, b.title
FROM names n
CROSS JOIN books b
LEFT JOIN read r ON ( r.n_id = n.n_id AND r.b_id = b.b_id )
WHERE r.n_id IS NULL

Answer 2

You would do a cartesian join between names and books to get all possible name/book combinations, then minus the ones that have been read:

SELECT n_id, b_id
FROM names, books
MINUS
SELECT n_id, b_id
FROM read

Others have suggested doing a cross join and a left join, which would also work perfectly well. Might want to try both to see which is faster in a real-world scenario - I suspect the left join others suggested would be faster, but not really sure.