Parenthesis for JOIN clause

Question

WITH 
t1(col) AS(
    VALUES
    (1),(2),(3)
),
t2(col) AS(
    VALUES
    (1),(3)
),
t3(col) AS(
    VALUES
    (1),(2)
)

SELECT * FROM ((t1
LEFT JOIN t2 ON t1.col = t2.col)
LEFT JOIN t3 ON t1.col = t3.col);

I often see queries written this way (I mean parenthesis in SELECT).

Is there any difference between above and without parenthesis:

SELECT * FROM t1
LEFT JOIN t2 ON t1.col = t2.col
LEFT JOIN t3 ON t1.col = t3.col;

?

Answer 1

Parentheses can make a difference in JOIN statements. In fact, sometimes they are necessary (I'll give an example of that later).

In your particular case, the parentheses are redundant because:

FROM t1 LEFT JOIN 
     t2
     ON t1.col = t2.col LEFT JOIN
     t3
     ON t1.col = t3.col

Is interpreted as:

FROM (t1 LEFT JOIN 
      t2
      ON t1.col = t2.col
     ) LEFT JOIN
     t3
     ON t1.col = t3.col

Because this interpretation is to from left-to-right. Incidentally, this is also how arithmetic expressions are interpreted: a + b - c - d is interpreted as ((a + b) - c) - d). Exactly the same idea.

And this is how the second query is written.

But consider these two versions:

FROM t1 LEFT JOIN 
     t2
     ON t1.col = t2.col INNER JOIN
     t3
     ON t2.col = t3.col

(Note the last join condition changed to be between t2 and t3)

And what looks similar:

FROM t1 LEFT JOIN 
     (t2 INNER JOIN
      t3
      ON t2.col = t3.col
     )
     ON t1.col = t2.col 

Result in different results. In particular, the first keeps only rows in t1 that match in t3. The second keeps all rows in t1.

I should also point out that although the syntax of the previous example has to change due to the on clause, it could be written with natural join (which I abhor) as:

from t1 left natural join t2 inner join t3

versus:

from t1 left natural join (t2 inner join t3)

assuming that the join columns are unique to the table pairs.

I will also say that I do not think that commas in the FROM clause are common, except for code written in MS Access, which requires them. They are rarely seen in SQL written for other databases (in my experience).

Answer 2

Semantically, the part of the BNF -syntax look like:

tbl_expr := tbl_expr 'JOINOP' tbl_expr ;

, Where tbl_expr is "table expression". (such as SELECT ... FROM ... , OR VALUES(...) ), and JOINOP is one of the JOIN operators.

In SQL, this part of the syntax look like:

tbl_expr := tbl_expr 'TJOIN' tbl_expr 'ON' bool_expr ;

, where TJOIN is one of {[INNER] JOIN , LEFT [OUTER] JOIN , RIGHT [OUTER] JOIN , FULL [OUTER] JOIN }

, or: the special case (without a join condition) :

tbl_expr := tbl_expr 'XJOIN' tbl_expr ;

, where XJOIN is CROSS JOIN

And, there is an additional syntax rule:

tbl_expr := '(' tbl_expr ')' ;

So, you can put parentheses around any tbl_expr, just as you would do with scalar expressions. The JOINOPs all associate left-to-right, so you could use parentheses to overrule that.

And NATURAL JOIN is syntactic sugar. (and semantic poison, IMHO. Stay away from it!)

The USING(column_list) variant also is syntactic sugar, and less dangerous. But it is just another syntactic variant of the TJOIN