|
Return to Newsletter Contents...
SQL - There's no difference between a Joins and a Where clause
- 'Joins don't buy anything'
by: Charles Tournear, Sr. Consultant, MCT, MCSE, MCSD, MCDBA, CRCP
The SQL language has been an ANSI standard for requesting data from relational
database systems since 1986. But the
standards have changed since there original definition.
In the ANSI standard 1986, 1988, and 1992, joins were defined with a
table list and connected by using a where clause.
Starting with ANSI standard 1999 and higher the syntax has changed to
using a JOIN and ON clause.
Examples of the ANSI standard 1992:
Inner Join (only retrieve rows that both tables have one or more fields in
common)
SELECT field1, field2
FROM table1,
table2
WHERE
table1.PKfield = table2.FKfield
AND field1 = ‘value’
Left Outer Join (retrieve all rows
from table on the left of the = sign and only matching rows from the other
table)
SELECT field1, field2
FROM table1,
table2
WHERE
table1.PKfield *= table2.FKfield
AND field1 = ‘value’
Right Outer Join (retrieve all rows from table on the right of the = sign and
only matching rows from the other table)
SELECT field1, field2
FROM table1,
table2
WHERE
table1.PKfield =* table2.FKfield
AND field1 = ‘value’
Examples of the same queries using ANSI standard 1999 syntax:
Inner Join (only retrieve rows that both tables have one or more fields in
common)
SELECT field1, field2
FROM table1
INNER JOIN table2
ON
table1.PKfield = table2.FKfield
WHERE field1
= ‘value’
Left Outer Join (retrieve all rows
from table on the left of the = sign and only matching rows from the other
table)
SELECT field1, field2
FROM table1
LEFT OUTER JOIN table2
ON
table1.PKfield = table2.FKfield
WHERE field1
= ‘value’
Right Outer Join (retrieve all rows from table on the right of the = sign and
only matching rows from the other table)
SELECT field1, field2
FROM table1
RIGHT OUTER JOIN table2
ON
table1.PKfield = table2.FKfield
WHERE field1
= ‘value’
ANSI standard 1999 also added some additional join types that weren’t available
using 1992 WHERE syntax such as NATURAL JOIN and FULL JOIN.
Natural join automatically join two tables on all common field names.
Full join is a combination of a left outer and a right outer join to pull
data from both tables whether or not there are matching records or not.
Another change in the ANSI standard is how NULL‘s are handled.
In ANSI standard 92, the NULL key word was used to indicate blank in
every occurrence of its use.
Beginning with ANSI standard 1999 the keyword NULL function depends on how it is
used. “= NULL” is now considered an
invalid statement. To check for
blanks the new syntax is “IS NULL”.
Depending on how well a system follows the rules for ANSI standards, this could
really mess up the results if you use WHERE to define the link between two
tables instead of using JOIN syntax.
For example consider the 92 syntax:
SELECT field1, field2
FROM table1,
table2
WHERE
table1.PKfield = table2.FKfield
AND field1 = ‘value’
In the 1999 syntax if it is possible that PKfield or FKfield could be blank or
NULL, then the result of the = comparison would be invalid and cause unexpected
output. Conditional statements such
as IF, CASE, or WHERE treat any comparison to a value that could return NULL as
invalid. Where as the ON clause of a
JOIN always treats the NULL value as meaning blank.
To give an example, consider the following code:
DECLARE
@VAR1 int
DECLARE
@VAR2 int
SET @VAR1 =
1
SET @VAR2 =
NULL
IF @VAR1 =
@VAR2
SELECT ‘true’
ELSE
SELECT ‘false’
IF @VAR1 <>
@VAR2
SELECT ‘true’
ELSE
SELECT ‘false’
In the above example the first IF clause would return false, which we would
expect since 1 does not equal NULL.
The second IF clause would also return false, which we would not expect since it
is true that 1 does not equal NULL.
In both cases, the comparison of any value to NULL returns Invalid which in this
case is interpreted as a false result.
No error will occur.
Another issue with using the WHERE clause for Joins, was that the where clause
could become very confusing combining links and row filters in the same clause.
Which parts are actually linking tables and which are used to filter
which rows to return?
The statement then that there is no difference between using the WHERE clause
versus JOIN syntax, while it was true for ANSI standard 1992, it is definitely
NOT true for any system that supports ANSI standard 1999 or higher.
In general then to be compatible with ANSI and most systems that support
the current version of the SQL standard, you should use the JOIN syntax to link
tables. Each part of a query then has a
specific task. SELECT is to choose
columns of data for output; FROM is to determine how tables should be linked;
and WHERE is to select which rows from each table are to be returned or used in
calculations.
Go to Top |
Return to Newsletter Contents
|
