Let me explain, I have a main table and an images table, I want to get the results from the main table that don’t have an equivalent in the images table (hence entries without images).
This works:
SELECT primaryKey, title FROM bookpedia WHERE primaryKey NOT IN
(SELECT DISTINCT a.`primaryKey`
FROM book_images a
GROUP BY a.`primaryKey`);
But is extremely slow.
A simple JOIN gets me the entries that have images so I thought that by negating it I could get the results a lot faster, but if I try to use the NOT keyword for a JOIN I get an error of course.
what you’re looking for is the LEFT OUTER JOIN, which allows you, if i may coin a phrase, to go looking for a matching row and yet keep the result even if there wasn’t one
SELECT bookpedia.primaryKey
, bookpedia.title
FROM bookpedia
LEFT OUTER
JOIN book_images
ON book_images.primaryKey = bookpedia.primaryKey
WHERE book_images.primaryKey IS NULL
the condition for the join is a matching column value
for each matching row that is found, the join produces a row in the result set, which will shortly be filtered by the WHERE clause
but when no such matching row is found, the row from the left table, bookpedia, is still included in the result set – that’s the difference between LEFT OUTER JOIN and INNER JOIN, where unmatched rows are dropped – and in the result set, any columns from the right table, in this case the column used for the join, are set to NULL
and then for the result set produced by the join, the WHERE condition now applies a rule – keep only those rows of bookpedia which had no matching join column
