LEFT JOIN and IN SUBQUERIES giving DIFFERENT results

Hi guys,

I need your help. I have been having this problem for 2 months and still cannot understand why these 2 queries are giving me different number of rows. I tried retrieving results in 2 ways: left joins and IN subqueries...

My goal is to get all customers that have paid before their current transaction of ordering new items - so basically when TRANSACTION_TYP=PAYMENT and transaction_date is less than current transaction date. The subquery is giving me less rows than the left joins. can anybody tell me why?? pls help

Here are my queries:
LEFT JOIN:
SELECT
A.CUST_ID,
A.TRANSACTION_DATE,
A.ORDER_AMT , A.TRANSACTION_TYP, B.TRANSACTION_TYP
FROM CUSTOMER_TABLE A LEFT JOIN CUSTOMER_TABLE B ON A.CUST_ID=B.CUST_ID AND B.TRANSACTION_TYP=’PAYMENT’ AND B.TRANSACTION_DATE<A.TRANSACTION_DATE
WHERE B.TRANSACTION_TYP IS NOT NULL

SUBQUERY:
SELECT CUST_ID, TRANSACTION_DATE, ORDER_AMT, TRANSACTION_TYP
FROM CUSTOMER_TABLE LEFT WHERE CUST_ID IN
(
SELECT B.CUST_ID FROM CUSTOMER_TABLE B WHERE B.CUST_ID=A.CUST_ID AND B.TRANSACTION_TYP=’PAYMENT’ AND B.TRANSACTION_DATE<A.TRANSACTION_DATE
)

Which result set is correct?

The LEFT JOIN may be retrieving more rows because of multiple transactions by the same customer. You are getting product of number of rows for a customer in the table. That means a customer has 3 records then you get 9 rows returned.

In the second case, you get only one record back.

In both cases, if the customer paid for one item before and has not paid for others, then your logic still allows the customer to make a purchase. You need to look for all records with payment or use NOT IN with no payment.