2

I'm pretty sure this works universally across various SQL implementations. Suppose I have many-to-many relationship between 2 tables: 

Customer: id, name

has many:

Order: id, description, total_price

and this relationship is in a junction table:

Customer_Order: order_date, customer_id, order_id

Now I want to write SQL query to join all of these together, mentioning the customer's name, the order's description and total price and the order date:

SELECT name, description, total_price FROM Customer
JOIN Customer_Order ON Customer_Order.customer_id = Customer.id
JOIN Order = Order.id = Customer_Order.order_id

This is all well and good. This query will also work if we change the order so it's FROM Customer_Order JOIN Customer or put the Order table first. Why is this the case? Somewhere I've read that JOIN works like an arithmetic operator (+, * etc.) taking 2 operands and you can chain operator together so you can have: 2+3+5, for example. Following this logic, first we have to calculate 2+3 and then take that result and add 5 to it. Is it the same with JOINs?

Is it that behind the hood, the first JOIN must first be completed in order for the second JOIN to take place? So basically, the first JOIN will create a table out of the 2 operands left and right of it. Then, the second JOIN will take that resulting table as its left operand and perform the usual joining. Basically, I want to understand how multiple JOINs work behind the hood.

daremkd
  • 8,244
  • 6
  • 40
  • 66
  • 3
    As a minor note: your query will not work in most databases because `order` is a reserved word. But, fixing that, it should work. – Gordon Linoff Jan 07 '15 at 17:30
  • If your question is whether, like addition, inner joins (which `JOIN`, unqualified, means) are associative, then the answer is yes. They are also commutative if that isn't obvious (i.e, `a JOIN b` is the same as `b JOIN a`). However, outer joins are not commutative and they are also not guaranteed to be associative. – David Faber Jan 07 '15 at 17:37
  • Sorry wasn't aware of that, just gave them as examples, you can take anything else you want that has many-to-many relationship. – daremkd Jan 07 '15 at 17:37
  • Also, I'm not sure the precedence of inner joins is defined. Because inner joins are associative, they can be done in any order. I would think a good optimizer would try to take the shortest/best path to get results (not always successfully). The precedence could depend on other conditions, such as indexes, etc. – David Faber Jan 07 '15 at 17:39
  • @DavidFaber my question is WHY are they associative? How does this whole multiple JOIN process works behind the hood (let's suppose they're all INNER joins). so if I first inner join table 'a' and 'b', and then add table 'c', how will this all work? Will the DBMS first join 'a' and 'b' and then add 'c' to ('a'+'b')? Or will it do 'a'+'b' and 'b' + 'c' separately and then do some other thing to combine everything? – daremkd Jan 07 '15 at 17:40
  • @DavidFaber [This discussion](http://dba.stackexchange.com/questions/12110/the-order-of-inner-joins#comment18861_12431) indicates that inner joins are not fully commutative in SQL (as opposed to the RM). – Air Jan 07 '15 at 17:40
  • 1
    OP may find [Does the join order matter in SQL?](http://stackoverflow.com/q/9614922/2359271) useful. – Air Jan 07 '15 at 17:42
  • 2
    do you remember Venn diagrams from school? Essentiall if you are INNER joining 3 tables, only things that match in all 3 will be in the "center" of the Venn diagram, so the order in which the tables or data are applied does not matter. In a SQL Server specific case, the Query Optimizer will look at all 3 tables, and and where conditions and determine the best way to physically get the data that the joined tables are asking for. – Jeremy Jan 07 '15 at 17:46
  • @AirThomas Speaking about INNER join with a junction table, I don't understand why it would work if we put the junction table first, so we have SELECT * FROM junction_table JOIN table1 ON __ JOIN table2 ON __. If we look at these in isolation, we're first joining junction_table with table1 and they do have a common ID column to connect them with. This isn't the case, however, if we do table1 JOIN table2 because they don't have any common columns. It makes most sense to put the junction tabble in the MIDDLE so we have table1 JOIN junction_table JOIN table2 but putting it at the beginning works. – daremkd Jan 07 '15 at 17:49
  • 1
    @daremkd SQL is supposed to be declarative; you tell it the result you want, not the path to take. Without using special syntax (straight join), the database engine is free to execute your query however it thinks is optimal. When you ask for an inner join, you're asking for the intersection of N sets. If N sets intersect, they intersect in exactly one way; so the order does not matter. – Air Jan 07 '15 at 17:58
  • The following is a quick and comprehensive tutorial regarding `JOIN` and `Cartesian Product`: http://web.utk.edu/sas/OnlineTutor/1.2/en/60477/m71/m71_3.htm – J.S. Orris Jan 07 '15 at 21:46
  • check this question , it'll give u an overview about that http://stackoverflow.com/questions/1083676/understanding-how-join-works-when-3-or-more-tables-are-involved-sql – Hany Hassan Nov 06 '15 at 21:11

1 Answers1

1

In many ways I think ORMs are the bane of modern programming. Unleashing a barrage of underprepared coders. Oh well diatribe out of the way, You're asking a question about set theory. THere are potentially other options that center on relational algebra but SQL is fundamentally set theory based. here are a couple of links to get you started

Using set theory to understand SQL

A visual explanation of SQL

Jerry Zornes
  • 21
  • 3