loading..
Ðóññêèé    English
21:45
go

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:

  1. A [FULL | INNER] JOIN B = B [FULL | INNER] JOIN A

and

  1. (A [FULL | INNER] JOIN B) [FULL | INNER] JOIN Ñ = 
  2. A [FULL | INNER] JOIN (B [FULL | INNER] JOIN Ñ)

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

  1. A LEFT JOIN B = B RIGHT JOIN A

But its are associative, for example:

  1. (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
Execute
  1. WITH a(a_id) AS
  2. (SELECT * FROM (VALUES('1'),('2'),('3')) x(y)),
  3. b(b_id) AS
  4. (SELECT * FROM (VALUES('1'),('2'),('4')) x(y)), 
  5. c(c_id) AS
  6. (SELECT * FROM (VALUES('5'),('2'),('3')) x(y))
  7. SELECT a_id, b_id, c_id  FROM (a LEFT JOIN b ON a_id=b_id) INNER JOIN c ON b_id=c_id
  8. UNION ALL
  9. SELECT '','',''
  10. UNION ALL
  11. SELECT 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.


Bookmark and Share
Pages 1 2 3 4
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
https://exchangesumo.com/obmen/to/EURBKZT/ . Medostaff ïðîèçâîäñòâî âèíà
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.