When you need to test whether certain related records exist or do not exist, you can use the EXISTS and NOT EXISTS operators with subqueries. These operators are especially useful for checking relationships between tables, such as finding books that have never been borrowed or members who have not made any borrowings.

In the example above, you see how NOT EXISTS is used to find all books in the books table that do not have a matching entry in the borrowings table. The subquery inside NOT EXISTS checks for any borrowing record where the book_id matches the outer books table. If no such record exists, the book is included in the result.

The EXISTS operator works similarly, but it returns true if the subquery returns any rows. Both EXISTS and NOT EXISTS are commonly used for these kinds of presence or absence checks, and they are often more efficient than using IN or NOT IN with subqueries, especially when dealing with large datasets or when the subquery could return NULL values.

Choosing between EXISTS/NOT EXISTS and IN/NOT IN depends on the specific use case. EXISTS and NOT EXISTS are generally preferred for correlated subqueries where you check for the existence of related rows.