RE: Full outer join to show differences

["Bill Meecham" <BMeecham@xxxxxxxxxxxxxxxxxxxx>]

this should work:


select * from before b where NOT EXISTS (select * from after a where a.data = b.data)


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of David FOXWELL
Sent: Thursday, March 10, 2011 10:09 AM
To: Midrange Systems Technical Discussion
Subject: RE: Full outer join to show differences



Birgitta do you mean exception join? I ask as you say to merge and not to join!

Joe, I was already trying IFNULL () but I get an error message.
Charles, thanks I was also trying ISNULL()??? and also getting an error message!


> -----Message d'origine-----
> De : midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Birgitta Hauser
> Envoyé : jeudi 10 mars 2011 15:20
> À : 'Midrange Systems Technical Discussion'
> Objet : AW: Full outer join to show differences
>
> Hi,
>
> you only want to see the rows that are either different or do
> not exist?
>
> IMHO joining is the wrong way, what you need is merging.
>
> The following statement returns all rows that are not identical:
>
>     (    Select * from Table1
>      Except
>          Select * from Table2)
> Union
>     (    Select * from Table2
>      Except
>          Select * from Table1)
>
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the
> stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek
> Bok) "What is worse than training your staff and losing them?
> Not training them and keeping them!"
>
>
> -----Ursprüngliche Nachricht-----
> Von: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von David FOXWELL
> Gesendet: Thursday, 10. March 2011 14:39
> An: Midrange Systems Technical Discussion
> Betreff: Full outer join to show differences
>
> Hi,
>
> I'm comparing 2 tables after a test with a full outer join ie
>
> select * from before b full outer join after  a on b.id=a.id
>
> But I only want to see the data that's different in the after
> table or not there at all, ie null in that select.
>
> So I add
> where a.data<>b.data
>
> This now excludes the missing after lines.
>
> So I need something like
>
> select * from before b full outer join after  a on b.id=a.id
> where a.data<>b.data  or b.data= null
>
> How do I get those null rows?