SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Kenneth Fisher / Fun with Outer Joins / Latest Posts

RE: Fun with Outer Joins

Steve Rosenbach — Wed, 24 Oct 2012 12:03:07 GMT

[i]Preserved and Unpreserved:[/i]I often use the terms "preserved" and "unpreserved" table. I think it's from some ORACLE documentation that I read about 15+ years ago, but I find it useful. [i]Bit Columns:[/i]In designing a truly normalized schema, I would do my best to avoid having a bit column in a table. Joe's example of Termination_Date versus Terminated_Y_N is a good one. It's a more "correct" way of getting to a fully normalized solution. Also, you can create an index on a Bit column, but it would be pretty much useless, as its not at all selective - the optimizer will probably never use it. Having said all that, I still liked the article - you got across the main point, and I liked the way you used the FULL JOIN to show the pattern and illustrate the principles involved.

RE: Fun with Outer Joins

marlon.seton — Thu, 20 Sep 2012 01:49:24 GMT

[quote]Often they are used to replicate data. Case in point is the [TerminationDate] of an employee. That column will be NULL for everyone who is still working and have a valid date for folks who have, or were, terminated. Therefore a [Terminated] bit column is not needed.[/quote]I would regard this as overloading the [TerminationDate] column by giving it two meanings (terminated yes or no and date thereof). In addition to a column on the main table that states the current status, I would generally have a table with a foreign key of the main table and two columns of 'status' and 'date/time of status' (maybe others such as 'who set the status', etc.) so I could view all changes of status in chronological order, thus allowing for a status going to one value and then to another. For the employee, I could then see the dates of actions such as 'hired', 'suspended', 'terminated', 'rehired'. I can add new status by allowing new values in the 'status' column on the subsidiary table rather than having to add new columns for each new status to the main table.

RE: Fun with Outer Joins

marlon.seton — Thu, 20 Sep 2012 01:20:30 GMT

[quote][b]thisisfutile (9/11/2012)[/b][hr]Like in a car, we don't reference left and right sides because that's relevant to whether or not I'm looking at it from the back or from the front. Of course, referencing driver-side and passenger-side is different based on the country you live in. Now that I think about it, like boats, I'm all for using port and starboard for cars. :-P[/quote]We use 'near side' for the side nearest the kerb and 'off side' for the other side. Then it doesn't matter which country you are in or where the driver and front seat passenger are in the car. True, this doesn't work for off-road driving but that's something I have no experience of so I'm not bothered.

RE: Fun with Outer Joins

Kenneth.Fisher — Tue, 18 Sep 2012 08:11:56 GMT

