Is there a way to get results from negating a JOIN?

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.


i kinda like the way you think :wink:

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 
  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

in other words, kind of like a negative join :slight_smile:

I swear I tried this but was getting no results. Maybe I mixed the table orders or something.

Well now it works, thanks a lot Rudy!