Inconsistent Query Results

Question

Inconsistent Query Results

Bill Mueller-221467

SSC Veteran

Points: 265
More actions
April 1, 2005 at 3:00 pm

#65631

When I execute the following T-SQL query, I only get 4655 rows returned:
SELECT c.* FROM Customers c INNER JOIN customers_from_dedupe d
ON c.recordid = d.recordid
WHERE promotion = 3465
The 'recordid' column is an IDENTITY column in both tables.
If I change the SELECT parameter from c.* to d.*, and execute:
SELECT d.* FROM Customers c INNER JOIN customers_from_dedupe d
ON c.recordid = d.recordid
WHERE promotion = 3465
I get the expected 771,458 rows returned.
After further investigation, I found that if I remove the WHERE-clause from the first query and execute:
SELECT c.* FROM Customers c INNER JOIN customers_from_dedupe d
ON c.recordid = d.recordid
I get the desired 771,458 rows. The 'promotion' column is a clustered-index.
Can anyone explain the reason for this inconsistency?? Shouldn't I get the same number of rows for all these queries??? Thanks!!

Viewing 8 posts - 1 through 7 (of 7 total)

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

Scott Hutchinson SSC Eights! Points: 814 More actions · Answer 1

Is the promotion column in c or d? I think you should prefix promotion with the table in the WHERE clause (like WHERE c.promotion = ). I'm not sure if that is related to your problem, but it might help us all understand the query and the data better.

Bill Mueller-221467 SSC Veteran Points: 265 More actions · Answer 2

Sorry....the 'promotion' column is only in table 'c' (customers).

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

Please post the DDL of the tables. It may also help to post the results of using SET SHOWPLAN_TEXT ON, with the 2 alternative queries.

Bill Mueller-221467 SSC Veteran Points: 265 More actions · Answer 4

Table C: recordid and promotion are INT

Table D: recordid is VARCHAR(50)

Here's the results from SHOWPLAN_TEXT:

StmtText

SELECT c.* FROM Customers c INNER JOIN customers_from_dedupe d

ON c.recordid = d.recordid

WHERE promotion = 3465

(1 row(s) affected)

StmtText

|--Hash Match(Inner Join, HASH[c].[RecordId])=([Expr1002]), RESIDUAL[c].[RecordId]=[Expr1002]))

|--Clustered Index Seek(OBJECT[GroupAuto].[dbo].[Customers].[Promo] AS [c]), SEEK[c].[Promotion]=3465) ORDERED FORWARD)

|--Compute Scalar(DEFINE[Expr1002]=Convert([d].[recordid])))

|--Table Scan(OBJECT[GroupAuto].[dbo].[customers_from_dedupe] AS [d]))

(4 row(s) affected)

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

Which 1 of these 2 statements from the initial post & subsequent replies above is actually correct ?

>>The 'recordid' column is an IDENTITY column in both tables.

>>Table D: recordid is VARCHAR(50)

Scott Hutchinson SSC Eights! Points: 814 More actions · Answer 6

Perhaps you should convert d.recordid from VARCHAR(50) to INT before comparing it to c.recordid.

Bill Mueller-221467 SSC Veteran Points: 265 More actions · Answer 7

The 'recordid' column is only an IDENTITY column in Table C. Casting the 'recordid' to INT int Table D has no effect:

SELECT c.* FROM Customers c INNER JOIN customers_from_dedupe d

ON c.recordid = CAST(d.recordid AS INT)

WHERE c.promotion = 3465

Table D originally contained all the records from Table C with promotion = 3465. Then, records with the same address were stripped out of Table D. Table D only contained a subset of the columns in Table C. The 'promotion' column is not in Table D and 'recordid' is used as the link to Table C.

Therefore, the where-clause is not really needed in the above query. If I remove it, the query returns the correct results. However, I would like to understand why the where-clause is having a negative effect on this query.