Basic SQL questions

Database
Enthusiast

Basic SQL questions

Hi all!

What is the difference IN THE RESULTS between giving a where clause after the JOIN condition and specifying an 'AND" condition ->JOIN ON a=b AND a= 'open'; Or rather will there be a diff?

Example:
Sel a.fld1, b.fld2
from table A
JOIN table B
on A.fld1 = B.fld 1 and b.fld3='open';

Sel a.fld1, b.fld2
from table A
JOIN table B
on A.fld1 = B.fld 1
where b.fld3='open';
3 REPLIES
Enthusiast

Re: Basic SQL questions

There's no difference between the two, only that the JOIN ON is cleaner.

Re: Basic SQL questions

The difference occurs when you do an outer join.

Compare SQL1
select t1.a, t2.a
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON t1.a = t2.a
WHERE t2.b = 1

versus SQL2
select t1.a, t2.a
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON t1.a = t2.a
AND t2.b = 1

Lets assume that in both table 1 and table 2, value 'a' runs from A to Z, one row per entry (for 26 entries).
In table 2, for column b, the first 10 rows have a value 1, and the remaining 16 entries b=2.

Sql 1 will return only 10 rows, because for table 2, the 16 rows fail the where clause (applied after the outer join) causing those rows to be eliminated.

In SQL 2, you will return 26 rows. The first 10 rows will return values for t1.a and t2.a. However rows 11 -26 will return a value for t1.a and NULL for t2.a as those rows do not match the outer join criteria.
Junior Supporter

Re: Basic SQL questions

The differences with the LEFT OUTER JOIN are explained with examples here (in spanish):

http://carlosal.wordpress.com/2009/06/03/ansi-outer-joins-parecido-no-es-igual/

HTH.

Cheers.

Carlos.