SQL WHERE and JOIN clauses

A brief discussion of the difference in SQL between criteria in the WHERE and JOIN clauses.

Suppose we have a very simple system of two tables A & B. A is a list of Deptartments and B is a list of charges to a departmental account.

Table A

Dept

10

20

30

Table B

Dept Acct Amt

10    15    2

10    11    4

20    11    1

Management request a report that shows all Depts in table A, regardless of the entries shown in B, and the sum of Amount by Acct for dept 15.

You might be tempted to write the SQL as:

SELECT A.DEPT, B.ACCT, SUM(Amount) as TOTAL

FROM A LEFT OUTER JOIN B

ON A.DEPT=B.DEPT

WHERE ACCT = 15 OR ACCT IS NULL

GROUP BY A.DEPT, B.ACCT

However this will give you these results.

Dept Acct Amount

10    15    2

30    NULL  NULL

Dept 20 does not appear in the list. As you can guess from the sample data Dept 20 is eliminated by the where clause, although the

DEPT IS NULL allows Dept 30 to appear.

There is a simple fix to show all departments, move the criteria to the JOIN.

SELECT A.DEPT, B.ACCT, SUM(Amount) as TOTAL

FROM A LEFT OUTER JOIN B

ON A.DEPT=B.DEPT AND ACCT = 15

GROUP BY A.DEPT, B.ACCT

This yeilds

Dept Acct Amount

10    15    6

20    NULL  NULL

30    NULL  NULL

Exactly what was requested.

Here is a little SQL which demonstrates the results.

SELECT A.DEPT, B.ACCT, SUM(AMT) AS AMOUNT

FROM

(SELECT 10 AS DEPT

UNION

SELECT 20

UNION

SELECT 30 ) A

LEFT OUTER JOIN

(

SELECT 10 AS DEPT, 15 AS ACCT, 2 AS AMT

UNION

SELECT 10, 11 , 4

UNION

SELECT 20, 11 , 1

) B

ON A.DEPT=B.DEPT AND B.ACCT=15

--WHERE B.ACCT=15 OR B.ACCT IS NULL

GROUP BY A.DEPT, B.ACCT