CODEWITHSUNDEEP
sql
ryan lawrence
ryan lawrence

Reputation: 39

SQL query statements

I have 2 separate SQL queries and I can't figure out how they are different:

SELECT A.AccountName,
       T.Total
FROM   ACCOUNTS A
       LEFT OUTER JOIN (SELECT *
                        FROM   Totals
                        WHERE  PersonID = @PersonID
                               AND Yr = @Year) T
         ON A.AccountID = T.AccountID
ORDER  BY AccountName

SELECT A.AccountName,
       T.Total
FROM   ACCOUNTS A
       LEFT OUTER JOIN (SELECT *
                        FROM   Totals) T
         ON A.AccountID = T.AccountID
WHERE  PersonID = @PersonID
       AND Yr = @Year
ORDER  BY AccountName

Any help would be greatly appreciated.

Upvotes: 1

Views: 121

Answers (3)

Gerardo Lima
Gerardo Lima

Reputation: 6712

Whenever you put simple clauses for an OUTER JOIN table, you have to test if the the outer table is present before testing the condition, or else, in practical terms, you transformed the outer join to an inner join. In you case:

-- original
SELECT A.AccountName, T.Total
FROM ACCOUNTS A
LEFT OUTER JOIN (SELECT *
    FROM Totals
    WHERE PersonID = @PersonID
    AND Yr = @Year
) T ON A.AccountID = T.AccountID
ORDER  BY AccountName;

-- modified with same result
SELECT A.AccountName, T.Total
FROM ACCOUNTS A
LEFT OUTER JOIN Totals T ON A.AccountID = T.AccountID
WHERE (1=2 -- syntactic sugar
  OR (T.AccountID IS NULL) -- this prevents outer joined rows to be excluded
  OR (T.PersonID = @PersonID AND T.Yr = @Year) -- original filter
)
ORDER  BY AccountName;

Upvotes: 0

Jamie F
Jamie F

Reputation: 23829

By applying the WHERE conditions after the outer join (the second query), you are potentially eliminating records from the result set. The first query will include every line from the ACCOUNTS table.

That is, in the first query, the returned table will have at least a row for every record in the ACCOUNTS table, with NULLs where there is no matching data in the Totals table. If there are multiple matches in the Totals table, then you will get multiple rows for that ACCOUNT.

The second query will only return rows in ACCOUNTS that match the parameters, and they must have a matching Totals row.

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 416179

The first query will only show a result for all Account records, whether or not an Account has a Total record that matches your @Year and @Person values (or even whether nor not it matches a Total record at all). It will have a NULL for the Total column for records that do not match, but there will be a result for all accounts. The second query will show only Accounts that have a total that matches the year and person. If there is no match, the Account record will not appear in your results at all.

And the 2 queries should really be written more like this (no need for subqueries here):

SELECT A.AccountName, T.Total
FROM   ACCOUNTS A
LEFT JOIN Totals T
         ON A.AccountID = T.AccountID AND T.PersonID = @PersonID AND T.Yr = @Year
ORDER  BY AccountName

SELECT A.AccountName, T.Total
FROM   ACCOUNTS A
LEFT JOIN Totals T ON A.AccountID = T.AccountID
WHERE  T.PersonID = @PersonID AND T.Yr = @Year
ORDER  BY AccountName

The above code is functionally equivalent to your originals.

Finally, it sounds like you may want something more like this:

SELECT A.AccountName, T.Total
FROM Accounts A
INNER JOIN Totals T ON T.AccountID = A.AccountID
WHERE T.PersonID = @PersonID AND T.Yr = @Year

In this query, it does not matter where you put your Year and Person conditions. The query will have the same result either way: an account record will only be shown if it matches a Totals record with the desired Year and Person. Accounts that don't match won't appear in the results at all.

Upvotes: 1

Related Questions