Skip to Main Content

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
11 comments
884 views