Hail to the Victors: JOIN vs. IN question

Hail to the Victors blogs a JOIN vs. IN question.

She wonders why these two queries are not equivalent.

Query 1

SELECT orders.customer_num, customer_name FROM orders, customer WHERE orders.customer_num = customer.customer_num AND order_date <> '20-OCT-2003';

Query 2

SELECT customer_num, customer_name FROM customer WHERE customer_num not in (SELECT customer_num FROM orders WHERE order_date = '20-OCT-2003');

The issue is in this condition from Query 1, “WHERE orders.customer_num = customer.customer_num”. The condition restricts the results to customers who have orders in the orders table as well as in the customers table. In Query 2, there is no such restriction.

You could use a MINUS query to achieve the same effect as Query 2. I do not see any way to do straight JOIN to achieve the same result as Query 2. A JOIN constrains you to entities that have entries in two or more tables while what you looking for is entities in one table that do not have entries in the other.

BTW, these insights did not come brilliant logical analysis. I did a SELECT * on both tables and saw what was going on.