a LEFT JOIN b ON a.c1 = b.c1 AND a.c2 = b.c2 AND a.c3 = b.c3 

With respect to determining which rows satisfy the join condition, both joins are semantically identical. 

With respect to determining which columns to display for SELECT *  expansion, the two joins are not semantically identical. The USING  join selects the coalesced value of corresponding columns, whereas the ON  join selects all columns from all tables. For the USING  join, SELECT *  selects these values: 

COALESCE(a.c1, b.c1), COALESCE(a.c2, b.c2), COALESCE(a.c3, b.c3) 

For the ON  join, SELECT *  selects these values: 

a.c1, a.c2, a.c3, b.c1, b.c2, b.c3 

With an inner join, COALESCE(a.c1, b.c1)  is the same as either a.c1  or b.c1  because both columns have the same value. With an outer join (such as LEFT JOIN ), one of the two columns can be NULL . That column is omitted from the result. 

•An ON  clause can refer only to its operands. 

Example: 

CREATE TABLE t1 (i1 INT); CREATE TABLE t2 (i2 INT); CREATE TABLE t3 (i3 INT); SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3; 

The statement fails with an Unknown column ’i3’ in ’on clause’  error because i3  is a column in t3 , which is not an operand of the ON  clause. To enable the join to be processed, rewrite the statement as follows: 

SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3); 

• JOIN  has higher precedence than the comma operator ( , ), so the join expression t1, t2 JOIN t3 is interpreted as (t1, (t2 JOIN t3)) , not as ((t1, t2) JOIN t3) . This affects statements that use an ON  clause because that clause can refer only to columns in the operands of the join, and the precedence affects interpretation of what those operands are. 

Example: 

CREATE TABLE t1 (i1 INT, j1 INT); CREATE TABLE t2 (i2 INT, j2 INT); CREATE TABLE t3 (i3 INT, j3 INT); INSERT INTO t1 VALUES(1, 1); INSERT INTO t2 VALUES(1, 1); INSERT INTO t3 VALUES(1, 1); SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3); 

The JOIN  takes precedence over the comma operator, so the operands for the ON  clause are t2  and t3 . Because t1.i1  is not a column in either of the operands, the result is an Unknown column ’t1.i1’ in ’on clause’  error. 

To enable the join to be processed, use either of these strategies: 

•Group the first two tables explicitly with parentheses so that the operands for the ON  clause are (t1, t2)  and t3 : 

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3); 

