CODEWITHSUNDEEP
sqlms-access
Tobias Funke
Tobias Funke

Reputation: 1814

Using a subquery with NOT IN

Find out how many books in the database are not part of the order.

Query:

SELECT COUNT(title)
FROM book INNER JOIN orders ON book.isbn = orders.bookID
WHERE isbn NOT IN (bookID);

Upvotes: 0

Views: 67

Answers (2)

ErikE
ErikE

Reputation: 50231

I would lean away from using NOT IN here. Learn to use JOIN:.

SELECT Count(*)
FROM
   book AS B
   LEFT JOIN orders AS O
      ON B.isbn = O.bookID
WHERE
   O.isbn IS NULL
;

And also (not 100% sure this will work in Access but I think so):

SELECT Count(*)
FROM
   book AS B
WHERE
   NOT EXISTS (
      SELECT *
      FROM orders AS O
      WHERE B.isbn = O.bookID
   )
;

Note that you don't need Count(title), Count(*) works fine and doesn't cast doubt that perhaps you're trying to count the number of non-NULL titles.

Upvotes: 3

Esteban Elverdin
Esteban Elverdin

Reputation: 3582

If you want to use a subquery and NOT IN, then you should write it this way

SELECT COUNT(title)
FROM book 
WHERE isbn NOT IN (SELECT bookID
                   FROM orders)

Upvotes: 0

Related Questions