CODEWITHSUNDEEP
sqldatabaseoracle-databasejoin
Basq
Basq

Reputation: 46

Oracle - Conditional Join eliminating additional joins where unnecessary

Let’s say I have a simplified table structure as follows:

Table A - ID (PK)
Table B - ID (PK), AID = FK to Table A
Table C - ID (PK), BID = FK to Table B
Table D - ID (PK), CID = FK to Table C

Query something like so:

SELECT * FROM TABLE_A TBLA
LEFT JOIN TABLE_B TBLB ON TBLA.ID = TBLB.AID
LEFT JOIN TABLE_C TBLC ON TBLB.ID = TBLC.BID
LEFT JOIN TABLE_D TBLD ON TBLC.ID = TBLD.CID

It’s relatively straight-forward but what I want to do is somewhat a conditional join in that I want all records from TABLE A but want to join TABLE B -> TABLE C -> TABLE D if the first join between TABLE A and TABLE B is satisfied, bearing in mind that I could change TABLE B -> TABLE C -> TABLE D joins to be INNER as there’ll exist in that initial join between TABLE A and TABLE B is satisfied.-

But also I’d need a WHERE condition on TABLE D also.

So essentially want to eliminate the LEFT JOIN’S between TABLE_B, TABLE_C, TABLE_D where join isn’t satisfied between TABLE_A and TABLE_B.

Very simplified data so apologies!

| Table A |
| ID      |
| 1       |
| 2       |
| 3       |
| 4       |
| 5       |

| Table B  |
| ID | AID |
| 1  | 5   |

| Table C  |
| ID | BID |
| 1  | 1   |

| Table D          |
| ID | CID | Value |
| 1  | 1   | ABC   |

The reason I want to eliminate the join is that for 4 of the 5 rows in Table A, I’m doing unnecessary joins across three tables to get the value in Table D.

Upvotes: 0

Views: 66

Answers (1)

BA.
BA.

Reputation: 934

You can use brackets with join, but not sure if that would help you!

SELECT * FROM 
TABLE_A TBLA
LEFT JOIN (TABLE_B TBLB 
           INNER JOIN TABLE_C TBLC ON TBLB.ID = TBLC.BID
           INNER JOIN TABLE_D TBLD ON TBLC.ID = TBLD.CID) ON TBLA.ID = TBLB.AID

This way, when you have A matches entries in B, but B is not matched in the chain to C and D, B data is not retrieved, and so for C

You mentioned that you need a where condition on table D? Does that mean that you always have to link to D?! Note that if you have a condition on D, the condition has to be satisfied in all cases. Hence, when no records are retrieved from D, no records will be retreived from the query (even with your initial outer join unless you used OR .. is null )

Upvotes: 0

Related Questions