CODEWITHSUNDEEP
sqloracle10ginner-join
Sam Nasr
Sam Nasr

Reputation: 33

Inner Join Producing cartesian product

Looking at the 2 queries below, I assumed they would return the same result set but they're way off. Why is the 2 query with the inner join producing so many records? What am I doing wrong? I've been staring at this a little too long and need a fresh pair of eyes to look at it.

SELECT COUNT(*)  
FROM ZCQ Z
WHERE Z.QUOTE_CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM CUST_ORDER)
-- returned 6,646 RECS

SELECT COUNT(*) 
FROM ZCQ Z
INNER JOIN CUST_ORDER CO ON zquote_customer_id = co.customer_id
-- returned 4,232,473 RECS

Please note these are Oracle 10g tables but have no FK or PK setup by the DBA.

Upvotes: 0

Views: 121

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

No, these will not generally return the same result.

  • The first counts the number of rows in ZCQ that match a customer in CUST_ORDER.

  • The second counts the total number of rows that match. If there are duplicate customers in CUST_ORDER, then all duplicates will be counted.

You could get the same result using:

SELECT COUNT(DISTINCT z.zquote_customer_id) 
FROM ZCQ Z JOIN
     CUST_ORDER CO
     ON zquote_customer_id = co.customer_id;

But IN or EXISTS is probably more efficient than removing the duplicates after doing the match.

Upvotes: 1

Related Questions