finding mismatched rows between identical tables based on 2 or more cols

[rshivaraman]

CREATE TABLE [RS_A] (
[ColA] [varchar] (10)
[ColB] [int] NULL
)

CREATE TABLE [RS_B] (
[ColA] [varchar] (10)
[ColB] [int] NULL
)

INSERT INTO RS_A
VALUES ('hemingway' , 1)
INSERT INTO RS_A
VALUES ('vidal' , 2)
INSERT INTO RS_A
VALUES ('dickens' , 3)
INSERT INTO RS_A
VALUES ('rushdie' , 4)

INSERT INTO RS_B
VALUES ('hemingway' , 1)
INSERT INTO RS_B
VALUES ('vidal' , 2)

I need to find all the rows in A which do not exist in B
by matching on both ColA and ColB

so the output should be
dickens 3
rushdie 4

So if i write a query like this , I dont get the right result set

SELECT A.ColA, A.ColB
FROM RS_A A
INNER JOIN RS_B B
ON A.ColA <B.ColA
OR B.ColB <B.ColB

But if i do the following, i do get the right result, but following
seems convoluted.

SELECT A.ColA, A.ColB
FROM RS_A A
WHERE ColA + CAST(ColB AS VARCHAR)
NOT IN (SELECT ColA+CAST(ColB AS VARCHAR) FROM RS_B B)

[Roy Harvey]

Here are two approaches. I personally prefer the EXISTS version.

SELECT A.ColA, A.ColB
FROM RS_A A
WHERE NOT EXISTS
(select * from RS_B B
where A.ColA = B.ColA
and B.ColB = B.ColB)

SELECT A.ColA, A.ColB
FROM RS_A A
LEFT OUTER JOIN RS_B B
ON A.ColA = B.ColA
AND B.ColB = B.ColB
WHERE B.ColA IS NULL

Roy Harvey
Beacon Falls, CT

On Fri, 08 Jun 2007 06:36:17 -0700, rs*********@gmail.com wrote:

>CREATE TABLE [RS_A] (
[ColA] [varchar] (10)
[ColB] [int] NULL
)

CREATE TABLE [RS_B] (
[ColA] [varchar] (10)
[ColB] [int] NULL
)

INSERT INTO RS_A
VALUES ('hemingway' , 1)
INSERT INTO RS_A
VALUES ('vidal' , 2)
INSERT INTO RS_A
VALUES ('dickens' , 3)
INSERT INTO RS_A
VALUES ('rushdie' , 4)

INSERT INTO RS_B
VALUES ('hemingway' , 1)
INSERT INTO RS_B
VALUES ('vidal' , 2)

I need to find all the rows in A which do not exist in B
by matching on both ColA and ColB

so the output should be
dickens 3
rushdie 4

So if i write a query like this , I dont get the right result set

SELECT A.ColA, A.ColB
FROM RS_A A
INNER JOIN RS_B B
ON A.ColA <B.ColA
OR B.ColB <B.ColB

But if i do the following, i do get the right result, but following
seems convoluted.

SELECT A.ColA, A.ColB
FROM RS_A A
WHERE ColA + CAST(ColB AS VARCHAR)
NOT IN (SELECT ColA+CAST(ColB AS VARCHAR) FROM RS_B B)

[Plamen Ratchev]

Here are two different approaches:

-- SQL Server 2005 only.
SELECT ColA, ColB FROM RS_A
EXCEPT
SELECT ColA, ColB FROM RS_B;

-- SQL Server 2000.
SELECT ColA, ColB FROM
( SELECT 'A' AS Source, ColA, ColB
FROM RS_A
UNION ALL
SELECT 'B' AS Source, ColA, ColB
FROM RS_B ) AS T
GROUP BY ColA, ColB
HAVING COUNT(*) = 1 AND MIN(Source) = 'A'

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[rshivaraman]

Roy & Plamen :

Thank for you for you valuable inputs.

- RS

[rshivaraman]

SELECT A.ColA, A.ColB
FROM RS_A A
LEFT OUTER JOIN RS_B B
ON A.ColA = B.ColA
AND B.ColB = B.ColB
WHERE B.ColA IS NULL

Hi Harvey :

I was implementing this solution at another place and i mentioned an
AND instead of a WHERE. and it did not work.
So i used WHERE and viola, things worked.
But actually how can i use a WHERE Clause?
I thought WHERE Clauses were for the FROM table and not for the tables
that are joined to the FROM table

Is my assumption correct ? Obviously it is wrong,
So is it a normal thing to mention conditions where only one table is
needed(like in the above example)
in the WHERE Clause, as then i can do that will all my other queries
where i am joining, and i always have mentioned unique conditions
pertaining to that table alone, using an AND clause.

thank you in advance
RS

[Roy Harvey]

The entire idea of using LEFT OUTER JOIN with a NULL test to give the
same results as NOT EXISTS is based on the fact that the WHERE clause
executes after the JOIN processing.

JOINs occurs before anything else. The WHERE clause acts on the
result of the JOIN. If you changed the WHERE to an AND in the example
shown, the test B.ColA IS NULL would move into the ON clause and
become part of the JOIN. Inside the JOIN that column will never be
NULL.

Personally I much prefer the NOT EXISTS syntax over this approach. I
think it shows what you are trying to retrieve - rows in one table
without matches in the other table - more clearly.

Roy Harvey
Beacon Falls, CT

On Fri, 22 Jun 2007 04:48:02 -0700, rs*********@gmail.com wrote:

SELECT A.ColA, A.ColB
FROM RS_A A
LEFT OUTER JOIN RS_B B
ON A.ColA = B.ColA
AND B.ColB = B.ColB
WHERE B.ColA IS NULL

Hi Harvey :

I was implementing this solution at another place and i mentioned an
AND instead of a WHERE. and it did not work.
So i used WHERE and viola, things worked.
But actually how can i use a WHERE Clause?
I thought WHERE Clauses were for the FROM table and not for the tables
that are joined to the FROM table

Is my assumption correct ? Obviously it is wrong,
So is it a normal thing to mention conditions where only one table is
needed(like in the above example)
in the WHERE Clause, as then i can do that will all my other queries
where i am joining, and i always have mentioned unique conditions
pertaining to that table alone, using an AND clause.

thank you in advance
RS

[rshivaraman]

Thank you for your explanation.
Now, i understand how the WHERE and AND works and can use it much more
effectively in my statements.

RS