Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Single column foreign key and left join - result depends from select column list

FilipFryOct 18 2013 — edited Oct 20 2013

create table test_fk(c1 date primary key deferrable);

create table test_fk1(b1 date references test_fk(c1) deferrable);

alter session set constraints=deferred;

insert into test_fk1 values(sysdate);

insert into test_fk1 values(sysdate);

insert into test_fk1 values(sysdate);

SELECT t1.C1, t.B1 FROM TEST_FK1 t LEFT JOIN TEST_FK t1 ON (t1.C1 = t.B1) WHERE t1.C1 IS NULL AND t.B1 IS NOT NULL;

C1                     B1                  

---------------------- ----------------------

                       18-OCT-2013 16.38.43  

                       18-OCT-2013 16.38.48  

                       18-OCT-2013 16.38.46 

SELECT t.B1 FROM TEST_FK1 t LEFT JOIN TEST_FK t1 ON (t1.C1 = t.B1) WHERE t1.C1 IS NULL AND t.B1 IS NOT NULL;

no rows selected

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 17 2013
Added on Oct 18 2013
#column, #list, #select
11 comments
889 views