SQL joins and the order in which they're listed in the query

Question

I've been on the net for about an hour now, but I can't seem to get a straight answer from other places. I have a few questions about joins that I hope you can answer. I'll try to be as simple as possible.

Suppose I have two tables:

A    B
-    -
1    3
2    4
3    5
4    6

Values (1, 2) are unique to A, (3, 4) are common, and (5, 6) are unique to B.

Now I understand the purpose of both inner and outer(left and right) joins. My questions relate to the ORDER in which they're written.

For example, would the following two queries produce the same results?

SELECT * FROM A LEFT JOIN B ON A.A = B.B

SELECT * FROM B LEFT JOIN A ON B.B = A.A

Questions:

1. What determines the main table on the left that will have all entries listed? Is it the order that I write the tables on either side of that "LEFT JOIN" statement? Or is it based on the order of the tables in the ON condition?

2. Does the order of the ON condition matter? Or is it just used to link the tables together on a specific column for comparison?

3. What happens when I have more than two tables? Let's say there's a table C that I want to LEFT JOIN to the results of doing an A LEFT JOIN B:

   SELECT * FROM A 
   LEFT JOIN B ON A.A = B.B
   LEFT JOIN C ON ....
   

What goes there? Is it C.C = A.A? Or is it C.C = B.B?

I guess the question is, what table specifically do any additional LEFT JOIN's get joined to?

What's on that left side of the join?

Answer 1

Your two queries would not give you the same results.

SELECT * FROM A LEFT JOIN B ON A.A = B.B

would give you

1 null

2 null

3 3

4 4

while

SELECT * FROM B LEFT JOIN A ON B.B = A.A

would give you

3 3

4 4

5 null

6 null

1. The table before LEFT JOIN is the table that will have all its results listed, no matter what is on the table that appears after the LEFT JOIN statement. The order that you write your ON conditions "on" have no influence on what is displayed in the results.

2. Order of the ON conditions have no effects on what is returned

3. You would do something like the following:

SELECT *
FROM A 
   LEFT JOIN B ON B.ID = A.ID
       LEFT JOIN C ON C.ID = A.ID

In the above statement, all values from table A will be displayed; along with any values from table B or C that are also present in table A.

If you changed

LEFT JOIN C ON C.ID = A.ID

to

LEFT JOIN C ON C.ID = B.ID

then only values that are present in all three tables would be displayed. You are essentially saying, "give me all information that is present in table A, plus any data corresponding to the same value in table B, plus any data corresponding to the same value in table C". If the data does not exist in table B, then any data in table C would also not be present in the results.

LEFT JOIN C ON C.ID = A.ID 

can be visualized as

On the other hand,

LEFT JOIN C ON C.ID = B.ID

can be seen as

Now I put down numbers instead of letters in the sketches, but in my Picassos above 1 = A, 2 = B, 3 = C