Re: Aggregate Functions and Null
From: Itzik Ben-Gan (itzik_at_REMOVETHIS.SolidQualityLearning.com)
Date: 09/25/04
- Next message: Ed: "what's wrong"
- Previous message: Tom Moreau: "Re: Help with CONSTRAINT CHECK"
- In reply to: Drew: "Re: Aggregate Functions and Null"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 25 Sep 2004 10:26:02 -0500
Hi Drew,
> So you mean that in general if you want to filter the non-preserved table
> you have to filter in the ON clause.
> Am i thinking correct ?
Yes.
> Can i use the preserved table also in the same way. I know we can filter
> the
> preserved table using WHERE clause, but just wanted to know.
Well, there's an important difference. The ON clause is applied before
adding the outer rows, while the WHERE is applied after.
If you filter in the ON clause, you'll end up with ALL rows from the
preserved table in the resultset. If you filter in the WHERE clause, a row
that is removed by the filtered is gone.
e.g., when you join Customers LOJ Orders, and want to filter customers from
US, if you filter in the ON clause you'll end up with non-US customers in
the result. If you filter in the WHERE clause, you end up only with US
customers.
-- BG, SQL Server MVP www.SolidQualityLearning.com "Drew" <agarwalp@lucent.com> wrote in message news:Ok0xzkkoEHA.1644@tk2msftngp13.phx.gbl... > So you mean that in general if you want to filter the non-preserved table > you have to filter in the ON clause. > Am i thinking correct ? > Can i use the preserved table also in the same way. I know we can filter > the > preserved table using WHERE clause, but just wanted to know. > > Thnks > > "Itzik Ben-Gan" <itzik@REMOVETHIS.SolidQualityLearning.com> wrote in > message > news:eysqA0WoEHA.2784@TK2MSFTNGP14.phx.gbl... >> Hi Janet, >> >> An outer join returns all rows from the preserved table regardless of >> whether a match was found or not. >> However, if you filter on an attriburte from the non-preserved side (tbl2 > in >> your case) in the WHERE clause, which is applied after outer rows were >> added, tbl1 rows that found no match are going to be removed. Also, >> substituting a NULL representing an outer row (non-match) within the >> COUNT >> function, would take the non-match into consideration and add it to the >> count. >> So, instead of: filtering in the WHERE clause, filter in the ON clause >> (applied before adding outer rows. Also, instead of >> COUNT(isNull(tbl2.pgy, >> 0)) simply use: COUNT(tbl2.pgy). >> Without sample data and the desired results, it's hard to tell if I was >> on >> the right track, but here's my guess:\ >> >> SELECT tbl1.pgm, tbl1.pgy, COUNT(tbl2.pgy) AS myCount >> FROM tbl2 >> RIGHT OUTER JOIN tbl1 >> ON tbl2.pgm = tbl1.pgm >> AND tbl2.pgy = tbl1.pgy >> AND (tbl2.questionID = 12) >> GROUP BY tbl1.pgm, tbl1.pgy >> >> -- >> BG, SQL Server MVP >> www.SolidQualityLearning.com >> >> >> "Janet" <janetb@mtn.ncahec.org> wrote in message >> news:475301c4a16a$03da9b00$a501280a@phx.gbl... >> > I have one table with the 'parent' set of data for which >> > I need counts (tbl1). The table containing the results >> > (tbl2) may not have rows matching every item in tbl1, but >> > I'd like to show 0 if there's no match or null. There's >> > gotta be a way to do this, right? >> > >> > Any assistance greatly appreciated. >> > >> > SELECT tbl1.pgm, tbl1.pgy, COUNT(isNull(tbl2.pgy, 0)) AS >> > myCount FROM tbl2 RIGHT OUTER JOIN tbl1 ON tbl2.pgm = >> > tbl1.pgm AND tbl2.pgy = tbl1.pgy WHERE (tbl2.questionID = >> > 12) GROUP BY tbl1.pgm, tbl1.pgy >> >> > >
- Next message: Ed: "what's wrong"
- Previous message: Tom Moreau: "Re: Help with CONSTRAINT CHECK"
- In reply to: Drew: "Re: Aggregate Functions and Null"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
