January 18, 2010 at 2:41 am
Hi,
I have got a bit confusion on joins in sql.
Regarding inner join, I know that it first takes a cartesian join and then filters out rows
based on join criteria.
Does the same holds good for outer joins as well.
I tried to google it out but could not get the info I need.
Can some one please explain or point me to a link explaining in detail
the algo behind joins.
Also what’s better for which cases.
“JOIN ON(tableA.id=tableB.id)” or a regular select with “Where (tableA.id=tableB.id)”
Thanks..
January 18, 2010 at 3:06 am
Also what’s better for which cases.
“JOIN ON(tableA.id=tableB.id)” or a regular select with “Where (tableA.id=tableB.id)”
With inner joins where the filter is applied seems to make no difference. The optimizer generally sorts out the most efficient access. You can check this by looking at the query plan.
Regarding inner join, I know that it first takes a cartesian join and then filters out rows
based on join criteria.
The optimizer does not necessarally do this but the effect is the same.
Does the same holds good for outer joins as well.
With outer joins it is important to understand the order in which the clauses in a SQL statement are processed. These are roughly:
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. TOP
An outer join can be thought as being processed in the following manner:
1. The two tables are inner joined using the join and filter conditions in the ON clause.
2. Any missing rows in the outer table are added with NULL for the columns in the joined table.
3. The WHERE clause is then applied.
This means that where the filter conditions for the joined table are placed are important in an outer join. If they are placed in the ON clause you will probably get what you expect. If they are placed in the WHERE clause the effect will be the same as an inner join as the filter will never equal NULL.
(ps There are some cases where you can put the filter condtion in the WHERE clause and do condidtional testing for NULL although this is generally not very efficient. The important thing to understand is the order in which the clasues are processed.)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply