Minus Operator with Missing Column

From: jimmyb <jimmybrock_at_gmail.com>
Date: Fri, 8 Jan 2010 15:02:15 -0800 (PST)
Message-ID: <de4e69fb-cf08-4af4-a7e7-4ec829c1f29e_at_34g2000yqp.googlegroups.com>

I'm trying to find the records in Table A, that are not in Table B. So I thought the Minus set operator would be a good tool to use.

select count(*) from tbl_A ;
-- 21265 rows

select a.person_id
from tbl_A a
minus
select b.person_id
from tbl_B b
-- 1399 rows

So far that looks good. Now I want to see the date the records in Table A were created, but this column does not exist in Table B; so I add a null value to Table B - like this.

select a.person_id, a.creation_date
from tbl_A a
minus
select b.person_id, to_date(null) "creation_date" from tbl_B b
-- 21247 rows

Am I missing something? I thought you replaced missing columns with null values cast to the corresponding data type. No? Received on Fri Jan 08 2010 - 17:02:15 CST