Best way to do an unmatched query

[jaylou]

Hi all,
I was wondering what the best SQL code is to find all records in table_A where they do not exist is Table_B

I use
select a.* from Table_A a left outer join Table_b b
on a.ID = b.ID
where b.ID is null

Is the the most efficient way to do this or is there better codsde for this?
Thanks.

 

[r937]

interesting question

another alternative is

select * 
  from Table_A a 
 where not exists 
     ( select ID
         from Table_b 
        where ID = a.ID )

i'd be interested to see which one is faster too

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[donutman]

Wouldn't it be nice if we had an FAQ that addressed each of these questions one at a time? I doubt that it would be fair to draw a conclusion from just one example.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[jaylou]

Thank you I will check the execurion plan for both and see which is more efficient.

Thanks again

 

[har1seldon]

I'm a bit new to database programming, that's my warning before I ask this followup question.

How would you modify that query to display records that did not match (both had data, but they were different) as opposed to finding data that was simply not entered?

 

[donutman]

Ahh, not sure what you mean by that, but maybe if you bought the optional crystal ball.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[r937]

i always have trouble finding data that isn't matched with anything



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[donutman]

The computer version of the old needle-in-haystack problem.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[JamesLean]

har1seldon, I assume you have a situation with two tables that have the same structure, which can be linked on some key value but that may have different data in the other columns?

If so something like this should do what you need:

SELECT a.id, a.c1, b.c1, a.c2, b.c2
FROM tablea a JOIN tableb b ON a.id = b.id
WHERE a.c1 <> b.c1
  OR a.c2 <> b.c2

--James

 

[jaylou]

James, you are absolutly correct.
except <> fails on nulls against data.
the only change I would make is

SELECT a.id, a.c1, b.c1, a.c2, b.c2
FROM tablea a JOIN tableb b ON a.id = b.id
WHERE isnull(a.c1,0) <> isnull(b.c1,0)
OR isnull(a.c2,' ') <> isnull(b.c2,' ')

assuming c1 is numeric and c2 is char.

this will compare something to something

 

[har1seldon]

I will have to try this on a test access database but I will show an example of what I'm looking for in the ouput.

10 answer survey, this is survey #6

Table1=Entered by Joe (True=1, False=2, no entry=0)

ID q1 q2 q3 q4 q5 q6 q7 q8 q9 q10
6 2 1 2 1 1 1 2 0 1 1

Table2=Entered by Jill

ID q1 q2 q3 q4 q5 q6 q7 q8 q9 q10
6 2 1 1 1 1 1 2 2 1 1


The old query just returns ID # 6, and you have to visually check that q3 and q8 have errors

Looking for it to say

Error on
ID6, q3, q8

 

[JamesLean]

Something like this?

SELECT t1.id,
  CASE WHEN t1.q1 <> t2.q1 THEN 'Q1 err' ELSE 'OK' END AS Q1,
  CASE WHEN t1.q2 <> t2.q2 THEN 'Q2 err' ELSE 'OK' END AS Q2,
  CASE WHEN t1.q3 <> t2.q3 THEN 'Q3 err' ELSE 'OK' END AS Q3,
  ...
FROM t1 JOIN t2 ON t1.id = t2.id
WHERE t1.q1 <> t2.q1
  OR t1.q2 <> t2.q2
  OR t1.q3 <> t2.q3
  ...
ORDER BY t1.id

--James