How to read / make sense this multiple JOINS SQL query properly?

#help #sql

The query goes like this

SELECT
...
FROM
    A
    RIGHT OUTER JOIN (
        SELECT
            b1,
            b2,
            b3
        FROM
            B
    ) AS BB
    INNER JOIN (
        SELECT
            c1,
            c2,
            c3
        FROM
            C
    ) AS CC ON BB.b1 = CC.c1
    INNER JOIN D ON CC.c2 = D.d2 ON A.a2 = D.d2

That query above returns a table. The problem is the syntax is so weird to me. I can't read and make sense of it the way the writer intended. I didn't even know which table is on which side.

Let's be friend 👋

Top comments (1)

geraldew • Dec 20 '21

Well, it's certainly a curious one.

In my experience, SQL dialects vary about how to handle nested ON clauses - e.g. some require bracketing, some don't.

At a guess, here's a sequence of re-edits that might make sense of it. I'm assuming I can start with a swap around of the OUTER join, and then I rearrange to have a sequence of INNER joins followed by a LEFT OUTER (as that's the form I'm mentally comfortable with).

SELECT
...
FROM
    A
    RIGHT OUTER JOIN BB
    INNER JOIN CC ON BB.b1 = CC.c1
    INNER JOIN D ON CC.c2 = D.d2 
    ON A.a2 = D.d2

SELECT
...
FROM
    A RIGHT OUTER JOIN BB
    INNER JOIN CC ON BB.b1 = CC.c1
    INNER JOIN D ON CC.c2 = D.d2 
    ON A.a2 = D.d2

SELECT
...
FROM
    BB LEFT OUTER JOIN A 
    INNER JOIN CC ON BB.b1 = CC.c1
    INNER JOIN D ON CC.c2 = D.d2 
    ON A.a2 = D.d2

SELECT
...
FROM
    BB LEFT OUTER JOIN A ON A.a2 = D.d2
    INNER JOIN CC ON BB.b1 = CC.c1
    INNER JOIN D ON CC.c2 = D.d2

SELECT
...
FROM
    BB 
    INNER JOIN CC ON BB.b1 = CC.c1
    INNER JOIN D ON CC.c2 = D.d2 
    LEFT OUTER JOIN A ON A.a2 = D.d2

DEV Community

How to read / make sense this multiple JOINS SQL query properly?

Top comments (1)

Read next

[HELP] Best practices for pagination using EF Core 8

JSX in React

Step-by-Step Guide: Installing Jenkins on Docker

Php features: first-class callable syntax