Link to home
Create AccountLog in
Avatar of andieje
andieje

asked on

best way to detect rows which fail join

Hi

What is the best way to detect rows which fail an inner join such as

 select * from tableA a inner join tableB b on a.field = b.field

I want to detect the rows in table a which fail the join. I can only think of
select * from tableA where tableA.field not in (select tableA.field from tableA a inner join tableB b on a.field = b.field)

assuming tableA.field is unique

thanks
ASKER CERTIFIED SOLUTION
Avatar of DowntownIT
DowntownIT
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of andieje
andieje

ASKER

won't that return the result of the join including the null fields in table b? I just wanted the fields in table
A
Should return all fields in Table a that do not have rows in Table b.
If I did a left join with no where clause, then I get all rows from table a regardless if there is a matching row or not. If there is no matching row in table b, the b.field is returned null. So by adding a where clause (where b.field is null), this only gives you the rows in table a that are not in table b.
Rows from table A that don't join should have NULL in the second column.

I have added the

HAVING
 b.Column1 = NULL

to pick out just the rows that don't join.
select 
 a.Column1,
 b.Column1
from 
 tableA a
  LEFT JOIN tableB b 
    ON a.field = b.field
HAVING
 b.Column1 = NULL

Open in new window

Avatar of andieje

ASKER

what i mean is that the query returns the columns in table a and b if the join is successful. i just want the cols in table a without having to write out a column list

'without having to write out a column list' - sorry I don't understand this bit.

Do you have a table structure and some example data and an example output what you are after?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
@ DowntownIT

Is it not

where b.field is not null ?

andieje seems to be after the opposite now.
I though his orignal question was to return all rows from table a that did not join to table b. Is this not correct?
Avatar of andieje

ASKER

thanks