title: Oracle - Conditional Join eliminating additional joins where unnecessary
body: <p>Let’s say I have a simplified table structure as follows:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Query something like so:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.-</p>
  
  <p>But also I’d need a WHERE condition on TABLE D also.</p>
  
  <p>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.</p>
  
  <p>Very simplified data so apologies!</p>
  
  <pre><code>| 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   |
  </code></pre>
  
  <p>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.</p>