Where to use AND and WHERE in JOINS - SQL Server

the OUTER JOIN means that even if the other table2 doesn't contain the record a.id = b.id
you have the raw of table1 with null fields of table2

query 1 basically would append the comparision after and to the on clause ... it is the same as this:

SELECT a.*, b.*
FROM Table1 a
INNER JOIN Table2 b
ON (a.id = b.id  AND a.col = 'value')

query 2 will join using a.id = b.id and select rows having a.col = 'value'

Hope that answers your question.

and here are the examples shown for all types of joins:
http://en.wikipedia.org/wiki/Join_(SQL)

Table1:

id      col
1       A
2       B
3       C

Table2:

id       col
1        AA
2        BB

Now let us consider:

SELECT a.*, b.* FROM Table1 a LEFT OUTER JOIN Table2 b ON a.id = b.id AND a.col = 'C'

SELECT a.*, b.* FROM Table1 a LEFT OUTER JOIN Table2 b ON a.id = b.id WHERE a.col = 'C'

How they are different ? Please provide the query which shows they are diffferent.

Thanks

when using and within the on clause (like in the first query) you should compare columns, values make no sense there.

something like (a.id = b.id AND a.col = b.col) makes more sense

try these two queries and you'll see the difference:

SELECT a.*, b.* FROM Table1 a LEFT OUTER JOIN Table2 b ON a.id = b.id AND a.col =b.col

SELECT a.*, b.* FROM Table1 a LEFT OUTER JOIN Table2 b ON a.id = b.id WHERE a.col = b.col

So Moral of the story:

SELECT a.*, b.* FROM Table1 a LEFT OUTER JOIN Table2 b ON a.id = b.id WHERE a.col = b.col

WHERE condition is applied to the whole result set. But

SELECT a.*, b.* FROM Table1 a LEFT OUTER JOIN Table2 b ON a.id = b.id AND a.col = b.col

It will be done when joining is happening. If we did not find any matching rows then it will give NULL values but all the 3 rows are returned.

Please correct me if i am wrong !!

Thanks

that is correct...

Great !! This is what i am looking for:

SELECT a.*, b.* FROM Table1 a LEFT OUTER JOIN Table2 b ON a.id = b.id AND a.col = 'C'

This means you are selecting all the rows in Table1 but only joining on records where col = 'C'

SELECT a.*, b.* FROM Table1 a LEFT OUTER JOIN Table2 b ON a.id = b.id WHERE a.col = 'C'

You are filtering table1 to only have records where col = 'C'

viralypatel:

Do you agree ?

Thanks

query two description is correct .... query 1 is sort of incorrect because when joining the on clause should be comparing columns on which it joins the tables. hard-coding a value there would not make sense.

Query one is very valid and there are many occasions where you need to run such queries.

It does make alot of sense depending on what you are processing

Just a trivial example

select a.*, b.name, c.name
from table1 a
left outer join table2 b on b.id = a.id and a.location = 'nj'
left outer join table3 c on c.id = a.id and a.location = 'ny'

or

select a.*, coalesce(b.name, c.name) [name]
from table1 a
left outer join table2 b on b.id = a.id and a.location = 'nj'
left outer join table3 c on c.id = a.id and a.location = 'ny'

JOIN conditions are evaluated/processed before WHERE conditions.  This applies to both INNER and OUTER joins.  OUTER joins will often only produce the desired results if some conditions are moved to the WHERE clause, so that the JOIN can be processed *before* applying the condition, not as part of the JOIN.

For an INNER JOIN, your original queries should always produce the same final result, but the first one *may* be more efficient (or may not, because of very small tables or SQL might "rewrite" the query to "move" the evaluation in the 2nd query "up", so that they essentially work the same way).

in case of multi join it does hold true ... like in ewangoya's second example above ....

To easy remember how the 1 clause affect the end result keep this in mind:

1. ON clause define the relation between the 2 table so it will affect only the combination of their rows
2. WHERE clause affects the WHOLE data set AFTER the JOIN was applied.

In the case of INNER join the results will be the same because this type of join only allows in the result the rows that are in the BOTH tables that satisfy the conditions in the ON clauses. Rows that don't comply with the condition will be excluded from BOTH tables.

In the case of OUTER join all the rows in the one table are kept and matched, where the case, with the rows from the second table. If there is no match in the second table NULL values are returned next to the first table rows. Normally if a condition is in the ON clause it will only affect the second table while all the rows in the first will still be returned. If you move the condition in the WHERE clause then both table will be affected.

However in you example because you compare a column in first table(a) to a value and not to a column in the second table the filter will only affect the a table which actually controls the whole query so teh result will still be the same even if you would use OUTER join instead of INNER.

>> in case of multi join it does hold true ... like in ewangoya's second example above .... <<

As I noted earlier, It's based on INNER JOINs vs OUTER JOINs, not whether it's more than one JOIN or not.

For mutliple INNER JOINs, you should get similar results whether you specify a condition in the ON clause of a JOIN or in the WHERE clause.

So after all these comments, can I conclude with these two statements ?

SELECT a.*, b.* FROM Table1 a LEFT OUTER JOIN Table2 b ON a.id = b.id AND a.col = 'C'

This means you are selecting all the rows in Table1 but only joining on records where col = 'C'

SELECT a.*, b.* FROM Table1 a LEFT OUTER JOIN Table2 b ON a.id = b.id WHERE a.col = 'C'

You are filtering table1 to only have records where col = 'C'

Thanks

That should be correct

Your first query means:

Return ALL rows table a matched with table b if b.id= a.id and if a.col='C'; - in this case the a.col='C' filter is applied in the join process

You second query:

Return ALL rows table a matched with table b if b.id=a.id. After join keep only rows where a.col='C'; - the join is completed and then filtered i applied in the where clause.

Thanks to all who have participated in this discussion. Appreciate your great help !

Thanks

Zberteoc:

You are correct. You made it very clear. Thanks a lot !!

Thanks