Thursday, January 17, 2013 5:50 AM
This is always a matter of confusion to me when joining multiple tables:
(1) Which table to keep the base table when joining multiple tables with various INNER and LEFT joins?
(2) How to decide whether to use WHERE clause or AND condition after ON?
Thursday, January 17, 2013 6:07 AM
1. The "rule of thumb" says that you should start from the smallest table to larger table. In this way the JOIN will be performed on a smaller number of records
* In most cases the optimization engine willbuilt the execution plan according
2. again... no one rule for any situation, but the "rule of thumb" is to use the filter as soon as you can so the JOIN will be performed on a smaller number of records
Thursday, January 17, 2013 6:16 AM
Thursday, January 17, 2013 6:16 AMAn inner join is a join in which the values in the columns being joined are compared using a comparison operator.
In the ISO standard, inner joins can be specified in either the FROM or WHERE clause. This is the only type of join that ISO supports in the WHERE clause. Inner joins specified in the WHERE clause are known as old-style inner joins.
Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join. All rows from both tables are returned in a full outer join.
SQL Server uses the following ISO keywords for outer joins specified in a FROM clause:
LEFT OUTER JOIN or LEFT JOIN
RIGHT OUTER JOIN or RIGHT JOIN
FULL OUTER JOIN or FULL JOIN
Many Thanks & Best Regards, Hua Min
Thursday, January 17, 2013 6:53 AM
(1) If there's a 'symmetric' (INNER of FULL OUTER) join then order of two joined tables makes no difference regarding the result. But it may affect execution plan
In case of LEFT/RIGHT OUTER JOIN the result depends on the order of tables joined by definition of LEFT/RIGHT OUTER JOIN, see docs.
(2) Concerning OUTER joins , ON condition may or may not filter out rows from the result, while WHERE always will restrict the result. For example the query
select A.*, B.* from A left join B on A.col1=B.col1 and B.col2 = 0
will not exclude any A row and may produce more rows then the query
select A.*, B.* from A left join B on A.col1=B.col1 where B.col2 = 0which may exclude some A rows, namely those which corresponding B row col2 is NULL or <> 0
Thursday, January 17, 2013 6:53 AM
(1) In case of inner joins the order in which the tables are written in your query, SQL Optimizer have the capability to change the order (data access path)in which data is accessed to give better performance.
(2)During Inner Joins there is no difference when you use a where clause or a ON condition. Again optimizer moves the where clause to ON condition or the other way to optimize your query
And in case of LEFT JOINS, Never USE a WHERE clause on the RIGHT table, USE the ON Clause instead, Look for the term de-facto inner join in the forums
Thursday, January 17, 2013 7:16 AM
Additional to other's comments...I always use the following format, that is legible and easy to debug:
SELECT .... FROM TableA A INNER /[LEFT/RIGHT OUTER] JOIN TableB B ON A.KeyCol = B.KeyCol -- Joining Conditions WHERE --- Filter conditionsand the order of the tables will be based on the business logic/table relationship hierarchy...