We help IT Professionals succeed at work.

Where to use AND and WHERE in JOINS - SQL Server

Hi,

I have following two SQL queries. Can you please explain me the differences between these two ? If the difference is in OUTER JOIN then please provide example on it.

QUERY: 1

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

Open in new window


QUERY: 2

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

Open in new window


Thanks
Comment
Watch Question

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)

Author

Commented:
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
Software Engineer
Commented:
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'
a.id      a.col    b.id   b.col
1       A            null   null
2       B            null   null
3       C            null   null

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'
a.id      a.col    b.id   b.col
3          C         null   null

Author

Commented:
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...

Author

Commented:
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.
Ephraim WangoyaSoftware Engineer

Commented:

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
Ephraim WangoyaSoftware Engineer

Commented:

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'
Ephraim WangoyaSoftware Engineer

Commented:
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'
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
>> 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.

Author

Commented:
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
Ephraim WangoyaSoftware Engineer

Commented:

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.

Author

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

Thanks

Author

Commented:
Zberteoc:

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

Thanks