title: Forcing INNER JOIN / LEFT JOIN precedence and groups
body: <p>I'm having a bunch of tables let's say A, B1, B2, C1, C2.</p>
  
  <p>I'm selecting from A, joining on B1, itself joining on B2. I'm also selecting from A on C1, itself joining on C2, I mean:</p>
  
  <pre><code>   SELECT *
       FROM A
       JOIN B1 ON A.b1 = B1.id
       JOIN B2 ON B1.b2 = B2.id
  LEFT JOIN C1 ON A.c1 = C1.id
  LEFT JOIN C2 ON C1.c2 = C2.id
  </code></pre>
  
  <p>I want the whole query to fail if the B branch fail, which is cleanly done which those JOINs.</p>
  
  <p>But I also want the branch of "C"s to fail if <code>LEFT JOIN C2</code> fails (I don't want the informations from C1 in this case, I want NULLS), but I still want the whole query to succeed if C branch fails.</p>
  
  <p>So I need a kind of:</p>
  
  <pre><code>    SELECT *
        FROM A
        JOIN B1 ON A.b1 = B1.id
        JOIN B2 ON B1.b2 = B2.id
   LEFT JOIN (C1 ON A.c1 = C1.id
         JOIN C2 ON C1.c2 = C2.id)
  </code></pre>
  
  <p>But it's not an existing syntax as far as I know.</p>
  
  <p>So I'm currently implementing it either as:</p>
  
  <pre><code>   SELECT *
       FROM A
       JOIN B1 ON A.b1 = B1.id
       JOIN B2 ON B1.b2 = B2.id
  LEFT JOIN (SELECT *
               FROM C1
               JOIN C2 ON C1.c2 = C2.id) AS C ON A.c1 = C.c1
  </code></pre>
  
  <p>Either with views.</p>
  
  <p>Which works like a charm, allowing me to explicitly express which JOIN goes with which JOIN, and their precedences, but it's heavy to write and heavy to read, also a bit redundent.</p>
  
  <p>But is there another clean solution I missed?</p>
  
  <p>Test set:</p>
  
  <pre><code>DROP TABLE IF EXISTS A;
  CREATE TABLE A (b1 int, c1 int);
  
  DROP TABLE IF EXISTS B1;
  CREATE TABLE B1 (id int, b2 int);
  
  DROP TABLE IF EXISTS B2;
  CREATE TABLE B2 (id int);
  
  DROP TABLE IF EXISTS C1;
  CREATE TABLE C1 (id int, c2 int);
  
  DROP TABLE IF EXISTS C2;
  CREATE TABLE C2 (id int);
  
  INSERT INTO C2 VALUES (1), (2), (3); -- To remove to make join fail.
  INSERT INTO C1 VALUES (1, 1), (2, 2), (3, 3);
  
  INSERT INTO B2 VALUES (1), (2), (3);
  INSERT INTO B1 VALUES (1, 1), (2, 2), (3, 3);
  
  INSERT INTO A VALUES (1, 1), (2, 2), (3, 3);
  </code></pre>