   03:02

# Explicit join operations page 4

## Commutative & Associative Laws and Joins

Inner and full outer joins are both commutative and associative, i.e. the following is fair for them:

`A [FULL | INNER] JOIN B = B [FULL | INNER] JOIN A`

and

`(A [FULL | INNER] JOIN B) [FULL | INNER] JOIN С = A [FULL | INNER] JOIN (B [FULL | INNER] JOIN С)`

It is obvious that left/right joins are not commutative in view of

`A LEFT JOIN B = B RIGHT JOIN A`

But its are associative, for example:

`(A LEFT JOIN B) LEFT JOIN C = A LEFT JOIN (B LEFT JOIN C)`

From the practical point of view, associativity means that we might use no brackets defining the treatment order of joins.

However the law of associativity, which is fair for connections of the same type, is being broken when the joins of different types are used in a query. Let's show this on example.  Console
`WITH a(a_id) AS(SELECT * FROM (VALUES('1'),('2'),('3')) x(y)),b(b_id) AS(SELECT * FROM (VALUES('1'),('2'),('4')) x(y)), c(c_id) AS(SELECT * FROM (VALUES('5'),('2'),('3')) x(y))SELECT a_id, b_id, c_id  FROM (a LEFT JOIN b ON a_id=b_id) INNER JOIN c ON b_id=c_idUNION ALLSELECT '','',''UNION ALLSELECT a_id, b_id, c_id  FROM  a LEFT JOIN (b INNER JOIN c ON b_id=c_id) ON a_id=b_id;`

a_id    b_id    c_id
2    2    2

1    NULL    NULL
2    2    2
3    NULL    NULL

Results of two queries are being separated by blank row for convenience.

Let's notice that in absence of brackets we shall receive the result conterminous with result of the first query, as joins will be carried out in the order that they are written in.

 Pages 1 2 3 4  