inner join returns cartesian product why?

Question

inner join returns cartesian product why?

Michael Tocik

SSCertifiable

Points: 7051
More actions
March 7, 2011 at 11:22 pm

#236236

Hi,
I have two order tables that I am trying to join. Both have have 13 records in them but when I do an inner join on the order number I get 169 records. Both tables have a data type of varchar(7) Here is the join;
SELECT INVOICE_DETAIL_CONF_ALL.order_number
FROM INVOICE_DETAIL_CONF_ALL
inner join ORDER_EXTRACT_AUTO
on [Ord No] = ORDER_NUMBER
What am I missing?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

tommyh SSCertifiable Points: 6302 More actions · Answer 1

Michael Tocik (3/7/2011)
Hi,
I have two order tables that I am trying to join. Both have have 13 records in them but when I do an inner join on the order number I get 169 records. Both tables have a data type of varchar(7) Here is the join;
SELECT INVOICE_DETAIL_CONF_ALL.order_number
FROM INVOICE_DETAIL_CONF_ALL
inner join ORDER_EXTRACT_AUTO
on [Ord No] = ORDER_NUMBER
What am I missing?

Apart from some DDL code to see what the tables look like?

So just guessing wild:

- All the rows in both tables actually have the same Order_number

- You joining on 2 columns from the same table. Try and add the table name before the column.

/T

Michael Tocik SSCertifiable Points: 7051 More actions · Answer 2

dope!:blush:

My mistake....I was joining on the wrong field

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Michael Tocik (3/8/2011)
dope!:blush:
My mistake....I was joining on the wrong field

Easily done. Try to get into the habit of using table aliases:

SELECT i.order_number

FROM INVOICE_DETAIL_CONF_ALL i

INNER JOIN ORDER_EXTRACT_AUTO o

ON o.[Ord No] = i.ORDER_NUMBER

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden