|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Exact relational divisionabout relational division and exact relational division. There's more background in the article, but the second example, for exact relational division, has a hangar with some airplanes, and a bunch of pilots, and you want to know which pilots can fly all the planes in the hangar and no other planes. The article says this: -- start of quote from article SELECT PS1.pilot FROM PilotSkills AS PS1 LEFT OUTER JOIN Hangar AS H1 ON PS1.plane = H1.plane GROUP BY PS1.pilot HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar) AND COUNT(H1.plane) = (SELECT COUNT(plane) FROM Hangar); This says that a pilot must have the same number of certificates as there planes in the hangar and these certificates all match to a plane in the hangar, not something else. The "something else" is shown by a created NULL from the LEFT OUTER JOIN. Please do not make the mistake of trying to reduce the HAVING clause with a little algebra to: HAVING COUNT(PS1.plane) = COUNT(H1.plane) because it does not work; it will tell you that the hangar has (n) planes in it and the pilot is certified for (n) planes, but not that those two sets of planes are equal to each other. --- end of quote from article I didn't quite understand this until I realized that "Count(H1.Plane)" is the number of planes in the hangar that the pilot can fly, while "SELECT COUNT(plane) FROM Hangar" is the number of planes in the hangar. I wanted to rant about why so many SQL'ers insist on using table aliases for short, one-word table names; the query seems to be easier to read without the table aliases, as follows: SELECT PilotSkills.pilot FROM PilotSkills LEFT OUTER JOIN Hangar ON PilotSkills.plane = Hangar.plane GROUP BY PilotSkills.pilot HAVING COUNT(PilotSkills.plane) = (SELECT COUNT(plane) FROM Hangar) AND COUNT(Hangar.plane) = (SELECT COUNT(plane) FROM Hangar); Putting in table aliases (for most queries) seems to confuse the situation and make the query harder to read, since we have to make mental substitutions, plus there are now four table names (the original two, plus the two aliases). But without the aliases, it's maybe little trickier to see that the left side of the last clause, "Count(Hangar.plane)", is not the count of planes in the hangar, it's still the count of planes in the hangar that the pilot (each pilot) can fly. It's the count of the planes that remain after the join -- the Having is applied after the join and uses the (grouped) results of the join. It took me a little while to understand all this, and looking at the results of the left outer join without the "group by" or "having" clases helped a lot. Interesting. David Walker I personally believe that when I spend a lot of time trying to figure
something out, the value of what I discover doing that by far supersedes the value of knowledge acquired quickly. I've read that same article a few weeks ago and let me say this: the value went through the roof. :) ML "=?Utf-8?B?TUw=?=" <M*@discussions.microsoft.com> wrote in Yep!news:761C03F8-D67B-4C29-82AD-9B1EE3D365CC@microsoft.com: > I personally believe that when I spend a lot of time trying to figure > something out, the value of what I discover doing that by far > supersedes the value of knowledge acquired quickly. > > I've read that same article a few weeks ago and let me say this: the > value went through the roof. :) > > > ML David Putting Aliases on tables once seemed a cosmetic operation to most SQL
practitioners (except for self-joins, for those Aliases were always necessary.) In the newer SQL tables alias are necessary in a number of places. Derived tables, PIVOT, CROSS APPLY.... BTW, the third edition of Celko's Smarties has just come out. Cheers, Mark Bosley Show quote "DWalker" <n***@none.com> wrote in message news:eRKbaIWtFHA.3848@TK2MSFTNGP10.phx.gbl... > Joe Celko's article at www.dbazine.com/ofinterest/oi-articles/celko1 talks > about relational division and exact relational division. > > There's more background in the article, but the second example, for exact > relational division, has a hangar with some airplanes, and a bunch of > pilots, and you want to know which pilots can fly all the planes in the > hangar and no other planes. > > The article says this: > > -- start of quote from article > > SELECT PS1.pilot > FROM PilotSkills AS PS1 > LEFT OUTER JOIN > Hangar AS H1 > ON PS1.plane = H1.plane > GROUP BY PS1.pilot > HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar) > AND COUNT(H1.plane) = (SELECT COUNT(plane) FROM Hangar); > > This says that a pilot must have the same number of certificates as there > planes in the hangar and these certificates all match to a plane in the > hangar, not something else. The "something else" is shown by a created > NULL > from the LEFT OUTER JOIN. > > Please do not make the mistake of trying to reduce the HAVING clause with > a > little algebra to: > > HAVING COUNT(PS1.plane) = COUNT(H1.plane) > > because it does not work; it will tell you that the hangar has (n) planes > in it and the pilot is certified for (n) planes, but not that those two > sets of planes are equal to each other. > > --- end of quote from article > > I didn't quite understand this until I realized that "Count(H1.Plane)" is > the number of planes in the hangar that the pilot can fly, while "SELECT > COUNT(plane) FROM Hangar" is the number of planes in the hangar. > > I wanted to rant about why so many SQL'ers insist on using table aliases > for short, one-word table names; the query seems to be easier to read > without the table aliases, as follows: > > SELECT PilotSkills.pilot > FROM PilotSkills > LEFT OUTER JOIN Hangar > ON PilotSkills.plane = Hangar.plane > GROUP BY PilotSkills.pilot > HAVING COUNT(PilotSkills.plane) = (SELECT COUNT(plane) FROM Hangar) > AND COUNT(Hangar.plane) = (SELECT COUNT(plane) FROM Hangar); > > Putting in table aliases (for most queries) seems to confuse the situation > and make the query harder to read, since we have to make mental > substitutions, plus there are now four table names (the original two, plus > the two aliases). > > But without the aliases, it's maybe little trickier to see that the left > side of the last clause, "Count(Hangar.plane)", is not the count of planes > in the hangar, it's still the count of planes in the hangar that the pilot > (each pilot) can fly. It's the count of the planes that remain after the > join -- the Having is applied after the join and uses the (grouped) > results > of the join. > > It took me a little while to understand all this, and looking at the > results of the left outer join without the "group by" or "having" clases > helped a lot. Interesting. > > David Walker
Show quote
"Mark Bosley" <mark.nspam@lightcc.com> wrote in Yes, aliases are necessary for self-joins and derived tables especially, news:#bCQwpXtFHA.3500@TK2MSFTNGP09.phx.gbl: > Putting Aliases on tables once seemed a cosmetic operation to most SQL > practitioners (except for self-joins, for those Aliases were always > necessary.) > > In the newer SQL tables alias are necessary in a number of places. > Derived tables, PIVOT, CROSS APPLY.... > > BTW, the third edition of Celko's Smarties has just come out. > > Cheers, Mark Bosley > but when they are not necessary they just confuse me. The cosmetics of "titles as t" escapes me completely; "titles" is so much more meaningful than "t". And, the number of rememberable items in my brain can overflow! :-) David"DWalker" <n***@none.com> wrote in message ERROR MESSAGEnews:Odj23wXtFHA.1440@TK2MSFTNGP10.phx.gbl... > "Mark Bosley" <mark.nspam@lightcc.com> wrote in > news:#bCQwpXtFHA.3500@TK2MSFTNGP09.phx.gbl: > Yes, aliases are necessary for self-joins and derived tables especially, > but when they are not necessary they just confuse me. The cosmetics of > "titles as t" escapes me completely; "titles" is so much more meaningful > than "t". And, the number of rememberable items in my brain can overflow! > :-) > > David Brain Memory Overflow Break for the weekend and reboot Monday morning A lot of the time, they jsut make the names shorter. But when you
start nesting SELECTs, you can get some surprises about which scope a column name falls within if you leave them off. And while we are plugging SFS III, I would like to mention that I also have several other fine SQL books whose royalties allow me to eat and live indoors. "--CELKO--" <jcelko***@earthlink.net> wrote in Well, "t" is shorter than "titles", but less meaningful! Short is not news:1126298168.472203.189180@g43g2000cwa.googlegroups.com: > A lot of the time, they jsut make the names shorter. But when you > start nesting SELECTs, you can get some surprises about which scope a > column name falls within if you leave them off. > > And while we are plugging SFS III, I would like to mention that I also > have several other fine SQL books whose royalties allow me to eat and > live indoors. > always an advantage. I can type "titles" pretty fast, and read it even faster. I think I have one of your books at home. Here at the office, I have Henderson's Guru's Guide to Transact-SQL, which you wrote the foreward to, and one by Ben-Gan & Moreau, and one by Kalen Delaney, and so on... :-) David |
|||||||||||||||||||||||
Other interesting topics