[quote][b]Charles Kincaid (9/18/2012)[/b][hr][quote][b]DB.Duck (9/17/2012)[/b][hr][quote][b]CELKO (9/9/2012)[/b][hr]...we do not use BIT flags in SQL...[/quote]You, sir, couldn't be more wrong. That is all.[/quote]Actually it's almost a great point but poorly stated. Let me give this a shot. For the most part you should avoid bit flags. Often they are used to replicate data. Case in point is the [TerminationDate] of an employee. That column will be NULL for everyone who is still working and have a valid date for folks who have, or were, terminated. Therefore a [Terminated] bit column is not needed.Then there is the whole aspect of indexing on bits. SQL Server is a quite fine product (all software has bugs so don't start) but even the greatest software has to make compromises. Indexing on a bit suffers. I'll leave further reading to you. There are many good articles and books.Bits are tempting. Recent versions will cram 8 bits into one byte of actual storage. Sounds cool. You create the first one at design time. You are sucked into a false sense as adding the next seven are very fast (only the meta data is changed). Now you add the ninth and wait as every page in your table is rewritten.[/quote]Ok so I'm willing to accept that bit flags should be used when appropriate. For example when you want to know if a professor has tenure or not, but don't care when they got it. At that point putting in a date field is wasteful. Or possibly a better example would be if you are storing the results of a questionnaire and have a number of true/false or yes/no questions. It's really all about what your data requirements are.I haven't read about the indexing problem of bit's but I certainly will at this point. Either way though I don't imagine it being all that helpful as the cardinality of a bit is bound to be terrible.As far as that 9th bit column. I could be wrong (and that happens far more frequently than I would like) but wouldn't the 9th bit column (adding 1 byte to the row) be the same effect as adding a char(1), tinyint, or really any data type?

RE: Fun with Outer Joins

Charles Kincaid — Tue, 18 Sep 2012 07:58:05 GMT

[quote][b]DB.Duck (9/17/2012)[/b][hr][quote][b]CELKO (9/9/2012)[/b][hr]...we do not use BIT flags in SQL...[/quote]You, sir, couldn't be more wrong. That is all.[/quote]Actually it's almost a great point but poorly stated. Let me give this a shot. For the most part you should avoid bit flags. Often they are used to replicate data. Case in point is the [TerminationDate] of an employee. That column will be NULL for everyone who is still working and have a valid date for folks who have, or were, terminated. Therefore a [Terminated] bit column is not needed.Then there is the whole aspect of indexing on bits. SQL Server is a quite fine product (all software has bugs so don't start) but even the greatest software has to make compromises. Indexing on a bit suffers. I'll leave further reading to you. There are many good articles and books.Bits are tempting. Recent versions will cram 8 bits into one byte of actual storage. Sounds cool. You create the first one at design time. You are sucked into a false sense as adding the next seven are very fast (only the meta data is changed). Now you add the ninth and wait as every page in your table is rewritten.

RE: Fun with Outer Joins

Kenneth.Fisher — Tue, 18 Sep 2012 07:43:34 GMT

[quote][b]vsarade (9/18/2012)[/b][hr]When executing the query with adding the FULL JOIN with where clause like below - SELECT Professor.Id AS [Professor.Id], Professor.ProfessorName, CASE Professor.HasTenure WHEN 1 THEN 'True' WHEN 0 THEN 'False' ELSE NULL END AS [Has Tenure], Class.ProfessorId AS [Class.ProfessorId], Class.ClassName, Class.ClassYear, Class.ClassSemester FROM Professor [b]FULL JOIN[/b] Class ON Professor.Id = Class.ProfessorId AND Class.ClassYear >= 2011 WHERE Professor.HasTenure = 'True' Then it is giving same results as adding LEFT OUTER JOIN.[/quote]Not really surprising. This is for the same reason that putting the "Class.ClassYear = 2011" condition into the where clause eliminates the effect of the LEFT OUTER JOIN and makes it act like an INNER JOIN. Because the condition "Professor.HasTenure = 'True'" is in the WHERE clause it will eliminate all of the entries where HasTenure is NULL. In other words it is eliminating all of the entries that the difference between FULL and OUTER join caused.

RE: Fun with Outer Joins

vsarade — Tue, 18 Sep 2012 02:47:00 GMT

When executing the query with adding the FULL JOIN with where clause like below - SELECT Professor.Id AS [Professor.Id], Professor.ProfessorName, CASE Professor.HasTenure WHEN 1 THEN 'True' WHEN 0 THEN 'False' ELSE NULL END AS [Has Tenure], Class.ProfessorId AS [Class.ProfessorId], Class.ClassName, Class.ClassYear, Class.ClassSemester FROM Professor [b]FULL JOIN[/b] Class ON Professor.Id = Class.ProfessorId AND Class.ClassYear >= 2011 WHERE Professor.HasTenure = 'True' Then it is giving same results as adding LEFT OUTER JOIN.

RE: Fun with Outer Joins

TheJrDBA — Mon, 17 Sep 2012 18:08:46 GMT

[quote][b]CELKO (9/9/2012)[/b][hr]...we do not use BIT flags in SQL...[/quote]You, sir, couldn't be more wrong. That is all.

RE: Fun with Outer Joins

cbrown 54140 — Fri, 14 Sep 2012 15:46:42 GMT

Very good article. I did get confused trying to follow through the FULL JOIN result set as it seemed to be missing 5 rows for the Selzer class. I eventually ran the code and confirmed that it IS missing 5 rows and the point of the ON clause was finally able to sink in.

RE: Fun with Outer Joins

Luis Cazares — Thu, 13 Sep 2012 08:09:50 GMT

[quote][b]thisisfutile (9/11/2012)[/b][hr][quote][b]Luis Cazares (9/10/2012)[/b][hr][quote][b]CELKO (9/9/2012)[/b][hr]The correct terms are "preserved table " and "unpreserved table" ...[/quote]Can you explain us these terms? I can't find any reference on the web.[/quote]While I'm sure Joe has a deeper, ANSI definition for these terms, I believe their simplest usage is as a reference point. In a LEFT join the LEFT table is the preserved table and the RIGHT is unpreserved. The reverse would be true in a RIGHT join. So in discussion it doesn't matter if we're talking about LEFT or RIGHT joins, we'll know which table is which using these two terms. Like in a car, we don't reference left and right sides because that's relevant to whether or not I'm looking at it from the back or from the front. Of course, referencing driver-side and passenger-side is different based on the country you live in. Now that I think about it, like boats, I'm all for using port and starboard for cars. :-P[/quote]That's a reasonable explanation, but there's no such thing as preserved or unpreserved table. As a matter of fact, it doesn't need to be a table to be in the statement, it could be a view, a TVF, a subquery, a CTE, etc. It could all be treater as a data set.

RE: Fun with Outer Joins

Kenneth.Fisher — Thu, 13 Sep 2012 07:56:07 GMT

[quote][b]pg53 (9/12/2012)[/b][hr]KennethJust to pick up on this point you made:[quote]"We" do use BITs quite often. They are a nice small data type where I can fit 8 of them into a byte. Unless you have a good reason not to use them that I've never heard of I think I'll continue to not waste disk space[/quote]My understanding is that this is the behaviour of the Bit field type anyway - is there a reason why you would do your own packing into a Tinyint?[/quote]Fair point. In this particular case I only ever put 1s and 2s in my classsemester column (fall/spring) so I could easily have put this into a Bit field. And to be honest since I only had 1 bit field in the Professor table I didn't save anything over a char(1). However I still feel that the appropriate data type for a flag (only ever going to be 2 options) is a bit. In the case of ClassSemester I could have included more semesters in the future (say summer) and was leaving room for it.All that being said my big complaint with what Joe had to say is that I used a very simple format. The minimum required to get my point across. While there were some minor points that could be argued (Id vs ProfessorId etc) they don't distract from the point of the article. Mr Celko's structure on the other hand was overly complex for what was needed. This is however just my opinion, and as my family knows my opinions should always be taken with a grain (tablespoon full) of salt.

RE: Fun with Outer Joins

pg53 — Wed, 12 Sep 2012 18:46:27 GMT

KennethJust to pick up on this point you made:[quote]"We" do use BITs quite often. They are a nice small data type where I can fit 8 of them into a byte. Unless you have a good reason not to use them that I've never heard of I think I'll continue to not waste disk space[/quote]My understanding is that this is the behaviour of the Bit field type anyway - is there a reason why you would do your own packing into a Tinyint?

RE: Fun with Outer Joins

David In BC — Tue, 11 Sep 2012 12:58:22 GMT

Joe,Thanks for the lesson.A question:Regarding "assembly language bits", we have a case where we need to track historic medical data for patients (problems), and track if it's resolved or not. We may not have a date (it could have been 20 years ago, and the patient does not remember). I am guessing that you are promoting a look-up table, with two values (Resolved, Unresolved). Also, "Your life will be easier if you learn to use row constructors: " - I guess it depends on the version of the DBMS the author is using. Some people are still on SQL Server 2005.David[quote][b]CELKO (9/9/2012)[/b][hr]The correct terms are "preserved table " and "unpreserved table" and your design is wrong. You have no keys, we do not use BIT flags in SQL, you believe in a magical "id" that changes from table to table, becoming a professor, a squid, a class, an automobile, etc. That is Kabahlah magice and nto RDBMS. You had only one professor, no key, a name that violates USPS standards and an assembly language bit flag. IDENTITY properties? And no DRI because of the bad non-schema. Yes, without keys and DRI, this is not RDBMS. You also do not understand temporal data. CREATE TABLE Professors(emp_id INTEGER NOT NULL PRIMARY KEY, professor_name VARCHAR(35) NOT NULL, tenure_date DATE); Your life will be easier if you learn to use row constructors: INSET INTO ProfessorsVALUES (1, 'Dr Coke', '2011-01-01'), (2, 'Dr Sprite', '2011-01-01'), (3, 'Dr 7-up', '2011-01-01'), (4, 'Mr Pepper', NULL), (5, 'Mr Fanta', NULL);Notice the use of a NULL for non-tenured professors. This still stinks and we ought to have a status and date range. But let's skip that design flaw for now. CREATE TABLE Classes(course_nbr INTEGER NOT NULL REFERENCES Catalog, semester_name CHAR(7) NOT NULL REFERENCES Semesters, PRIMARY KEY (course_nbr, semester_name) emp_id INTEGER REFERENCES Professors);The common temporal idiom in SQL is a look up table for reporting periods. I prefer a name that can be sorted with ISO-8601 data:CREATE TABLE Semesters(semester_name CHAR(7) NOT NULL PRIMARY KEY CHECK (semester_name LIKEB '[12][0-9][0-9][0-9]-S[1-3]'), semester_start_date DATE NOT NULL, semester_end_date DATE NOT NULL, CHECK(semester_start_date < semester_end_date); You can get the class name from the catalog and not waste space and time on it. What you had was not normalized. Courses have numbers; look at a college catalog. The NULL is my favorite instructor “TBD” (I do adjunct teaching and that means work).INSERT INTO Class_Schedule (class_nbr, semester_name, emp_id)VALUES (101 '2010-S1', 4), (101, '2010-S2', 4), (101, '2011-S1', 4), (101, '2012-S1', NULL), (101, '2012-S2', NULL), Etc. Now try your queries with a proper schema.[/quote]

RE: Fun with Outer Joins

Cliff Jones — Tue, 11 Sep 2012 12:03:28 GMT

You have a couple of misspelled words there Mr. celko. This did not hinder my understanding of the point you were making. Nor did Kenneth’s use of words and schema hinder my understanding of his.

RE: Fun with Outer Joins

Kenneth.Fisher — Tue, 11 Sep 2012 08:25:34 GMT

[quote][b]thisisfutile (9/11/2012)[/b][hr][quote][b]Luis Cazares (9/10/2012)[/b][hr][quote][b]CELKO (9/9/2012)[/b][hr]The correct terms are "preserved table " and "unpreserved table" ...[/quote]Can you explain us these terms? I can't find any reference on the web.[/quote]While I'm sure Joe has a deeper, ANSI definition for these terms, I believe their simplest usage is as a reference point. In a LEFT join the LEFT table is the preserved table and the RIGHT is unpreserved. The reverse would be true in a RIGHT join. So in discussion it doesn't matter if we're talking about LEFT or RIGHT joins, we'll know which table is which using these two terms. Like in a car, we don't reference left and right sides because that's relevant to whether or not I'm looking at it from the back or from the front. Of course, referencing driver-side and passenger-side is different based on the country you live in. Now that I think about it, like boats, I'm all for using port and starboard for cars. :-P[/quote]There you go. In a LEFT OUTER JOIN from now on we will call the left table the "driver's side" or "port" table and the right one the "passenger side" or "starboard" table. Reverse of course for a RIGHT join. :hehe:

RE: Fun with Outer Joins

thisisfutile — Tue, 11 Sep 2012 08:09:51 GMT

[quote][b]Luis Cazares (9/10/2012)[/b][hr][quote][b]CELKO (9/9/2012)[/b][hr]The correct terms are "preserved table " and "unpreserved table" ...[/quote]Can you explain us these terms? I can't find any reference on the web.[/quote]While I'm sure Joe has a deeper, ANSI definition for these terms, I believe their simplest usage is as a reference point. In a LEFT join the LEFT table is the preserved table and the RIGHT is unpreserved. The reverse would be true in a RIGHT join. So in discussion it doesn't matter if we're talking about LEFT or RIGHT joins, we'll know which table is which using these two terms. Like in a car, we don't reference left and right sides because that's relevant to whether or not I'm looking at it from the back or from the front. Of course, referencing driver-side and passenger-side is different based on the country you live in. Now that I think about it, like boats, I'm all for using port and starboard for cars. :-P

RE: Fun with Outer Joins

Kenneth.Fisher — Tue, 11 Sep 2012 06:21:26 GMT

[quote][b]Bill Talada (9/10/2012)[/b][hr]Setting STATISTICS TIME & IO ON will cause severe slowdowns. I generally write each table as a separate SELECT in the FROM clause as above because it always self documents and simultaneously optimizes. Often the sub-selects can be reused in other procs. I'm maintaining 1000+ custom procs in a world class application. Everything is fast although the databases are all under 100 GB.Give it another try![/quote]While I agree setting STATISTICS TIME & IO on will cause slowdowns I would think they would slow both queries down equally wouldn't they? I'll try running it on one of our bigger test servers and see what happens.

RE: Fun with Outer Joins

Bill Talada — Mon, 10 Sep 2012 19:32:50 GMT

Setting STATISTICS TIME & IO ON will cause severe slowdowns. I generally write each table as a separate SELECT in the FROM clause as above because it always self documents and simultaneously optimizes. Often the sub-selects can be reused in other procs. I'm maintaining 1000+ custom procs in a world class application. Everything is fast although the databases are all under 100 GB.Give it another try!

RE: Fun with Outer Joins

Kenneth.Fisher — Mon, 10 Sep 2012 16:44:17 GMT

[quote][b]Bill Talada (9/10/2012)[/b][hr]Good article. I just want to present a style of coding that prevents this situation. I often work with queries involving a dozen tables. Notice that each table is basically a separate select statement. If I didn't code in my style, I'd quickly get confused.[code="other"]select p.*, c.*FROM (-- professor data SELECT Professor.Id AS [Id], Professor.ProfessorName, CASE Professor.HasTenure WHEN 1 THEN 'True' WHEN 0 THEN 'False' ELSE NULL END AS [Has Tenure] FROM Professor WHERE Professor.HasTenure = 'True' ) as Pleft outer join (-- class data SELECT Class.ProfessorId AS [ProfessorId], Class.ClassName, Class.ClassYear, Class.ClassSemester FROM Class WHERE Class.ClassYear >= 2011 ) as c on p.Id = c.ProfessorId[/code][/quote]Interestingly enough one of my coworkers suggested exactly the same thing. I tried it while I was writing the article but it came up significantly slower than doing it the other way. Not noticeable with small data sets but if you turn STATISTICS TIME & IO on you can see it.

RE: Fun with Outer Joins

Bill Talada — Mon, 10 Sep 2012 15:05:12 GMT

Good article. I just want to present a style of coding that prevents this situation. I often work with queries involving a dozen tables. Notice that each table is basically a separate select statement. If I didn't code in my style, I'd quickly get confused.[code="other"]select p.*, c.*FROM (-- professor data SELECT Professor.Id AS [Id], Professor.ProfessorName, CASE Professor.HasTenure WHEN 1 THEN 'True' WHEN 0 THEN 'False' ELSE NULL END AS [Has Tenure] FROM Professor WHERE Professor.HasTenure = 'True' ) as Pleft outer join (-- class data SELECT Class.ProfessorId AS [ProfessorId], Class.ClassName, Class.ClassYear, Class.ClassSemester FROM Class WHERE Class.ClassYear >= 2011 ) as c on p.Id = c.ProfessorId[/code]

RE: Fun with Outer Joins

Luis Cazares — Mon, 10 Sep 2012 15:02:13 GMT

[quote][b]CELKO (9/9/2012)[/b][hr]The correct terms are "preserved table " and "unpreserved table" ...[/quote]Can you explain us these terms? I can't find any reference on the web.

RE: Fun with Outer Joins

thisisfutile — Mon, 10 Sep 2012 15:00:41 GMT

[quote][b]adamg 27214 (9/10/2012)[/b][hr]Using a function on a column in a where clause will not allow your query to use an index. That is why instead of isnull you want to use "Where col1 = 'a' OR Col1 IS NULL..."[/quote]Roger that. Most of the querying I do is ad-hoc on small datasets and the isnull function is a personal "at-a-glance" way for me to see that I'm including the null results on the RIGHT table (Bad habit? Yes. In production? No). For me it's easier when I'm troubleshooting because the less "OR" and "AND" structures I have to logic my way through during a troubleshooting session the better off I am. Thanks for pointing that out though. I constantly feel like I learn something on this site. I'm still amazed that I never realized two result sets are produced by just moving something from the WHERE clause up to the ON clause. I felt a "click" in my brain today and at 40 years of age I thought I was getting past most of those. It's good to know I can still learn. :-P

RE: Fun with Outer Joins

adamg 27214 — Mon, 10 Sep 2012 13:08:17 GMT

[quote][b]CELKO (9/9/2012)[/b][hr]The correct terms are "preserved table " and "unpreserved table" and your design is wrong. You have no keys, we do not use BIT flags in SQL, you believe in a magical "id" that changes from table to table, becoming a professor, a squid, a class, an automobile, etc. That is Kabahlah magice and nto RDBMS. You had only one professor, no key, a name that violates USPS standards and an assembly language bit flag. IDENTITY properties? And no DRI because of the bad non-schema. Yes, without keys and DRI, this is not RDBMS. You also do not understand temporal data. CREATE TABLE Professors(emp_id INTEGER NOT NULL PRIMARY KEY, professor_name VARCHAR(35) NOT NULL, tenure_date DATE); Your life will be easier if you learn to use row constructors: INSET INTO ProfessorsVALUES (1, 'Dr Coke', '2011-01-01'), (2, 'Dr Sprite', '2011-01-01'), (3, 'Dr 7-up', '2011-01-01'), (4, 'Mr Pepper', NULL), (5, 'Mr Fanta', NULL);Notice the use of a NULL for non-tenured professors. This still stinks and we ought to have a status and date range. But let's skip that design flaw for now. CREATE TABLE Classes(course_nbr INTEGER NOT NULL REFERENCES Catalog, semester_name CHAR(7) NOT NULL REFERENCES Semesters, PRIMARY KEY (course_nbr, semester_name) emp_id INTEGER REFERENCES Professors);The common temporal idiom in SQL is a look up table for reporting periods. I prefer a name that can be sorted with ISO-8601 data:CREATE TABLE Semesters(semester_name CHAR(7) NOT NULL PRIMARY KEY CHECK (semester_name LIKEB '[12][0-9][0-9][0-9]-S[1-3]'), semester_start_date DATE NOT NULL, semester_end_date DATE NOT NULL, CHECK(semester_start_date < semester_end_date); You can get the class name from the catalog and not waste space and time on it. What you had was not normalized. Courses have numbers; look at a college catalog. The NULL is my favorite instructor “TBD” (I do adjunct teaching and that means work).INSERT INTO Class_Schedule (class_nbr, semester_name, emp_id)VALUES (101 '2010-S1', 4), (101, '2010-S2', 4), (101, '2011-S1', 4), (101, '2012-S1', NULL), (101, '2012-S2', NULL), Etc. Now try your queries with a proper schema.[/quote]wow... beating up the author because of the design correctness of the example in an article about joins? it was just an example by the way. He was giving some example code so people could try it themselves (and so the article made sense). Get off your high horse and understand the article was not about database design. It was about joins....

RE: Fun with Outer Joins

Charles Kincaid — Mon, 10 Sep 2012 13:04:52 GMT

Ken,It's a great article.Yep as a simple teaching example I get your design. I also get what Joe is saying, although he has often said it better, in that it winds up validating bad design by being used as an example. The "relationship" table (Catalog) is used to connect [Professor] to [Class]. For your next eaxample you might choose something like [SalesOrder] and [SalesOrderActivity].Joe,Keep up the good fight! I had one VP take me to task about my penchant for normalization as being "abnormal". I pointed out that we have a mediumn sized database in production that is small, robust, comprehensive, and quick. I learend a lot from your books. To everybody else,Take this to heart in that the real answer here is not so much how the joins work but the fact that the way SQL Server processes your execution can surprise you if you are not careful. The key here is that the WHERE clause seems to be processed after all the joins have taken place.

RE: Fun with Outer Joins

adamg 27214 — Mon, 10 Sep 2012 13:02:23 GMT

[quote][b]thisisfutile (9/10/2012)[/b][hr]I really learned from this article! I've been working with SQL Server for 7 years now and I had no idea the results were different if I took a filter from the WHERE clause and put it into the ON clause...I always assumed the results were going to be the same. I personally knew the right table results were being filtered with the WHERE clause (Dr Sprite and Dr 7-Up were being excluded) because I've been down that bumpy road many times. I've always dealt with this situation by adding "ISNULL" into the where clause.AND ISNULL(class.classyear,2011)>=2011Not a pretty solution but NOW I know there is a better way!Thanks for the article AND it's simplicity. I get what Joe Celko was driving at, but I find simple examples to be refreshing...though it could probably be argued that is why I'm a 7 year veteran and still learning what many DBA's consider elementary.[/quote]Using a function on a column in a where clause will not allow your query to use an index. That is why instead of isnull you want to use "Where col1 = 'a' OR Col1 IS NULL..."

RE: Fun with Outer Joins

David Jackson — Mon, 10 Sep 2012 11:00:27 GMT

The way I always remember this is thus: If you refer to the right hand table anywhere is the where clause, you immediately turn that left join into a inner join. That's the effect you see anyway.Putting the predicate in the ON clause keeps it a left join. Unless of course you are doing an anti-join, where you want all records from Set A not in Set B, so you use [code="other"]select cols from Set_A aleft join Set_B bOn a.col1 = b.col1where b.col1 is NULL[/code]That is the only time I refer to a column from the right hand table involved in a left outer join in the where clause.HTHDave J

RE: Fun with Outer Joins

thisisfutile — Mon, 10 Sep 2012 10:04:40 GMT

I really learned from this article! I've been working with SQL Server for 7 years now and I had no idea the results were different if I took a filter from the WHERE clause and put it into the ON clause...I always assumed the results were going to be the same. I personally knew the right table results were being filtered with the WHERE clause (Dr Sprite and Dr 7-Up were being excluded) because I've been down that bumpy road many times. I've always dealt with this situation by adding "ISNULL" into the where clause.AND ISNULL(class.classyear,2011)>=2011Not a pretty solution but NOW I know there is a better way!Thanks for the article AND it's simplicity. I get what Joe Celko was driving at, but I find simple examples to be refreshing...though it could probably be argued that is why I'm a 7 year veteran and still learning what many DBA's consider elementary.

RE: Fun with Outer Joins

Kenneth.Fisher — Mon, 10 Sep 2012 09:20:09 GMT

First I would like to appologize if my choice of test data is throwing people off. The data and table layout was meant to be simple and easy to follow so it didn't get in the way of the description of the difference between the ON clause and the WHERE clause.Second I'll address a few points.[quote]The correct terms are "preserved table " and "unpreserved table" [/quote]Never heard of either. What are they the correct terms for?[quote]and your design is wrong. [/quote]My design is correct for the purpose. It's simple. I didn't need or want a complicated design to demonstrate a subject that can be complicated enough for a lot of people.[quote]we do not use BIT flags in SQL[/quote]"We" do use BITs quite often. They are a nice small data type where I can fit 8 of them into a byte. Unless you have a good reason not to use them that I've never heard of I think I'll continue to not waste disk space.[quote]Your life will be easier if you learn to use row constructors: INSET INTO ProfessorsVALUES (1, 'Dr Coke', '2011-01-01'), (2, 'Dr Sprite', '2011-01-01'), (3, 'Dr 7-up', '2011-01-01'), (4, 'Mr Pepper', NULL), (5, 'Mr Fanta', NULL);[/quote]I do in fact know how to use this method of inserting. However it only works on 2008 and above and not everyone (believe it or not) is done using 2005 (and even lower).[quote]Now try your queries with a proper schema.[/quote]My queries work exactly the same. Which is why I chose to use a very simple table layout. If I had chosen to make a realistic schema I could have added quite a few more columns, tables, defaults, constraints, foreign keys etc. That not being the point of the article I chose not to.[quote][b]SQLKnowItAll (9/10/2012)[/b][hr]I suppose I don't understand the point. We know that when records do not exist in the RIGHT table that NULLs are returned. So why would we expect a filter condition on the RIGHT table to treat NULLs differently than we would otherwise?i.e. Simply change the filter condition to account for NULLs[code="sql"]WHERE Professor.HasTenure = 'True' AND (Class.ClassYear >= 2011 OR Class.ClassYear IS NULL)[/code][/quote]You can absolutly do it this way. Personally I prefer the other, and I do consider the ClassYear part of the join in this case. But that comes down to personal preference I think. I will say that you should consider yourself lucky to understand it so well. I still get around 1 or 2 questions a week on this subject. Which is why I wrote the article in the first place. It tends to be a difficult subject for some people. Not everyone, but in my experience the vast majority.

RE: Fun with Outer Joins

CapnHector — Mon, 10 Sep 2012 08:07:32 GMT

[quote][b]SQLKnowItAll (9/10/2012)[/b][hr][quote][b]capn.hector (9/10/2012)[/b][hr][quote][b]SQLKnowItAll (9/10/2012)[/b][hr]I suppose I don't understand the point. We know that when records do not exist in the RIGHT table that NULLs are returned. So why would we expect a filter condition on the RIGHT table to treat NULLs differently than we would otherwise?i.e. Simply change the filter condition to account for NULLs[code="sql"]WHERE Professor.HasTenure = 'True' AND (Class.ClassYear >= 2011 OR Class.ClassYear IS NULL)[/code][/quote]id say because the above query is the classic catch all. if we can avoid the catch all performance should increase. (as always the it depends please test applies).if we have an index on Class with ProfessorID and ClassYear we can use that index in the join. we also eliminating records we dont need early in execution and have a lower overall row count.To Kenneth, Great article and a gave me a great way to explain join conditions vs where conditions.[/quote]I don't see it as a catch all. I see it as defining the requirements of the query. The join condition is the id, the filter is the tenure and the year. Assessing the question posed, I wouldn't expect to see the results that the author posed; i.e. if class year is NULL, then they didn't teach that year and I don't need them in the results. I just don't understand why the expected results would be different. If someone has been working with LEFT OUTER JOINs for more than 20 minutes... why would they ever expect the query to return rows from the LEFT table when the filter is on the RIGHT? I don't put the filter in the JOIN because it is a filter, not a join condition.[/quote]while i agree with you that the join condition is the id and the filter is tenure and year, i disagree that the join on id should be the only thing in the join conditions. as far as the expected results, well users want what they want and we are there to provide.(Class.ClassYear >= 2011 OR Class.ClassYear IS NULL) while not exactly a catch all with a passed variable you are still using the form (something = constant or something IS NULL). if we can eliminate the second check for null (even by not putting a "filter" in the where clause but in the join) we should speed up the query.this of course is a great situation for the it depends, test both ways, or what does your code reviewer say answer to which is correct.

RE: Fun with Outer Joins

SQLKnowItAll — Mon, 10 Sep 2012 07:48:35 GMT

[quote][b]capn.hector (9/10/2012)[/b][hr][quote][b]SQLKnowItAll (9/10/2012)[/b][hr]I suppose I don't understand the point. We know that when records do not exist in the RIGHT table that NULLs are returned. So why would we expect a filter condition on the RIGHT table to treat NULLs differently than we would otherwise?i.e. Simply change the filter condition to account for NULLs[code="sql"]WHERE Professor.HasTenure = 'True' AND (Class.ClassYear >= 2011 OR Class.ClassYear IS NULL)[/code][/quote]id say because the above query is the classic catch all. if we can avoid the catch all performance should increase. (as always the it depends please test applies).if we have an index on Class with ProfessorID and ClassYear we can use that index in the join. we also eliminating records we dont need early in execution and have a lower overall row count.To Kenneth, Great article and a gave me a great way to explain join conditions vs where conditions.[/quote]I don't see it as a catch all. I see it as defining the requirements of the query. The join condition is the id, the filter is the tenure and the year. Assessing the question posed, I wouldn't expect to see the results that the author posed; i.e. if class year is NULL, then they didn't teach that year and I don't need them in the results. I just don't understand why the expected results would be different. If someone has been working with LEFT OUTER JOINs for more than 20 minutes... why would they ever expect the query to return rows from the LEFT table when the filter is on the RIGHT? I don't put the filter in the JOIN because it is a filter, not a join condition.

RE: Fun with Outer Joins

CapnHector — Mon, 10 Sep 2012 07:40:52 GMT

[quote][b]SQLKnowItAll (9/10/2012)[/b][hr]I suppose I don't understand the point. We know that when records do not exist in the RIGHT table that NULLs are returned. So why would we expect a filter condition on the RIGHT table to treat NULLs differently than we would otherwise?i.e. Simply change the filter condition to account for NULLs[code="sql"]WHERE Professor.HasTenure = 'True' AND (Class.ClassYear >= 2011 OR Class.ClassYear IS NULL)[/code][/quote]id say because the above query is the classic catch all. if we can avoid the catch all performance should increase. (as always the it depends please test applies).if we have an index on Class with ProfessorID and ClassYear we can use that index in the join. we also eliminating records we dont need early in execution and have a lower overall row count.To Kenneth, Great article and a gave me a great way to explain join conditions vs where conditions.

RE: Fun with Outer Joins

SQLKnowItAll — Mon, 10 Sep 2012 07:23:05 GMT

I suppose I don't understand the point. We know that when records do not exist in the RIGHT table that NULLs are returned. So why would we expect a filter condition on the RIGHT table to treat NULLs differently than we would otherwise?i.e. Simply change the filter condition to account for NULLs[code="sql"]WHERE Professor.HasTenure = 'True' AND (Class.ClassYear >= 2011 OR Class.ClassYear IS NULL)[/code]

RE: Fun with Outer Joins

WolfgangE — Mon, 10 Sep 2012 02:52:24 GMT

hi there,maybe this little trick helps:I personally understood this behaviour of outer joins quite a time ago when it was explained to me as follows (yes I know, very simplified...):there is a logical order of processing the a query. At some time the outer join with its ON-clause is processed, first as an inner join, then adding the outer rows and also the NULL-values on the missing inner rows. Some time after that the WHERE-clause is processed maybe "removing" null-values.Since I'm saying to myself "first the ON is processed, after that the WHERE is processed" it's no problem for me any more writing my outer joins in a way they are doing what I expect.

RE: Fun with Outer Joins

dioscoredes — Mon, 10 Sep 2012 02:44:21 GMT

Thanks for the article. In my experience (15 years SQL Server, 25 Oracle) there have been so many instances where despite hours of research and hacking the T-SQL, an outer join fails to deliver the keys for non matched rows. This appears to be a not uncommon finding. Maybe there's scope for a follow on article explaining with why this happens and how to fix it without resorting to #tables.

RE: Fun with Outer Joins

CELKO — Sun, 09 Sep 2012 23:14:54 GMT

The correct terms are "preserved table " and "unpreserved table" and your design is wrong. You have no keys, we do not use BIT flags in SQL, you believe in a magical "id" that changes from table to table, becoming a professor, a squid, a class, an automobile, etc. That is Kabahlah magice and nto RDBMS. You had only one professor, no key, a name that violates USPS standards and an assembly language bit flag. IDENTITY properties? And no DRI because of the bad non-schema. Yes, without keys and DRI, this is not RDBMS. You also do not understand temporal data. CREATE TABLE Professors(emp_id INTEGER NOT NULL PRIMARY KEY, professor_name VARCHAR(35) NOT NULL, tenure_date DATE); Your life will be easier if you learn to use row constructors: INSET INTO ProfessorsVALUES (1, 'Dr Coke', '2011-01-01'), (2, 'Dr Sprite', '2011-01-01'), (3, 'Dr 7-up', '2011-01-01'), (4, 'Mr Pepper', NULL), (5, 'Mr Fanta', NULL);Notice the use of a NULL for non-tenured professors. This still stinks and we ought to have a status and date range. But let's skip that design flaw for now. CREATE TABLE Classes(course_nbr INTEGER NOT NULL REFERENCES Catalog, semester_name CHAR(7) NOT NULL REFERENCES Semesters, PRIMARY KEY (course_nbr, semester_name) emp_id INTEGER REFERENCES Professors);The common temporal idiom in SQL is a look up table for reporting periods. I prefer a name that can be sorted with ISO-8601 data:CREATE TABLE Semesters(semester_name CHAR(7) NOT NULL PRIMARY KEY CHECK (semester_name LIKEB '[12][0-9][0-9][0-9]-S[1-3]'), semester_start_date DATE NOT NULL, semester_end_date DATE NOT NULL, CHECK(semester_start_date < semester_end_date); You can get the class name from the catalog and not waste space and time on it. What you had was not normalized. Courses have numbers; look at a college catalog. The NULL is my favorite instructor “TBD” (I do adjunct teaching and that means work).INSERT INTO Class_Schedule (class_nbr, semester_name, emp_id)VALUES (101 '2010-S1', 4), (101, '2010-S2', 4), (101, '2011-S1', 4), (101, '2012-S1', NULL), (101, '2012-S2', NULL), Etc. Now try your queries with a proper schema.

Fun with Outer Joins

Kenneth.Fisher — Sun, 09 Sep 2012 17:47:12 GMT

Comments posted to this topic are about the item [B]Fun with Outer Joins[/B]