Which table to keep base table when using Joins?

Question

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?

Answer 1

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

---

Answer 2

Generally,

when i read the document in oracle.it uses left side of table as Base table in join condition.

---

-- Bhavesh Thakkar

Sr.Database Administrator
My Blog
Linked In Profile

Answer 3

An 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

Answer 4

(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 = 0

which may exclude some A rows, namely those which corresponding B row col2 is   NULL or <> 0

---

Serg

Answer 5

(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

Regards
satheesh

Answer 6

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 conditions

and the order of the tables will be based on the business logic/table relationship hierarchy...

---

Krishnakumar S