>>>Can someone help me understand this please? Below are 2 select statement that I expect would produce equal results but they do not. The only difference in the statements is the order of the join but one produces 30K records and the other 23K. The left join is also much faster that the right join. So what's going on here? My understanding is that the left join preserves all the records from the table on the left side and the right join preserves all the records on the right side. Since the left table in the first query is the same as the right table in the second, I would expect the results to be the same but they are not. So where am I wrong?
>>>
>>>
>>>

>>>SELECT pagcleanad as infoaddress,pagbusines as infobusiness, INT((mon1_empl+mon2_empl+mon3_empl)/3) as infoemploy, ;
>>>emp as q3Employ, address as q3address, trade_name as q3business ;
>>>FROM q3 ;
>>>left JOIN info ON address=pagcleanad AND trade_name=pagbusines
>>>
>>>SELECT pagcleanad as infoaddress,pagbusines as infobusiness, INT((mon1_empl+mon2_empl+mon3_empl)/3) as infoemploy, ;
>>>emp as q3Employ, address as q3address, trade_name as q3business ;
>>>FROM q3 ;
>>>right JOIN info ON pagcleanad=address AND pagbusines=trade_name
>>>

>>>
>>>Thanks
>>
>>SELECT ALWAYS produce the right results, but not ALWAYS the expected ones :-)
>>In the first SELECT you want ALL records from Q3 table and matching records from INFO.
>>In the second SELECT you want ALL records from INFO table and matching records from Q3.
>
>Thanks guys. Perhaps some more info is in order. The data in the 2 tables purports to be the same but is from different sources and so is not identical. We want to determine which is to more reliable source by comparing the 2 against each other. Each record contains address, businessname, and # of employees. So I want to produce a side by side comparison of the number of employees for each address/business match. My approach to do this is using Q3 table as a base, find the matching records in INFO without loosing any non-matching records. Hence:
>
>

>SELECT pagcleanad as infoaddress,pagbusines as infobusiness, INT((mon1_empl+mon2_empl+mon3_empl)/3) as infoemploy, ;
>emp as q3Employ, address as q3address, trade_name as q3business ;
>FROM info ;
>LEFT JOIN q3 ON pagcleanad = address AND pagbusines = trade_name
>

>
>Next I wanted to turn it around and preserve all the records from the INFO table. I expected this could be done by simply switching from a left to a right join like this.
>

>SELECT pagcleanad as infoaddress,pagbusines as infobusiness, INT((mon1_empl+mon2_empl+mon3_empl)/3) as infoemploy, ;
>emp as q3Employ, address as q3address, trade_name as q3business ;
>FROM info ;
>RIGHT JOIN q3 ON pagcleanad = address AND pagbusines = trade_name
>

>
>Note the only change is going from a LEFT to a RIGHT join. These 2 selects are then put together with a UNION ALL. I may be wrong but I believe this is producing the desired result. What surprised me was that the second select takes MUCH longer to run than the first. So as an experiment I elected to turn the second select around and make it a LEFT join which also required turning the equivalencies around. When I did this I found that this 3rd select is not equivalent to the second which is what has me confused. I had thought that
>
>LEFT JOIN q3 ON pagcleanad = address AND pagbusines = trade_name
>
>was equivalent to
>
>RIGHT JOIN q3 ON address=pagcleanad AND trade_name=pagbusines
>
>but it clearly is not. So when we talk about left and right what exactly are we talking about? Apparently it is not the order of the equivalency. If we say LEFT JOIN Q3 we are supposed to preserve all the records on the left, but which table is on the left if it is not determined by the order of the equivalency?
>
>Thanks for any further clarification. I guess I've been confused for a long time and didn't even know it.


LEFT is ALWAYS the table from FROM clause (or if you do further JOINs the FIRST table).

FROM Table1
     LEFT JOIN Table2 ON ..
     LEFT JOIN Table3 ON Table2.Some = Table3.Some

LEFT for the first JOIN is Table1, for the Second JOIN is Table2.
As Tamar said, the JOIN condition doesn't matters.