Can I specify the order in which the joins occur?

Say I have three tables, A, B and C. Conceptually A (optionally) has one B, and B (always) has one C.

Table A:
a_id
... other stuff

Table B:
a_fk_id (foreign key to A.a_id, unique, primary, not null)
c_fk_id (foreign key to C.c_id, not null)
... other stuff

Table C:
c_id
... other stuff

I want to select All records from A as well as their associated records from B and C if present. However, the B and C data must only occur in the result if both B and C are present.

I feel like I want to do:

SELECT *
FROM
    A
    LEFT JOIN B on A.a_id=B.a_fk_id
    INNER JOIN C on B.c_fk_id=C.c_id

But Joins seem to be left associative (the first join happens before the second join), so this will not give records from A that don't have an entry in C.

AFAICT I must use sub queries, something along the lines of:

SELECT *
FROM
    A
    LEFT JOIN (
        SELECT * FROM B INNER JOIN C ON B.c_fk_id=C.c_id
开发者_Python百科    ) as tmp ON A.id = tmp.a_fk_id

but once I have a couple of such relationships in a query (in reality I may have two or three nested), I'm worried both about code complexity and about the query optimizer.

Is there a way for me to specify the join order, other than this subquery method?

Thanks.

---

In SQL Server you can do

SELECT *
FROM   a
       LEFT JOIN b
                 INNER JOIN c
                   ON b.c_fk_id = c.c_id
         ON a.id = b.a_fk_id  

The position of the ON clause means that the LEFT JOIN on b logically happens last. As far as I know this is standard (claimed to be ANSI prescribed here) but I'm sure the downvotes will notify me if it doesn't work in MySQL!

---

Edit: And that's what I get for talking faster than I think. My previous solution doesn't work because 'c' hasn't been joined yet. Let's try this again.

We can use a WHERE clause to limit the results to only those that match the criteria you're looking for, where C has a valid (IS NOT NULL) or B does not have a value (IS NULL). Like this:

SELECT *
FROM a
    LEFT JOIN b ON (b.a = a.a)
    LEFT JOIN c ON (c.b = b.b)
WHERE (c.c IS NOT NULL OR b.b IS NULL);

Without WHERE Results:

mysql> SELECT * FROM a LEFT JOIN b ON (b.a = a.a) LEFT JOIN c ON (c.b = b.b);
+------+------+------+------+------+
| a    | a    | b    | c    | b    |
+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 |
|    1 |    1 |    2 | NULL | NULL |
|    2 |    2 |    3 |    2 |    3 |
|    3 | NULL | NULL | NULL | NULL |
|    4 | NULL | NULL | NULL | NULL |
+------+------+------+------+------+

With WHERE Results:

mysql> SELECT * FROM a LEFT JOIN b ON (b.a = a.a) LEFT JOIN c ON (c.b = b.b) WHERE (c.c IS NOT NULL OR b.b IS NULL);
+------+------+------+------+------+
| a    | a    | b    | c    | b    |
+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 |
|    2 |    2 |    3 |    2 |    3 |
|    3 | NULL | NULL | NULL | NULL |
|    4 | NULL | NULL | NULL | NULL |
+------+------+------+------+------+

---

Yes, you use the STRAIGHT_JOIN for this.
When using this keyword the join will occur in the exact order that you specify.

See: http://dev.mysql.com/doc/refman/5.5/en/join.html

---

Well, I thought up another solution as well, and I'm posting it for completeness (Though I'm actually using Martin's answer).

Use a RIGHT JOIN:

SELECT 
    *
FROM
    b
    INNER JOIN c ON b.c_fk_id = c.c_id
    RIGHT JOIN a ON a.id = b.a_fk_id

I'm pretty sure every piece I've read about JOINS said that RIGHT JOINs were pointless, but there you are.