CODEWITHSUNDEEP
sqloracle-databasejoininner-join
SmallFry
SmallFry

Reputation: 153

OR Condition in Oracle SQL

We have inherited a set of tables from a third-party product. One of the tables has 2 foreign key constraints that link to the same column on the parent table. I have simplified the tables to demonstrate this:

CREATE TABLE profile_defn 
(   
  profile_id NUMBER(10,0), 
  profile_name  VARCHAR2(50),
  CONSTRAINT pk1 PRIMARY KEY (profile_id)
);

CREATE TABLE letter_defn 
(
  letter_defn_id  NUMBER(10,0), 
  letter_name     VARCHAR2(50 BYTE), 
  orig_print_id   NUMBER(10,0),
  new_print_id    NUMBER(10,0),
  CONSTRAINT fk1 FOREIGN KEY (orig_print_id) REFERENCES profile_defn (profile_id) ENABLE,
  CONSTRAINT fk2 FOREIGN KEY (new_print_id) REFERENCES profile_defn (profile_id) ENABLE
);

INSERT INTO profile_defn
VALUES
(
  1,
  'profile1'
);

INSERT INTO profile_defn
VALUES
(
  2,
  'profile2'
);

INSERT INTO profile_defn
VALUES
(
  3,
  'profile3'
);

INSERT INTO letter_defn
VALUES 
(
  1,
  'letter1',
  1,
  2
);

INSERT INTO letter_defn
VALUES 
(
  1,
  'letter2',
  2,
  3
);

Consequently when joining the 2 tables there is an OR condition as it needs to identify matching records on either column:

SELECT * FROM letter_defn ld
JOIN profile_defn p 
ON ld.orig_print_id = p.profile_id OR ld.new_print_id = p.profile_id;

Are there are any consequences of using OR in the JOIN or is there a 'better' way of doing it?

Thanks.

Upvotes: 3

Views: 196

Answers (3)

Manos Anastasiadis
Manos Anastasiadis

Reputation: 116

Just in case you select to use the UNION ALL alternative suggested, be aware of the fact that any records with orig_print_id = new_print_id will apear twice in the results.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

The best approach is usually two left joins:

SELECT ld.*, . . .
FROM letter_defn ld LEFT JOIN
     profile_defn po
     ON ld.orig_print_id = po.profile_id LEFT JOIN
     profile_defn pn
     ON ld.new_print_id = p.profile_id 
WHERE po.profile_id IS NOT NULL OR pn.profile_id IS NOT NULL;

This puts the columns from the two profiles in a single row. The query can make full use of indexes. You don't specify what you want the results to look like. If you want only one value from the two joins, then use COALESCE().

Upvotes: 2

sagi
sagi

Reputation: 40481

An alternative will be to use UNION or UNION ALL which might have better execution plan :

SELECT * FROM letter_defn ld 
JOIN profile_defn p ON ld.orig_print_id = p.profile_id 
UNION
SELECT * FROM letter_defn ld 
JOIN profile_defn p ON ld.new_print_id = p.profile_id;

The consequences is that or is usually slower because the compiler can no longer perform an index seek. Other then that, it's ok .

Upvotes: 3

Related Questions