Re: Minus Operator with Missing Column

: 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?

E.g. set A

	x.no dates	y.with dates
	( 1) 	|	( 1, 1-jan-2000 )
	( 2)	|	( 2, 23-feb-1999)
minus set B	|
	( 2)	|	( 2, null )
gives		|
	( 1)	|	( 1, 1-jan-2000 )
		|	( 2, 23-feb-1999)

In the second half of the example, set A.y does not contain ( 2, null ), so minus'ing it does not remove anything. That shows why your second query returns more rows. To use minus, I would do something like the following

	select id, creation_date  
	from   tbl_A 
	where  person_id 
	in
	( select    a.person_id
	  from      tbl_A a
	  minus
	  select    b.person_id
	  from      tbl_B b
	)