problem with union

[chinni0719]

when i executed below query seperate they are displaying all the records and when i union these queries they are unable to display all the records........ .

select 'deleted', m.col1,v.col2,m .col3 from MasterData m right join VendorData v on m.a=v.b where xyz in null

union

select 'new record',m.col1, v.col2,m.col3 from MasterData m left join VendorData v on m.a=v.b where xyz is null

[ck9663]

when i executed below query seperate they are displaying all the records and when i union these queries they are unable to display all the records........ .

select 'deleted', m.col1,v.col2,m .col3 from MasterData m right join VendorData v on m.a=v.b where xyz in null

union

select 'new record',m.col1, v.col2,m.col3 from MasterData m left join VendorData v on m.a=v.b where xyz is null

The first query will return all records from VendorData regardless if there are matching records from MasterData. The second query is the exact opposite. What records do you think are missing?

Try including the WHERE on your join condition.

-- CK

[chinni0719]

select 'deleted', m.col1,v.col2,m .col3 from MasterData m right join VendorData v on m.a=v.b where xyz in null

union

select 'new record',m.col1,v.col2,m .col3 from MasterData m left join VendorData v on m.a=v.b where xyz is null



thanku ............... .....i found it............. ....what i did was i did not change the columns to be displayed for second query "new Records" becoz new records will be present in MasterData and also where clause