How to select distinct from union result with condition

You can get your expected output using row number and case statement. If you have two registered then you can use rank, else you can use row number if you want just one row per vendor id.

select vendor_id, vendor_name, registration_status from ( SELECT bv.vendor_id,bv.vendor_name,bv.registration_status, row_number() over (partition by bv.vendor_id order by case when registration_status = 'Register' then 1 else 2) rownum FROM bidding_vendor as bv) t where t.rownum = 1 

There are multiple possibilities here.

You can make your main query a derived table source for your results query

SELECT pv.vendor_id, pv.vendor_name, MAX(registration_status) as registration_status FROM ( SELECT pv.vendor_id,pv.vendor_name,'Not Registered' as registration_status FROM proposal_vendor as pv UNION SELECT bv.vendor_id,bv.vendor_name,bv.registration_status FROM bidding_vendor as bv WHERE bv.bidding_header_id = 97 ) src 

You can use Outer Join logic

SELECT pv.vendor_id, pv.vendor_name, CASE WHEN bv.vendor_id is null THEN 'Not ' END + 'Registered' as registration_status FROM proposal_vendor as pv FULL OUTER JOIN bidding_vendor as bv ON pv.vendor_id = bv.vendor_id WHERE pv.vendor_id = 97 OR bv.vendor_id = 97 

Or you can choose another from perhaps a half dozen different techniques.