Nulls Make Things Easier?
CC Attribution - NonCommercial - ShareAlike 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this
Momjian, Bruce
Nulls are a very useful but also very error-prone relational database feature. This talk is designed to help applications developers better manage their use of Nulls. It covers the use of NULLs in relational databases, with a focus on Postgres behaviour. It covers three-value logic, comparing nulls, mapping nulls to strings, indexing nulls, and aggregates.
 Thank you very much for coming. My name is Bruce Momjian. I'm one of the PostgreSQL team members. I work for Enterprise DB. Many of you who have been here before already know many of these things, but I have to say them anyway for those who were not here. This presentation along with about 30 others is at that website right here. In fact, this website I enabled on my website about an hour ago. So if you want to follow along, you are very well able to download this PDF and follow along as I speak. Fortunately, there will be a recording of this, so I'm excited that once the recording comes up, hopefully I'll get an email or somebody will tell me about it. And then I can relink to it from my web page. You'll see some not only do I have 30 presentations on there, but also many of them have recordings next to them. So there's a little tab, and you can open it up, and you can see the recordings. Where I've spoken, where I've given each talk. That way I don't repeat stuff, which is not good for me, worthy audience. This presentation is fairly unusual. What this presentation actually is, is inspired by a series of blog posts that I put out about three months ago relating to the handling of nulls. Have any of you read any of those blog posts? Great. OK. So for those of you who have read the blog post, there's going to be some additional analysis of some of the issues, and I think some of the discussion will be interesting. But I will admit that much of the content is directly from that blog post. What's really interesting about doing it the way I did it was, A, it was a series of 11 blog posts that spanned probably three weeks. And what was really neat was I got feedback as I was doing it, because of course I used discuss. So people can comment on my blogs. You might have noticed there was a keynote from Discuss this morning. That's not why I chose it. Actually, it was just the best solution. And effectively, what was neat was I was able to kind of improve the blog and improve the content as I got comments from the people who were basically commenting on the blog post. So there's sort of a combination. Has anyone here actually comment on the blogs that made up this series? Because then you would be sort of an author above them. Anyway, I'm sure there's somebody at PGCon who did. So let's talk about this particular blog post, this particular presentation. The reason I was inspired to do it is because being somebody who's on the PostgreSQL mailing list all the time, there is a continual kind of difficulty in working with nulls. In fact, that inspired the title, which is a question, nulls make things easier. Was this supposed to help me? You're trying to help me with this? A lot of people are very frustrated about the way nulls behave. If they find it very confusing, I will stay up front. It is confusing. And as we get to the end, I think we'll start to see sort of a pattern of what's emerging. But the bottom line is that the pattern that's emerged is basically, eh, I kind of get it. But there is no overarching single system that you can sort of place upon nulls and say, here's the one fact I need to remember about nulls and everything else will fall into place. Doesn't work that way. I'm even going to explain why it doesn't work that way. But let's get started to get going. Any questions before we get started? Again, I do take questions from the audience or those of you who are not in my tutorial. So I'm excited. Let's get going. What's null? Now, we have a null in English. Null means nothing. It basically just means void. There is sort of a confusion of how to represent that. Is it a whole? Is it nothing? Is it a blank slide? There's sort of how do you describe nothing with something because you have to use something to describe nothing. And in English, people don't get so confused. There is the concept of nulls in various procedural languages. SQL is not a procedural language. It's a declarative language. But in procedural languages, you do have the concept of null, particularly in C-based languages, Java being one of the better known ones. And it's, of course, C++ and other sort of C-derived languages. You have the concept of null when you're assigning a value to a pointer that doesn't point to anything. And that's pretty easy to understand. You've got a pointer. It's not a data value. It's a pointer. And you're pointing to something that doesn't exist. That's pretty easy to describe. It's pretty easy to have a little diagram. You have a box, a little arrow. There's an x on the arrow. It doesn't go anywhere. That's pretty easy to get. Where it becomes confusing, frankly, is in data. Because unlike a pointer where you know the value either points to a memory location or points to nothing, in data, you aren't dealing with pointers. You're dealing with a container. You're dealing with a container that normally holds a value. You don't have pointers in SQL. So you have a container. It normally contains a value. And now we have the challenge of representing no value or nothing. And that's probably the first thing that is the bender. Again, when you're dealing with pointers, it's kind of easy to say it points to something and points to nothing. That's pretty easy to understand. When you have a container, and you have to represent that container as containing nothing in the container, how do you do that? Well, in fact, we do that with nulls. And the way Postgres stores nulls is internally, in fact, is very similar to the way you'd expect. We don't actually store. For example, if you have an integer, and that integer field is null, integer is normally 4 bytes in Postgres, or 8 bytes if you do a big int. But if the value is null, we don't store 4 bytes. We don't store 8 bytes. We have a separate area in the row which identifies which of the columns is null, which of the fields is null. So if you have three integer columns in a table, and you're looking at a specific row, and the middle one is null, we store the value for field 1, column 1, in the row. We store the value for column 3 in the row. And then we have a bitmap over here, which has, by the way, slot number 2, we're going to set that bit high. And that bit indicates that that second field, that second column for that row, doesn't have a value in it. So even in Postgres, the idea of representing nothing, internally, we have to create a separate bitmap to handle it. There's some performance reasons for that. Of course, if you have a row that's mostly nulls, which does happen, we actually talked about this a little bit. On Tuesday, when we had the tutorial, effectively, you're now looking at a null bitmap with 1,000 bits. You're not looking at 1,000 empty rows or whatever. But why do we need this thing? Why do we need this thing in SQL that we call nulls? Well, effectively, as much as people belly ache about it, it's harder if you don't have it than if you haven't. So as much confusion as I'm going to show you in this future slides, where you look at it and you're saying, why do they do that? Why does it do it that way? Why am I having to learn about nulls? How many of you have been sort of surprised by null behavior in the past? Yeah. I've got a lot of hands there. So the question is, why are we surprised? Why can't we make it so we're not surprised? The bottom line is we need to represent this. Don't have a value. There's nothing in the data. Why? Because if you don't have it, it becomes a problem to represent your data. Strings, usually, if you don't have a data value, you can put single quotes or zero-length string. And that's like, I don't have a value there. It's not the same as null, but we could probably get away with it and never have invented nulls in the first place. Maybe. I'm not sure we could have. But if everything was a string, we might have gotten away in SQL without nulls. But where it becomes a really problem is all the other fields. Everything's not a string. Numerics, what do you put in a field that you don't want to value in? I'm sure many of you work with databases that didn't have nulls, and you start to get into the special value. Minus 100, minus 99, minus 1, zero. And it's a different special value in every field. And then we have to encode those special values in your application. And you have to remember that the special value for this field is this, and the special value for this field is that. And obviously, that doesn't work well. Not only is it a problem with your application, but the database doesn't know your special value. It doesn't know that a minus 99 over here and a minus 99 over there aren't the same, because hey, they're both minus 99. But there's actually some behavioral changes that we need to represent in SQL when we're specifying something we don't know. And again, you're going to see that propagate into the future slides. Dates, same problem. If you don't know the date or you don't want to date in a field, if you don't have null, what do you put there? 1900, January 1st, 1970, all these crazy things that you have to do if you don't have nulls. And that's why we have to bite the bullet, and that's why we had to get the nulls. And what I'm going to do for the rest of the presentation is basically explain how nulls get into your database, how they behave in various situations, and then give you an understanding so you don't get surprised. So you can walk away and say, you know, I wish we didn't have need nulls. We do need nulls. Here's how they behave in these situations. I have to learn that as an application program. This is a good question. Time forget. Any questions anybody has about the groundwork I said and sort of the concepts that I covered? OK, great. So let's look at why we would use nulls, like abstractly. What possible purposes would I have for a null? Probably the most popular one is the unknown values. So you're inserting a row. You don't have information about all the fields you want, but you want to get part of the data in. This is very common in data entry. You know the person's first and last name. You don't know their birth date. And you're going to come back later and fill it in. Well, we don't want to put 0101900 in there, because then they'd be 113 years old. Or some magical value that we then have to kind of scrape through our system looking for. The logical thing to use for an unknown value is a null. The second one is inapplicable values. These are values that aren't unknown but are effectively don't apply to this row. It's not like I don't know the value. It's like I'm never going to fill that value in, because the value doesn't exist. And I'm going to show you an example in the next slide. The third one is empty placeholders. And these are cases where you're doing some SQL. You've asked for a row or part of a row. And there's some values we just didn't supply. And what do you want us to put there? Minus 1, 0, 1900? No, we're going to put a null there. And I would argue that some have said that there, and this is kind of going to go berserk. But some have said that there should be three types of null in SQL. Has anybody heard that before? Kevin? It kind of makes it that there should be separate flags around there. Yeah. So this is a very interesting idea. And the reason it gets a little wonky is because the behavior that you might expect for a value that's unknown might be different than a value you expect in SQL if you were to go for the logical way to handle it, would be different for an inexplicable value. I know I'm getting way out of the space for some people. But you kind of see that when you're overloading multiple purposes on a single value, then often by definition, we can't generate a predictable behavior for this type of value. I'm going to show you a lot of very unpredictable cases in the future slides. So here's an example that I think takes it home for me. And I don't know where I heard this example before, but it's an example I've always used. I've never seen where this came from. Did I think about it? I can't even remember. But it's the idea that you've got an employee table and you've got a spouse field. I don't know why you track the spouse name maybe for invitations to the Christmas, whatever. You've got to call them up to say the spouse is died. I don't know why you'd have it there. But let's suppose you have a spouse field. So those of you who are in my previous tutorial are finding that very funny. And you should. Because there is no hook on this to hold it on. So merely brushing by it is enough to send it launching to the floor. So employee.spouse. So here's the three examples of how we might use a null in this particular field. The first one is we don't know the spouse's name. The employee's just hired. We didn't ask him the name. They didn't fill it out on the form. Who knows? We couldn't read the form. We couldn't read the name on the form. That's a great example. They filled it in so we know they have a spouse, but we can't read it. We're not going to wait for them to call them up. We just want to get the data in there, and we'll come back later. We'll put the spouse in. We would put a null in the spouse field for that particular row, means we don't know the spouse. Separate case would be the employee is not married, and therefore has no spouse. You can see there's different concepts. We know we're not coming back, at least not in the next week to put their spouse in, because they're not going to get married in the next week. The third case, and this is the empty placeholders, we do an outer join on the employee table, and we don't have a row there. I'm sure many of you have dealt with outer joins, but effectively in an outer join, when there's a column that doesn't match, and you've got to put something there, you put a null there. So there's three uses of nulls, really distinct uses of nulls, that we really are representing three different cases of we don't want a value in a field. Any questions? So let's get into the meat of it. First thing, don't use nulls if you don't have to. I know there's some people who have kind of gone over the deep end with nulls. Usually if you're using nulls too much, you start to get a lot of the unpredictable behavior that we talk about here. I even mentioned this two days ago when we talked about the not null constraint on a column. Remember that? I said, if you don't want a null, don't allow a null to the column. And frankly, we had a discussion of what percentage of columns probably could be not null, but maybe 80% of your columns probably should be not null, meaning just don't let a null get in there. Customer name? If you don't know the customer name, you probably shouldn't be entering it into the system. We can get away without an employee spouse field just fine. But if you don't know the name of the employee, don't put them in. And you start to get into some constraints of preventing the nulls. So example I have here is actually if you've ever used pliers to take off a nut, surprisingly there's virtually no picture of this nut grinding pliers problem on Google. I would love somebody to email to me, but that's the only one I could find. So the point is that nulls are to be used only in special circumstances. They're not really to be used when you can avoid using them and because of some of the behaviors that we talked about. Warning. This is actually from CJDate. Again, Kevin Grittner, who gave a talk previously, actually talked about Cod and how he thought there should be three nulls. Here's the other father of SQL. And he's basically coming out and saying don't use nulls. Although it's a little nuance there. But he's being really honest. Is this right his opinion that nulls, at least, is currently defined in the meta-sql are far more trouble than there were and should be avoided? They display very strange and inconsistent behavior. It can be a rich source of error confusion. So if you've ever been confused by it, it's not you. It's nulls. It's not your fault that you're confused. And he's basically saying I will urge you not to use them, but I will seem contradictory because I will talk it. I think of nulls as a drug. If you use properly, it works. But abuse it, it can ruin everything. Best policies, avoid nulls and when you can and use them properly when you have to. That's actually Joe Selco, who actually spoke at Prague and at Austin Postgres events. It's a very well-known SQL guy. SQL Super Smarties is the book that he's talked about. He's basically very honest that this is really weird. It's not a great system. We have to live with it for the reason of the pliers thing. But it's the reason we're talking about today. So I'm going to have a lot of queries in this text. What I've done is highlight the part you should look at in red. So everyone seems to remember this picture just fine. So when I go to this picture, everyone's like, ah, I see where I'm looking. So that's what you should be looking for, the red. So effectively, let's walk through some queries here and let's look at exactly what type of behaviors I'm talking about and see if we can at least get a mental framework of how bit nulls behave in various circumstances. Any questions? So first thing you can do is you can select a literal null value. Just select null. Works just fine. As you can see, nothing shows up there, which is maybe not good. What you can do in PSQL is you can ask PSQL to show you null with a certain string. And I've done this throughout the rest of the presentation because, frankly, showing you nothing really doesn't help you. It's confusing. Like, we don't have enough confusion. We've got to add that. So effectively here, if I do select null, I get a null string there. It tells me that's a null. It's not six characters. It's really a null value. So if you see that null in parentheses, that's what you're looking at. OK, so first example. We create a table called null test. We insert a literal null into the field, again, assuming we don't know the value or the value doesn't apply. And then we do a select from the table. We literally see that null appearing. Nothing surprising there. We put a null in. We got a null out. Second case, here I'm inserting into the same table. And here's the first case where we implicitly add a null. You don't see any null up here, do you? No, there's no null there. But when you look at the output, you can see row two also has a null just like row one. So here I explicitly add a null. There's a null right there in red. This one doesn't have a null. How did I do it? I actually supplied the columns in the table I wanted to insert. So the table has columns x and y, super creative of me. I've told the system I only want to insert into column x. What's the system going to use for column y? No, and that's exactly what it does. So here we've actually implicitly, and Mr. Phil, I don't think it's here. Robert, is he coming? No, OK. So anyway, Phil had the idea of showing this. I think it was a comment that he had suggested in the thing. No, actually, that might be, take that back. That is a mistake. It shouldn't be there. Sorry about that. I will fix it. That's actually from previous presentation, and I guess I didn't delete that part. You have to null it. Have to null? Exactly, exactly. So let's get, I talked to you about the ability to specify not null in your fields. This is exactly what I'm doing here. I'm saying that my new table cannot have a null in the first field and cannot have a null in the second field, and I've now blocked out nulls from getting inserted. I've prevented from explicitly getting at it, and I've prevented from implicitly getting at it. Is everybody with me on this? So by blocking nulls at the two columns, explicitly, implicitly, value doesn't get in. Let's start to look at what happens when you have a null. So we've talked about getting nulls into the system implicitly, explicitly. Let's talk about what happens. How do you manipulate a null? What happens if you try and add one to a null? So here I'm adding one to a null. The result is null. So it isn't one. It isn't zero. It's null, because an unknown plus one is still an unknown. And this is maybe confusing, but that's kind of makes sense. And you have to sort of prepare yourself for that. I'm going to show you some other cases a little later on where it starts to really get crazy. This kind of makes sense. But if you start to extrapolate this into some other cases, it starts to get crazy. What if I concatenate a null with an A? I don't get an A. It doesn't act as a zero-length string. It's a null. And the same thing if I concatenate the other way, it's also a null. So either way, it's going to be null. And this kind of makes sense. If you start to create a sentence and you're concatenating stuff together, one of the fields is null. All of a sudden, the whole thing becomes null. Very surprising sometimes. Here is a little different case, kind of setting things up here. So I'm creating a table called ink test, which is an increment test. I insert 30, 40, and null into it. So I'm kind of prepping my table. Then I add one to it. So 30 becomes 31, 40 becomes 41, and then null is still null. Follows what we talked about here. We're good. Let's talk a little bit about comparisons. Now this is operations. This is concatenate increment. Yes, you have a question? Yeah. I am getting there. And it will be crazy. Yes, it's really not pretty, but I have to do it. So if I start to look at comparisons on nulls, we start to get some interesting behavior as well. So if I say, is null equal to 1? Well, that's easy. That's null. And notice it's not false. It's null. And that's where you start to get into another sort of twisty passage of nulls, is that when you normally think of comparisons, you think of Boolean logic, Boolean being true or false. When you're starting to look at nulls, you have, when you deal with SQL, every comparison is actually a three-valued logic. It can be true. It can be false. It can be unknown or null. And there is some very unusual behavior that you start to see in later queries where that unknown doesn't even behave like false. You're like, wow. So be prepared. This is common. Is it equal to 1? No. Is it equal to a zero-length string? No. It might display as a zero-length string to the user, but it's not the same as a zero-length string. Does null equal null? Now here's where you start to get the twisty little passages all alike. So does null equal null? Actually, we don't know. Because the null, one null could have come from some place. The other null could have come from some way itself. It could be two rows that are both null or equal. We don't know. That's kind of the logic that they're having here. You can select if null is null. I'm going to get to that. Yeah. So this is comparing a literal value of null to some other literal value of null. This kind of really crazy. Again, as I said, there's some extrapolation that starts to go in. When I said null plus 1 is null, I said we're going to see a case where you start to extrapolate that out. It starts to look kind of weird. Is null less than null plus 1? Well, I went to math class. Null less than null plus 1 is probably true. Unless the two nulls are different and came from different places, at which point I don't know. And that's basically the tactic that the SQL standard committee says is that those two nulls could have come from different places, be in different rows. They aren't necessarily the same null. Because again, if you think of adding rows, you have a column that represents weight. So it's like, I'm 1 pound. I'm 5 pounds. I'm 10 pounds. I'm 20 pounds. Different parts. What if you have two rows that have null weights? Well, that means the first row might be 1,000 pounds and the second row might be 2,000 pounds. You don't know. So the concept here is that because I've got two nulls, I can assume that they're the same value as I would if the two weights were 10 pounds and 20 pounds. I know what those values are. So even something that logically you would think would be true comes out as null or unknown. So query comparisons, here's where it starts to get interesting. Select one where true. That's going to return a row. Select one where false. That's not going to return anything. That's not a null row. That's nothing. Select one where null returns nothing. And in fact, somebody argued, and I think this might have been a comment. I can't remember. Somebody said the where clause should be called the sure clause. Like, are you sure it matches? Because effectively, where only returns a result on true, not on false, and not on null. And here's where you get that three-valued logic thing going on. OK. So let's take a look at the and and or kind of case. True and null is null, because we don't know if the null is true or not. That kind of makes sense. Select not null. The inverse, the opposite of null, is of course null. Again, a little surprising there. I guess we don't know if it's true or false, so we don't know if it's true or false. Noted, and therefore it's unknown. No operator comparisons. Again, I'm kind of coming back here to look at some of the row comparisons. So we're really combining in my mind, we're combining these tests, the operator tests that we went through, and the where clause test that we went through. And I'm going to go ahead and do a little bit of a comparison that we went through, and I'm going to show you combined. So now I'm back to my ink test table, 30, 40, and null. Very easy to understand, right? Three rows. I want all the rows where x is greater than 0. 30, 40. Good, makes sense. I want all the rows that are less than 0. Nothing. There are no rows here. Nothing's, we don't know. Because null is, we don't know. I want all the rows that are less than 0 or greater than equal to 0. Now that's a real tough one not to say an null and null should match. But it doesn't. Because again, this returns a null, this returns a null. The result is a null. It goes into the where clause. The where clause says, I only return on true. And you get nothing. Yes, sir? So this is true for the, for n null and null, it's null and null at the same time. Yeah. Yeah, it's kind of like 0 times 0 times. I almost think of it as multiplication with a 0. Like as soon as you multiply, you can have a billion. And since you multiply by 0, it's 0, right? So when you start to chain these things together, any of these results that return null, it kind of just, it pollutes the whole expression. And the expression of how long it is or how much we try to kind of narrow it down. And it just polluted the whole thing. And one of the criticisms of nulls is that effect, that a null has a tendency to pollute itself way beyond the column it's in. And just starts to spill out all over the place. And has a very negative or very surprising consequence in areas you might not have suspected. Another case, give me all the values that aren't 10. That's easy. Give me all the values that are 10 or 10. Aren't 10 or 10, right? So either you're 10 or you're not 10. No, my null is not there. Again, that sort of pollution effect across expressions. Where it starts to get a little confusing is the not in clause. And I'm going to kind of show you an example of expressed doubt. So if I'm doing a comparison, I'm saying one isn't two and one isn't three. Well, that's true, because one isn't two and one isn't three. If I do a separate comparison, one isn't two, one isn't three, and one isn't null, then I get a null. Because I don't know if that null might be a one. I don't know. So if we convert that, so I say select A in null, what I would think of is a null, because effectively, we don't know. But if I say A is not in a row that's just a null, again, I get a null. So a lot of times you have to take away is that a lot of times people say, well, I'm not going to get null. A lot of times people say, well, my in clause, I understand nulls are going to prevent that from matching, but my not in clause, well, that shouldn't have a problem. They think of it as the negative. So I'm going to get all the rows that are in a certain set and all the rows that are not in a certain set. And the two results together should be the full set. In my mind, that's what I'm thinking. I got everything that's in this group. I get everything that's in A. I have everything that's not in A. Combine those two, I should get the original group. And what I'm saying is with nulls, that is no longer true, because nulls mean that I might have some things that are in the group. I don't know it. Null, not in the group. I don't know it. Null. And they don't show up. And a lot of times you spend time debugging queries trying to figure out why I'm missing rows. And a lot of times the null is the culprit. A little more expanded example. So is A in A or null? Now this one actually works, because we're saying, is A in a result that's in A or null? What's in the A? So we're done. We don't have to look over here, because we know this matches. And we go like, OK, true. Good. We're go. Not in is where it bites everybody. And I'm not going to get a list of hands that's bitten. But not in always is a thing that gets people with nulls. So is A not in A or not in null? In fact, this one works, because you know that A is in A. So you can return false. You're good there. Is A in B or null? I don't know. Here's the one that kills everybody. Is A in B or a null? I don't know. And a lot of times people do sets, and they'll say, give me everything that isn't, give me everything that's less than 100 or whatever. And then all of a sudden, there's all these rows that don't show up, and they can't figure out why. It's because they've got nulls in there. In fact, it pollutes the whole system. So I could have 20 bees over here. I could have thousands of rows. As soon as I get this thing, the not in just says, I don't know. It doesn't return to anything. OK, so it really can get you. Really can get you. Let's expand out the query. Is A equal to B or is A equal to null? This is the bottom two queries expanded. Is A in B or is A in null? I don't know. Is A not in B and is A not in null? Again, I don't know. This is where you start to see the problem. Questions? Great. Explicit null compare since somebody already talked about this, I showed an example up here, null equals null. Remember that? That's like, is null equal to null? It returns, I don't know. If you need to test for a null, you probably are explicitly going to need to use a special clause. Not equal null, but is null. Is null, is null? Yes. This is where I'm mapping now my null to into the Boolean world. So I get my null out of the three-valued logic world, and is null basically pulls it into the Boolean world when I know my result's going to be true or false here. Same thing with not in, is null not in? Select null is not null, and it's false. Yes, sir? The keyword is null. The keyword is null. So the is is not something you can use on its own. No, you don't. It's always is null. Good question. And again, this pulls it out. So for example, when we're trying to do something like not in, a lot of times what you will end up doing is burying an is not null test in your subquery to prevent this type of comparison. So what they'll do is they'll select null, and they'll say where column is not null. And they'll prevent the nulls from propagating up into this result set, and therefore the not in will properly evaluate by extracting out. You know what I'm saying? So for example, if you want all your customers who are not in Pennsylvania, you would say maybe you want to select all your customers here. I don't know for whatever reason. If you have some customers that are null, you're not going to get any rows back. Because it's like, well, customers not in, and list of values is null, so therefore it's not going to match. So a lot of times what people do is they'll say is not null, and they'll prevent the nulls from getting up into the target list and being seen by the is not null. I know it sounds like I'm gibbering, but you know what I'm saying? You prevent the nulls from getting up to the target list, and therefore your is null, your in or your not in now behaves logically that you might not have been able to do before. OK? Could you say that the one back on start? Yeah. Because it's not a reasonable approach to benefit the performance of the life story, not for the value to the comparison. I don't think performance really ever enters into the use of is null and is not null. It's more of a desire to exclude nulls from polluting out into the rest of your comparisons. That's really where I think the win is. Because as I showed you with concatenation and all these other things and not in and in and add and so forth, a lot of times the way that null propagates it's this sort of polluting thing that sort of wants to spread uncertainty through the whole expression or even up into the upper query. And a lot of people who know I've got some data that I don't want to process. OK? They'll just say they'll just exclude anything that has a null in the field they're working with. And that really is the filter that prevents it from going out and up and causing sort of unpredictable behavior. Yes, sir? There is a problem with the optimization of is not in sub-query. The references of data goes unoptimized. So, for example, not exist. The semantics are different. But not in some cases optimizes very poorly. OK. But it's not unique to nulls. It's a behavior of the. Is there a number of elements? OK. Right. So if we go down to this test, it starts to make a little more sense. We're kind of bringing things together here. So we have our three rows in ink test. We can say select star where x is null. And I'm now pulling out just the null rows. And now select intents where x is not null and I'm pulling out the non-null rows. So as I said before, you think of your result set as here's all the ones that are in. Here's all the ones that are not in. Combined, that should be my full set. You should probably have a third version which says here's all my ones that are in. Here's my ones that are not in. And here's all my unknowns. And you now have to think of having a third set of values which combined with the first two will give you your original set. That's when I, a lot of times I remember when it was an application program, I would say give me all the positives and then give me all the non-negatives. I mean, I don't know what it is. Give me all the negatives and then give me all the non-negatives, right, zero and positive. And I would think that would be my whole set. And I'd have two queries. I would say all the negatives, I'm going to do this with it. All the non-negatives, zero and positive, I'm going to do this with it. And I wouldn't process all my rows. Like really? It's like, I don't have null in that column, so therefore I've got nulls in that field. And now all of a sudden I have to be, I need the third block which is going to say give me all of the null rows, values for that field and do something special with those. And that's why I think a lot of the concern you saw earlier in the quotes, people saying, ah, don't use them, right? I mean, be careful when you use them because now all of a sudden your logic has to be, you have to now think when you're doing these type of queries, how is a null going to behave in all these situations? A lot of cases you don't care. You give me everyone in Pennsylvania. You don't care if they're not in Pennsylvania, they're just not in Pennsylvania. In most cases you don't care. It's usually when you're trying to split up your result set into two parts. Like give me all the Pennsylvania people, give me everyone who isn't in Pennsylvania. That's when you usually get caught. Because there is that set that isn't in Pennsylvania or not in Pennsylvania. What about the big, empty example of that? That's been one of my favorite things in the official of the defense. I think the big difference is null. I don't want to say a big difference. Yeah. Null is about the only choice I've seen with that. In other words, when you get to use 1, 1, h, and 100, I'm funky. How do you deal with that? Recasting that field is strange. Making it something other than null. Glitch, you asked. I'm actually about to get to that. I actually have some commands specifically for that case. I think I have one more slide, two more slides. So another, remember I told you about null is null and null. There's another clause that we have, which is a little different called distinct. Distinct's a little hard to get your hand around. But it allows you to test for whether a value is either different from or null, and it returns a truth. For example, I'm saying is null distinct from 1? Millenia, what it means is is it 2 or is it just a null? And both of those in distinct land are different from the 1. I'm sorry, I think I've distorted it by using a 1 here and a 2 here. So my apologies, I shouldn't have done that. Effectively here I'm saying is 1 different than 2? Yes, it is. Is null different from 1? Yes, it is. Is null different from null? No, it's not. So what this allows me to do is instead of doing an equality comparison with a value, null equals 1, which obviously returns null, if I use is distinct from, it's effectively saying, are you not equal to that value? And if you're null, you're still not equal. I realize I don't like the fact this is a negative. So distinct from is effectively a not equals. And in fact, what's really crazy is there is a not distinct from which is effectively equals. So I don't like that. I don't know if I could have thought of a better solution. But effectively it allows us to do comparisons between nulls and potentially non-nulls, where we say a not equal or an equal with a null returns false. So if you need to do quality, unequality comparisons, not is null, is null is testing if it's a null. That's pretty easy. But if you need to do a comparison between a column and something else, distinct and not distinct are actually pretty nice to use, although the negativity really throws me for a loop. Is that the answer to the question? Yes, it is. Yeah, we kind of got stuck there. Here's an example. So I've got two columns, 1, 1, 2, 3, and null and null. And I say is x, which is the first column, distinct from y. Now, 1 is not distinct from null. Yeah, 1 is not distinct from 1, so we return it. 2 is distinct from 3, so we don't return it. Is null not distinct from null? I know it's like true. And I know I just can't get ahead around a lot of times. But effectively, it allows us to, again, map that null behavior into a Boolean behavior, which is only going to be true or false. So think of it as mapping what would normally return a null for equality comparisons into a true or false that you can then work with. Particularly, this is useful for joins. Because in your application, say, oh, it's null, do this or not equal. But if you're joining two columns and some of them might be null, this distinct form can be very helpful. So the dense operator is only for nulls? Is it reason for that? If you look at the grammar, I don't even think we have an is operator. It's like it actually has to be those keywords in that order. Yeah, where there is is optional, not distinct, is optional, not null. It's really, there is no is alone without either null. I guess you could say it's for null behavior checking. Makes sense. Yeah, I can see that. Yeah, you can say is true. Yeah, yeah, yeah, yeah, yeah, yeah, yeah. So you're right. You could do integer, integer, or the same as null. Ooh. So maybe it's only for Boolean? Maybe? Because you're right, both of these return Booleans is null. And you can say false is true, and that'll work. So you're right, is, I guess, is an operator. Never thought of that. It's its own little thing. But you're right, integer, is integer doesn't work. Let's take a look at ordering. Ordering is a case where I think we basically sort of discard a lot of the rules that we talked about already, and we make up new ones. So here we have a table that has a null, a two, a one, and a null. It looks just like this. What you're noticing is that these nulls are now all the way at the end. And when we talked about nulls equaling nulls, they don't. They always return null. But when you order them, all of a sudden, they're equal to each other. Magic. And the reason that they do that is because they really couldn't do anything else. Sort of randomly sprinkling the nulls in the ordering really wasn't going to do anyone any good. So effectively, you just had to fall back and say, well, what's the practical behavior that would make the most sense? We'll just put the nulls together, even though they don't equal each other. And we've hounded that to people and beaten it into their heads all this time. I think technically what they would say is that you get the unknowns at the end. So the unknowns just seem to come at the end. Same thing. We can actually specify the nulls to go first. And we can control and say we want the nulls to come first. Similar to indexes, we throw the baby out a little bit. Or maybe not. I don't know. We're sort of heading into crazy land here. So if I create a unique index, I can actually put multiple nulls in that unique index just fine. Because they don't equal each other, so therefore, they're unique, I think. Sierra can obviously put nulls in there just fine. And I think there is a, is it oracle that only rounds one null in a unique field? There is some. Yeah. There's some other databases that don't. I don't remember if the standard requires us to do this, or we break the standard, or I don't know. We just thought it made the most sense to allow multiple nulls in a field, in a unique field. Because yeah, the idea that one null would equal another null doesn't make any sense. So they are kind of unique, if you look at it sideways. Aggregates, I promise to bend your brain on this one. Again, you sort of get a practical behavior that may not match what you saw before. Here we have a table. I'm putting in 7, 8, and null. Drag test, very easy to understand. I'm going to just run a bunch of aggregates on this. Count, star, count, and a field name. Some, min, max, and average. When you do count star, it counts rows, irregardless of the values in the row. So you get three, because there's three rows there. When you specify a field in the count parentheses, then we only count non-null values. How convenient. So in fact, we have two here, because there are only two non-null values up there. Not very obvious that that's what it would do, but you just have to burn it into your brain. Count star and count on the column actually have these different behaviors. Some, that's pretty clear. Well, 15, well, not really. Because 7 plus 8 plus null is null. We talked about that in the earlier slides. But I would get to throw that out, just discard that. I would argue that just as count doesn't look at nulls in the field, sum, and min, and max, and indeed, average, don't look at nulls in the field. And you just have to learn that. So the sum is 15. It's the sum of the non-null values. The min and the max make perfect sense. The average, again, you're only looking at two rows. So the average, it's sort of 7.5. Odds are with the null in there. It really isn't 7.5. It's something else. And it really is null, in my mind. But I guess returning null for that field just wouldn't make a lot of sense. There'd be a lot of cases where you had to say, is not null all over the place for aggregate. So they just throw them away. I'm sorry? You're also dividing by null. Yeah, I'm dividing. Thank you. I'm even dividing by null. It just takes me to a whole new level. What's also interesting, if I go delete the table and I just run the same query on empty table, I start to get reasonable values. I get count of 0, no rows, count, both of them are 0. I get a sum not of 0, which kind of surprises me, but null. And I think they did that because the min and the max and the average problem, the min and the max particularly, would have to be null. You couldn't guess what a max of no rows would be. So they just kind of across the board said, we're just going to return all nulls if there's no rows there. Is that obvious? I don't know. And I don't think so. But that's what they did. Put some rows back in there again. Now we're going to put two null values in the table. And we're going to run the same query with a group by. And this is where we're really out in space here. Because as I said before, a null never equals a null. But group by, it's like, hey, let's go. We're all together as nulls here. So just as the aggregate kind of had this weird behavior for nulls, group by, all of a sudden, all the nulls equal to each other. Hey, there you go. So my takeaways, don't assume aggregate with nulls is going to behave just as though you typed out the sum or the, you know what I'm saying? It's just not the same. It has some sort of super behavior. And it doesn't even ignore nulls, which we could understand here we're ignoring nulls. I get that. But wait, when I do a group by, nulls are now good citizens again. And they are all equal to each other. This makes no sense at all. And I don't pretend it makes sense. And I don't think the committee even pretends it makes sense. They sort of punt and they say, well, we think this is the most reasonable behavior. Even though it's quite inconsistent, even from the previous slide. Yes, sir? AUDIENCE member asks, do you guys use nulls as consistent? Yes. Internally, they'd have to use as null to make that work and then sort of put them in their same bucket. So the rows, the 7 and the 8 is pretty easy. If I do a count star, this is really, really interesting. If I do a count star on my null rows, I get 2. But if I do a count with the x there, I get 0. Because remember, the count with the field in there ignores the nulls, even though the group by is clearly processing the nulls. And I have a sum of null, which makes sense, and a min and a max in average old null. That part I get. Mapping nulls to strings, this is what the gentleman in the green shirt asked about, and dates and things. Again, there are some cases where the nulls are just going to propagate. How do we concatenate something that has a null? This is driving me crazy. We do have two commands. One is called coalesce. And what coalesce does is basically it says, if it basically allows you to have as many parameters as you want, and will return the first non-null parameter. Crazy definition, frankly. It's like the distinct being not equal to. It's like, throw as many parameters as you want, and I'll give you the first non-null one. I don't know why anyone would want more than two, but they support it. What I'm basically doing here is I'm saying, if the first fields null return a zero, that's pretty stupid. If the first fields null return that string, I am null. Good. That's what we could use for a date. We could say, if we've got an f, a g, and a null in a table, I'm going to say, instead of returning x, I'm going to return coalesce of x and na. Not applicable. Not unknown. In this case, I know null means not applicable. Don't ask me how I know that. I'm just saying. In this case, I'm saying not applicable. Yeah? If you do a where clause, it'll use the previous table value, not the coalesce. If you do a where clause, you're going to get x. You're not going to get the coalesce. If you want the coalesce, you have to duplicate the coalesce in your where clause, and then compare that to a string. That would work. Yeah? If you name the coalesce, the problem is the way the SQL is executed, it's everything from the from down and then the target list. So the names are typically not accessible only at the end of the query. Now, there are some cases where that's not true, particularly in order by. You can, I think, reference the column names that you specify. But in the where clause and the from clause, you don't have access to those names up there. Unless Postgres did something wonky. But typically, those names are really for display only, and kind of also for group by and order by. And again, I'm not proud of that behavior, and I think it was defined that way by the standard. So we're kind of stuck. But it's, again, a very inconsistent behavior, I think. Because I tell people, well, the target list is only evaluated at the end, but then you can mention the, in Postgres, I believe you can mention the aliases in the order by. But you can't mention them in the where clause. Crazy. I'm not proud of it, but I think we did the best we could. I think that's what it was. So here I'm getting Na in my field instead of null. So this would be what we use for my date. Here's another case. Here's my exact case. I want to concatenate three fields. And this one, if there's a null there, I just want single quotes. So I would normally put the column here, the column. Instead of a null, I'd have a column. And I'd say, if that column is null, give me zero length string. Just disappear. If it was null, just disappear. So the next one actually gave me a case where I had a solution. Great. And this one is kind of a fix for the previous one. So remember I said that when you do a sum on a column, if it's null, you get null. So basically what I'm saying is, if you do a sum and the sum returns null, I want a zero there. It allows you to kind of whack around this thing so you don't get a null here. So I'm kind of combining areas and coalesce there to make it work. Mapping strings to nulls, this is the opposite. So instead of taking a null and mapping it to a string, I'm going to map a string to a null. So here I'm saying null if x is null. No, no, no. What am I saying? Return null if x is equal to that string. So now instead of taking nulls and wanting strings out, I'm testing for strings and I'm having nulls come out. So remember, nulls in, string out is coalesce. String in, nulls out is null if. I still can't remember them sometimes. But basically what I'm saying here is I'm saying if x is na, return a null. In fact, I'm doing that right here. I'm actually specifically putting in na. This is the sort of combined. So I'm taking a null, converting it to na, and then na is going to match that. It's going to return a null. So I'm doing both in and out. Just as an illustrative, confusing thing that I just like to do. Yes? Yeah? Can you repeat the last one? Yeah. In the beginning of the thing, you argued that you could be involved in the US methodology. You faced this in the real life application. And the iPad told us some. You could argue the problem was that there should never have been nulls in that column I summed in the first place. Yes, you definitely could do that. Yes, sir? Or it's an outer join which you can't avoid. That's where you start to get some interesting stuff. A little bit to wrap up. We also support row expressions. Again, there's some pretty unusual behavior here. Is a null integer array null? Yes, it is. Is an empty integer array null? No, it isn't. Is a null inside of an integer array null? It is not. Getting a little crazier. We have row expressions. Is an empty row a null? Yes. Is a row containing a null null? It is. Is a row containing two nulls a null? It is. Is a row containing a null and a non-null null? It is not. Is a row containing a null and a non-null not null? It is not. And then is one or two is not null. That's true. So effectively, the behavior is if there's only nulls in there, we can return true. We know what it is. There's only non-nulls in there. We can return true. We can test it. If there's a mix, we're going to return false. Just that's the way it did it. Distinct from this problem. Yeah, that would kind of clean it up. Another case where if you have an empty table in your select clause, that effectively return a null row. I know that's kind of weird, but it's assumed that every select clause in a target list is going to return one row. If it doesn't return any rows, we will effectively throw a null in there. And this is a surprising behavior, I think, and somebody had noticed on the email list a while back. So I think I get it. No, you don't. It's really a crazy mix of behaviors. Jeff Davis, who you will see walking around, very nice guy. He says, he had a blog post, and I pulled this out of there. He said, oh, that makes sense. When you see individual behaviors in null, they look systematic, and your brain quickly sees a pattern and extrapolates what might happen in other situations. Often, that extrapolation is wrong, because null semantics are a mix of behaviors. I think the best way to think about null is as a Frankenstein monster of several philosophies and systems stitched together by a series of special cases. And I think I have adequately illustrated that here. I wish there was a systematic way I could present it to you, but for practical reasons, and because of the way the SQL standard has defined it, it is a Frankenstein of behaviors. But hopefully, this presentation has helped you to identify that, not to feel like you've missed the boat somehow, and to realize that when you see nulls in different circumstances, you can now sort of map how you would think those would behave. And with that, there's nothing left to say. So thank you very much. The actual series of blog posts is right at this URL. So if you want to share this pain with your friends, you can actually look at the original 11 blog post with text. That explains it to you. And of course, this entire presentation is at that URL right here. So I think we're a couple minutes late. I'm afraid I'm not going to be able to take questions. We did take some during the class. And I thank you for that. I believe lunch is being served right now.
 Thank you very much for coming.  My name is Bruce Momjian.  I'm one of the PostgreSQL team members.  I work for Enterprise DB.  Many of you who have been here before already know many of  these things, but I have to say them anyway for those who  were not here.  This presentation along with about 30 others is at that  website right here.  In fact, this website I enabled on my website about an  hour ago.  So if you want to follow along, you are very well able to  download this PDF and follow along as I speak.  Fortunately, there will be a recording of this, so I'm  excited that once the recording comes up, hopefully I'll  get an email or somebody will tell me about it.  And then I can relink to it from my web page.  You'll see some not only do I have 30 presentations on  there, but also many of them have recordings next to them.  So there's a little tab, and you can open it up, and you  can see the recordings.  Where I've spoken, where I've given each talk.  That way I don't repeat stuff, which is not good for me,  worthy audience.  This presentation is fairly unusual.  What this presentation actually is, is inspired by a  series of blog posts that I put out about three months ago  relating to the handling of nulls.  Have any of you read any of those blog posts?  Great.  OK.  So for those of you who have read the blog post, there's  going to be some additional analysis of some of the  issues, and I think some of the discussion will be  interesting.  But I will admit that much of the content is directly from  that blog post.  What's really interesting about doing it the way I did it  was, A, it was a series of 11 blog posts that spanned  probably three weeks.  And what was really neat was I got feedback as I was doing  it, because of course I used discuss.  So people can comment on my blogs.  You might have noticed there was a keynote from  Discuss this morning.  That's not why I chose it.  Actually, it was just the best solution.  And effectively, what was neat was I was able to kind of  improve the blog and improve the content as I got comments  from the people who were basically  commenting on the blog post.  So there's sort of a combination.  Has anyone here actually comment on the blogs that made up  this series?  Because then you would be sort of an author above them.  Anyway, I'm sure there's somebody at PGCon who did.  So let's talk about this particular blog post, this  particular presentation.  The reason I was inspired to do it is because being somebody  who's on the PostgreSQL mailing list all the time, there is  a continual kind of difficulty in working with nulls.  In fact, that inspired the title, which is a question,  nulls make things easier.  Was this supposed to help me?  You're trying to help me with this?  A lot of people are very frustrated about the way nulls  behave.  If they find it very confusing, I will stay up front.  It is confusing.  And as we get to the end, I think we'll start to see sort  of a pattern of what's emerging.  But the bottom line is that the pattern that's emerged is  basically, eh, I kind of get it.  But there is no overarching single system that you can  sort of place upon nulls and say, here's the one fact I  need to remember about nulls and everything else will fall  into place.  Doesn't work that way.  I'm even going to explain why it doesn't work that way.  But let's get started to get going.  Any questions before we get started?  Again, I do take questions from the audience or those of you  who are not in my tutorial.  So I'm excited.  Let's get going.  What's null?  Now, we have a null in English.  Null means nothing.  It basically just means void.  There is sort of a confusion of how to represent that.  Is it a whole?  Is it nothing?  Is it a blank slide?  There's sort of how do you describe nothing with something  because you have to use something to describe nothing.  And in English, people don't get so confused.  There is the concept of nulls in various procedural languages.  SQL is not a procedural language.  It's a declarative language.  But in procedural languages, you do have the concept of null,  particularly in C-based languages, Java being one of  the better known ones.  And it's, of course, C++ and other sort of C-derived  languages.  You have the concept of null when you're assigning a value  to a pointer that doesn't point to anything.  And that's pretty easy to understand.  You've got a pointer.  It's not a data value.  It's a pointer.  And you're pointing to something that doesn't exist.  That's pretty easy to describe.  It's pretty easy to have a little diagram.  You have a box, a little arrow.  There's an x on the arrow.  It doesn't go anywhere.  That's pretty easy to get.  Where it becomes confusing, frankly, is in data.  Because unlike a pointer where you know the value either  points to a memory location or points to nothing, in data,  you aren't dealing with pointers.  You're dealing with a container.  You're dealing with a container that normally  holds a value.  You don't have pointers in SQL.  So you have a container.  It normally contains a value.  And now we have the challenge of representing no value or  nothing.  And that's probably the first thing that is the bender.  Again, when you're dealing with pointers, it's kind of  easy to say it points to something and points to  nothing.  That's pretty easy to understand.  When you have a container, and you have to represent that  container as containing nothing in the container, how do  you do that?  Well, in fact, we do that with nulls.  And the way Postgres stores nulls is internally, in fact,  is very similar to the way you'd expect.  We don't actually store.  For example, if you have an integer, and that integer field  is null, integer is normally 4 bytes in Postgres, or 8 bytes  if you do a big int.  But if the value is null, we don't store 4 bytes.  We don't store 8 bytes.  We have a separate area in the row which identifies which of  the columns is null, which of the fields is null.  So if you have three integer columns in a table, and you're  looking at a specific row, and the middle one is null, we  store the value for field 1, column 1, in the row.  We store the value for column 3 in the row.  And then we have a bitmap over here, which has, by the way,  slot number 2, we're going to set that bit high.  And that bit indicates that that second field, that second  column for that row, doesn't have a value in it.  So even in Postgres, the idea of representing nothing,  internally, we have to create a separate bitmap to handle it.  There's some performance reasons for that.  Of course, if you have a row that's mostly nulls, which  does happen, we actually talked about this a little bit.  On Tuesday, when we had the tutorial, effectively, you're  now looking at a null bitmap with 1,000 bits.  You're not looking at 1,000 empty rows or whatever.  But why do we need this thing?  Why do we need this thing in SQL that we call nulls?  Well, effectively, as much as people belly ache about it,  it's harder if you don't have it than if you haven't.  So as much confusion as I'm going to show you in this  future slides, where you look at it and you're saying, why  do they do that?  Why does it do it that way?  Why am I having to learn about nulls?  How many of you have been sort of surprised by null behavior  in the past?  Yeah.  I've got a lot of hands there.  So the question is, why are we surprised?  Why can't we make it so we're not surprised?  The bottom line is we need to represent this.  Don't have a value.  There's nothing in the data.  Why?  Because if you don't have it, it becomes a problem to  represent your data.  Strings, usually, if you don't have a data value, you can  put single quotes or zero-length string.  And that's like, I don't have a value there.  It's not the same as null, but we could probably get away  with it and never have invented nulls in the first place.  Maybe.  I'm not sure we could have.  But if everything was a string, we might have gotten away  in SQL without nulls.  But where it becomes a really problem is all the other  fields.  Everything's not a string.  Numerics, what do you put in a field that you don't want to  value in?  I'm sure many of you work with databases that didn't have  nulls, and you start to get into the special value.  Minus 100, minus 99, minus 1, zero.  And it's a different special value in every field.  And then we have to encode those special values in your  application.  And you have to remember that the special value for this  field is this, and the special value for this field is  that.  And obviously, that doesn't work well.  Not only is it a problem with your application, but the  database doesn't know your special value.  It doesn't know that a minus 99 over here and a minus 99  over there aren't the same, because hey, they're both  minus 99.  But there's actually some behavioral changes that we  need to represent in SQL when we're specifying something we  don't know.  And again, you're going to see that propagate into the  future slides.  Dates, same problem.  If you don't know the date or you don't want to date in a  field, if you don't have null, what do you put there?  1900, January 1st, 1970, all these crazy things that you  have to do if you don't have nulls.  And that's why we have to bite the bullet, and that's why  we had to get the nulls.  And what I'm going to do for the rest of the presentation is  basically explain how nulls get into your database, how  they behave in various situations, and then give  you an understanding so you don't get surprised.  So you can walk away and say, you know, I wish we didn't  have need nulls.  We do need nulls.  Here's how they behave in these situations.  I have to learn that as an application program.  This is a good question.  Time forget.  Any questions anybody has about the groundwork I said and  sort of the concepts that I covered?  OK, great.  So let's look at why we would use nulls, like abstractly.  What possible purposes would I have for a null?  Probably the most popular one is the unknown values.  So you're inserting a row.  You don't have information about all the fields you want,  but you want to get part of the data in.  This is very common in data entry.  You know the person's first and last name.  You don't know their birth date.  And you're going to come back later and fill it in.  Well, we don't want to put 0101900 in there, because then  they'd be 113 years old.  Or some magical value that we then have to kind of scrape  through our system looking for.  The logical thing to use for an unknown value is a null.  The second one is inapplicable values.  These are values that aren't unknown but are effectively  don't apply to this row.  It's not like I don't know the value.  It's like I'm never going to fill that value in, because  the value doesn't exist.  And I'm going to show you an example in the next slide.  The third one is empty placeholders.  And these are cases where you're doing some SQL.  You've asked for a row or part of a row.  And there's some values we just didn't supply.  And what do you want us to put there?  Minus 1, 0, 1900?  No, we're going to put a null there.  And I would argue that some have said that there, and  this is kind of going to go berserk.  But some have said that there should be three types of null  in SQL.  Has anybody heard that before?  Kevin?  It kind of makes it that there should be separate  flags around there.  Yeah.  So this is a very interesting idea.  And the reason it gets a little wonky is because the  behavior that you might expect for a value that's unknown  might be different than a value you expect in SQL if you were  to go for the logical way to handle it, would be different  for an inexplicable value.  I know I'm getting way out of the space for some people.  But you kind of see that when you're overloading multiple  purposes on a single value, then often by definition, we  can't generate a predictable behavior for this type of  value.  I'm going to show you a lot of very unpredictable cases in  the future slides.  So here's an example that I think takes it home for me.  And I don't know where I heard this example before, but  it's an example I've always used.  I've never seen where this came from.  Did I think about it?  I can't even remember.  But it's the idea that you've got an employee table and  you've got a spouse field.  I don't know why you track the spouse name maybe for  invitations to the Christmas, whatever.  You've got to call them up to say the spouse is died.  I don't know why you'd have it there.  But let's suppose you have a spouse field.  So those of you who are in my previous tutorial are  finding that very funny.  And you should.  Because there is no hook on this to hold it on.  So merely brushing by it is enough to send it launching  to the floor.  So employee.spouse.  So here's the three examples of how we might use a null in  this particular field.  The first one is we don't know the spouse's name.  The employee's just hired.  We didn't ask him the name.  They didn't fill it out on the form.  Who knows?  We couldn't read the form.  We couldn't read the name on the form.  That's a great example.  They filled it in so we know they have a spouse, but we  can't read it.  We're not going to wait for them to call them up.  We just want to get the data in there, and we'll come back  later.  We'll put the spouse in.  We would put a null in the spouse field for that particular  row, means we don't know the spouse.  Separate case would be the employee is not married, and  therefore has no spouse.  You can see there's different concepts.  We know we're not coming back, at least not in the next  week to put their spouse in, because they're not going to  get married in the next week.  The third case, and this is the empty placeholders, we do  an outer join on the employee table, and we don't have a  row there.  I'm sure many of you have dealt with outer joins, but  effectively in an outer join, when there's a column that  doesn't match, and you've got to put something there, you  put a null there.  So there's three uses of nulls, really distinct uses of  nulls, that we really are representing three different  cases of we don't want a value in a field.  Any questions?  So let's get into the meat of it.  First thing, don't use nulls if you don't have to.  I know there's some people who have kind of gone over the  deep end with nulls.  Usually if you're using nulls too much, you start to get a  lot of the unpredictable behavior that we talk about  here.  I even mentioned this two days ago when we talked about the  not null constraint on a column.  Remember that?  I said, if you don't want a null, don't allow a null to  the column.  And frankly, we had a discussion of what percentage of  columns probably could be not null, but maybe 80% of your  columns probably should be not null, meaning just don't  let a null get in there.  Customer name?  If you don't know the customer name, you probably  shouldn't be entering it into the system.  We can get away without an employee spouse field just  fine.  But if you don't know the name of the employee, don't put  them in.  And you start to get into some constraints of preventing the  nulls.  So example I have here is actually if you've ever used  pliers to take off a nut, surprisingly there's virtually  no picture of this nut grinding pliers problem on Google.  I would love somebody to email to me, but that's the only  one I could find.  So the point is that nulls are to be used only in special  circumstances.  They're not really to be used when you can avoid using them  and because of some of the behaviors that we talked about.  Warning.  This is actually from CJDate.  Again, Kevin Grittner, who gave a talk previously, actually  talked about Cod and how he thought there should be three  nulls.  Here's the other father of SQL.  And he's basically coming out and saying don't use nulls.  Although it's a little nuance there.  But he's being really honest.  Is this right his opinion that nulls, at least, is currently  defined in the meta-sql are far more trouble than there  were and should be avoided?  They display very strange and inconsistent behavior.  It can be a rich source of error confusion.  So if you've ever been confused by it, it's not you.  It's nulls.  It's not your fault that you're confused.  And he's basically saying I will urge you not to use them,  but I will seem contradictory because I will talk it.  I think of nulls as a drug.  If you use properly, it works.  But abuse it, it can ruin everything.  Best policies, avoid nulls and when you can and use them  properly when you have to.  That's actually Joe Selco, who actually spoke at Prague  and at Austin Postgres events.  It's a very well-known SQL guy.  SQL Super Smarties is the book that he's talked about.  He's basically very honest that this is really weird.  It's not a great system.  We have to live with it for the reason of the pliers thing.  But it's the reason we're talking about today.  So I'm going to have a lot of queries in this text.  What I've done is highlight the part you should look at in red.  So everyone seems to remember this picture just fine.  So when I go to this picture, everyone's like, ah, I see  where I'm looking.  So that's what you should be looking for, the red.  So effectively, let's walk through some queries here and  let's look at exactly what type of behaviors I'm talking  about and see if we can at least get a mental framework of  how bit nulls behave in various circumstances.  Any questions?  So first thing you can do is you can select a literal null  value.  Just select null.  Works just fine.  As you can see, nothing shows up there, which is maybe not  good.  What you can do in PSQL is you can ask PSQL to show you  null with a certain string.  And I've done this throughout the rest of the  presentation because, frankly, showing you nothing really  doesn't help you.  It's confusing.  Like, we don't have enough confusion.  We've got to add that.  So effectively here, if I do select null, I get a null  string there.  It tells me that's a null.  It's not six characters.  It's really a null value.  So if you see that null in parentheses, that's what  you're looking at.  OK, so first example.  We create a table called null test.  We insert a literal null into the field, again, assuming  we don't know the value or the value doesn't apply.  And then we do a select from the table.  We literally see that null appearing.  Nothing surprising there.  We put a null in.  We got a null out.  Second case, here I'm inserting into the same table.  And here's the first case where we implicitly add a null.  You don't see any null up here, do you?  No, there's no null there.  But when you look at the output, you can see row two also  has a null just like row one.  So here I explicitly add a null.  There's a null right there in red.  This one doesn't have a null.  How did I do it?  I actually supplied the columns in the table I wanted to  insert.  So the table has columns x and y, super creative of me.  I've told the system I only want to insert into column x.  What's the system going to use for column y?  No, and that's exactly what it does.  So here we've actually implicitly, and Mr. Phil, I  don't think it's here.  Robert, is he coming?  No, OK.  So anyway, Phil had the idea of showing this.  I think it was a comment that he had suggested in the thing.  No, actually, that might be, take that back.  That is a mistake.  It shouldn't be there.  Sorry about that.  I will fix it.  That's actually from previous presentation, and I guess I  didn't delete that part.  You have to null it.  Have to null?  Exactly, exactly.  So let's get, I talked to you about the ability to  specify not null in your fields.  This is exactly what I'm doing here.  I'm saying that my new table cannot have a null in the  first field and cannot have a null in the second field, and  I've now blocked out nulls from getting inserted.  I've prevented from explicitly getting at it, and I've  prevented from implicitly getting at it.  Is everybody with me on this?  So by blocking nulls at the two columns, explicitly,  implicitly, value doesn't get in.  Let's start to look at what happens when you have a null.  So we've talked about getting nulls into the system  implicitly, explicitly.  Let's talk about what happens.  How do you manipulate a null?  What happens if you try and add one to a null?  So here I'm adding one to a null.  The result is null.  So it isn't one.  It isn't zero.  It's null, because an unknown plus one is still an unknown.  And this is maybe confusing, but that's kind of makes sense.  And you have to sort of prepare yourself for that.  I'm going to show you some other cases a little later on  where it starts to really get crazy.  This kind of makes sense.  But if you start to extrapolate this into some other cases,  it starts to get crazy.  What if I concatenate a null with an A?  I don't get an A. It doesn't act as a zero-length string.  It's a null.  And the same thing if I concatenate the other way,  it's also a null.  So either way, it's going to be null.  And this kind of makes sense.  If you start to create a sentence and you're concatenating  stuff together, one of the fields is null.  All of a sudden, the whole thing becomes null.  Very surprising sometimes.  Here is a little different case, kind of setting things up  here.  So I'm creating a table called ink test, which is an  increment test.  I insert 30, 40, and null into it.  So I'm kind of prepping my table.  Then I add one to it.  So 30 becomes 31, 40 becomes 41, and then null is still null.  Follows what we talked about here.  We're good.  Let's talk a little bit about comparisons.  Now this is operations.  This is concatenate increment.  Yes, you have a question?  Yeah.  I am getting there.  And it will be crazy.  Yes, it's really not pretty, but I have to do it.  So if I start to look at comparisons on nulls, we start  to get some interesting behavior as well.  So if I say, is null equal to 1?  Well, that's easy.  That's null.  And notice it's not false.  It's null.  And that's where you start to get into another sort of  twisty passage of nulls, is that when you normally think  of comparisons, you think of Boolean logic, Boolean being  true or false.  When you're starting to look at nulls, you have, when you  deal with SQL, every comparison is actually a  three-valued logic.  It can be true.  It can be false.  It can be unknown or null.  And there is some very unusual behavior that you start to  see in later queries where that unknown doesn't even  behave like false.  You're like, wow.  So be prepared.  This is common.  Is it equal to 1?  No.  Is it equal to a zero-length string?  No.  It might display as a zero-length string to the user, but  it's not the same as a zero-length string.  Does null equal null?  Now here's where you start to get the twisty little  passages all alike.  So does null equal null?  Actually, we don't know.  Because the null, one null could have come from some place.  The other null could have come from some way itself.  It could be two rows that are both null or equal.  We don't know.  That's kind of the logic that they're having here.  You can select if null is null.  I'm going to get to that.  Yeah.  So this is comparing a literal value of null to some other  literal value of null.  This kind of really crazy.  Again, as I said, there's some extrapolation that starts to  go in.  When I said null plus 1 is null, I said we're going to see a  case where you start to extrapolate that out.  It starts to look kind of weird.  Is null less than null plus 1?  Well, I went to math class.  Null less than null plus 1 is probably true.  Unless the two nulls are different and came from  different places, at which point I don't know.  And that's basically the tactic that the SQL standard  committee says is that those two nulls could have come from  different places, be in different rows.  They aren't necessarily the same null.  Because again, if you think of adding rows, you have a column  that represents weight.  So it's like, I'm 1 pound.  I'm 5 pounds.  I'm 10 pounds.  I'm 20 pounds.  Different parts.  What if you have two rows that have null weights?  Well, that means the first row might be 1,000 pounds and the  second row might be 2,000 pounds.  You don't know.  So the concept here is that because I've got two nulls, I  can assume that they're the same value as I would if the  two weights were 10 pounds and 20 pounds.  I know what those values are.  So even something that logically you would think would  be true comes out as null or unknown.  So query comparisons, here's where it starts to get  interesting.  Select one where true.  That's going to return a row.  Select one where false.  That's not going to return anything.  That's not a null row.  That's nothing.  Select one where null returns nothing.  And in fact, somebody argued, and I think this might have  been a comment.  I can't remember.  Somebody said the where clause should be called the  sure clause.  Like, are you sure it matches?  Because effectively, where only returns a result on  true, not on false, and not on null.  And here's where you get that three-valued logic  thing going on.  OK.  So let's take a look at the and and or kind of case.  True and null is null, because we don't know if the null is  true or not.  That kind of makes sense.  Select not null.  The inverse, the opposite of null, is of course null.  Again, a little surprising there.  I guess we don't know if it's true or false, so we don't know  if it's true or false.  Noted, and therefore it's unknown.  No operator comparisons.  Again, I'm kind of coming back here to look at some of the  row comparisons.  So we're really combining in my mind, we're combining these  tests, the operator tests that we went through, and the  where clause test that we went through.  And I'm going to go ahead and do a little bit of a  comparison that we went through, and I'm going to show you  combined.  So now I'm back to my ink test table, 30, 40, and null.  Very easy to understand, right?  Three rows.  I want all the rows where x is greater than 0.  30, 40.  Good, makes sense.  I want all the rows that are less than 0.  Nothing.  There are no rows here.  Nothing's, we don't know.  Because null is, we don't know.  I want all the rows that are less than 0 or greater than  equal to 0.  Now that's a real tough one not to say an null and null  should match.  But it doesn't.  Because again, this returns a null, this returns a null.  The result is a null.  It goes into the where clause.  The where clause says, I only return on true.  And you get nothing.  Yes, sir?  So this is true for the, for n null and null, it's null  and null at the same time.  Yeah.  Yeah, it's kind of like 0 times 0 times.  I almost think of it as multiplication with a 0.  Like as soon as you multiply, you can have a billion.  And since you multiply by 0, it's 0, right?  So when you start to chain these things together, any of  these results that return null, it kind of just, it  pollutes the whole expression.  And the expression of how long it is or how much we try to  kind of narrow it down.  And it just polluted the whole thing.  And one of the criticisms of nulls is that effect, that a  null has a tendency to pollute itself way beyond the  column it's in.  And just starts to spill out all over the place.  And has a very negative or very surprising consequence in  areas you might not have suspected.  Another case, give me all the values that aren't 10.  That's easy.  Give me all the values that are 10 or 10.  Aren't 10 or 10, right?  So either you're 10 or you're not 10.  No, my null is not there.  Again, that sort of pollution effect across expressions.  Where it starts to get a little confusing is the not in  clause.  And I'm going to kind of show you an example of expressed  doubt.  So if I'm doing a comparison, I'm saying one isn't two and  one isn't three.  Well, that's true, because one isn't two and one isn't three.  If I do a separate comparison, one isn't two, one isn't  three, and one isn't null, then I get a null.  Because I don't know if that null might be a one.  I don't know.  So if we convert that, so I say select A in null, what I  would think of is a null, because effectively, we  don't know.  But if I say A is not in a row that's just a null, again, I  get a null.  So a lot of times you have to take away is that a lot of  times people say, well, I'm not going to get null.  A lot of times people say, well, my in clause, I  understand nulls are going to prevent that from matching, but  my not in clause, well, that shouldn't have a problem.  They think of it as the negative.  So I'm going to get all the rows that are in a certain set  and all the rows that are not in a certain set.  And the two results together should be the full set.  In my mind, that's what I'm thinking.  I got everything that's in this group.  I get everything that's in A. I have everything that's not  in A. Combine those two, I should get the original  group.  And what I'm saying is with nulls, that is no longer  true, because nulls mean that I might have some things that  are in the group.  I don't know it.  Null, not in the group.  I don't know it.  Null.  And they don't show up.  And a lot of times you spend time debugging queries  trying to figure out why I'm missing rows.  And a lot of times the null is the culprit.  A little more expanded example.  So is A in A or null?  Now this one actually works, because we're saying, is A in  a result that's in A or null?  What's in the A?  So we're done.  We don't have to look over here, because we know this  matches.  And we go like, OK, true.  Good.  We're go.  Not in is where it bites everybody.  And I'm not going to get a list of hands that's bitten.  But not in always is a thing that gets people with nulls.  So is A not in A or not in null?  In fact, this one works, because you know that A is in A.  So you can return false.  You're good there.  Is A in B or null?  I don't know.  Here's the one that kills everybody.  Is A in B or a null?  I don't know.  And a lot of times people do sets, and they'll say, give me  everything that isn't, give me everything that's less than  100 or whatever.  And then all of a sudden, there's all these rows that  don't show up, and they can't figure out why.  It's because they've got nulls in there.  In fact, it pollutes the whole system.  So I could have 20 bees over here.  I could have thousands of rows.  As soon as I get this thing, the not in just says, I don't  know.  It doesn't return to anything.  OK, so it really can get you.  Really can get you.  Let's expand out the query.  Is A equal to B or is A equal to null?  This is the bottom two queries expanded.  Is A in B or is A in null?  I don't know.  Is A not in B and is A not in null?  Again, I don't know.  This is where you start to see the problem.  Questions?  Great.  Explicit null compare since somebody already  talked about this, I showed an example up here, null  equals null.  Remember that?  That's like, is null equal to null?  It returns, I don't know.  If you need to test for a null, you probably are explicitly  going to need to use a special clause.  Not equal null, but is null.  Is null, is null?  Yes.  This is where I'm mapping now my null to into the Boolean  world.  So I get my null out of the three-valued logic world,  and is null basically pulls it into the Boolean world when I  know my result's going to be true or false here.  Same thing with not in, is null not in?  Select null is not null, and it's false.  Yes, sir?  The keyword is null.  The keyword is null.  So the is is not something you can use on its own.  No, you don't.  It's always is null.  Good question.  And again, this pulls it out.  So for example, when we're trying to do something like  not in, a lot of times what you will end up doing is burying  an is not null test in your subquery to prevent this type  of comparison.  So what they'll do is they'll select null, and they'll say  where column is not null.  And they'll prevent the nulls from propagating up into this  result set, and therefore the not in will properly evaluate  by extracting out.  You know what I'm saying?  So for example, if you want all your customers who are not  in Pennsylvania, you would say maybe you want to select all  your customers here.  I don't know for whatever reason.  If you have some customers that are null, you're not going  to get any rows back.  Because it's like, well, customers not in, and list  of values is null, so therefore it's not going to match.  So a lot of times what people do is they'll say is not null,  and they'll prevent the nulls from getting up into the  target list and being seen by the is not null.  I know it sounds like I'm gibbering, but you know what  I'm saying?  You prevent the nulls from getting up to the target list,  and therefore your is null, your in or your not in now  behaves logically that you might not have been able to do  before.  OK?  Could you say that the one back on start?  Yeah.  Because it's not a reasonable approach to  benefit the performance of the life story, not for the  value to the comparison.  I don't think performance really ever enters into the use of  is null and is not null.  It's more of a desire to exclude nulls from polluting out  into the rest of your comparisons.  That's really where I think the win is.  Because as I showed you with concatenation and all these  other things and not in and in and add and so forth, a lot  of times the way that null propagates it's this sort of  polluting thing that sort of wants to spread uncertainty  through the whole expression or even up into the upper query.  And a lot of people who know I've got some data that I don't  want to process.  OK?  They'll just say they'll just exclude anything that has a  null in the field they're working with.  And that really is the filter that prevents it from going  out and up and causing sort of unpredictable behavior.  Yes, sir?  There is a problem with the optimization of is not in  sub-query.  The references of data goes unoptimized.  So, for example, not exist.  The semantics are different.  But not in some cases optimizes very poorly.  OK.  But it's not unique to nulls.  It's a behavior of the.  Is there a number of elements?  OK.  Right.  So if we go down to this test, it starts to make a little  more sense.  We're kind of bringing things together here.  So we have our three rows in ink test.  We can say select star where x is null.  And I'm now pulling out just the null rows.  And now select intents where x is not null and I'm pulling  out the non-null rows.  So as I said before, you think of your result set as here's  all the ones that are in.  Here's all the ones that are not in.  Combined, that should be my full set.  You should probably have a third version which says here's  all my ones that are in.  Here's my ones that are not in.  And here's all my unknowns.  And you now have to think of having a third set of values  which combined with the first two will give you your  original set.  That's when I, a lot of times I remember when it was an  application program, I would say give me all the positives  and then give me all the non-negatives.  I mean, I don't know what it is.  Give me all the negatives and then give me all the  non-negatives, right, zero and positive.  And I would think that would be my whole set.  And I'd have two queries.  I would say all the negatives, I'm going to do this with it.  All the non-negatives, zero and positive, I'm going to do  this with it.  And I wouldn't process all my rows.  Like really?  It's like, I don't have null in that column, so therefore I've  got nulls in that field.  And now all of a sudden I have to be, I need the third block  which is going to say give me all of the null rows, values  for that field and do something special with those.  And that's why I think a lot of the concern you saw earlier  in the quotes, people saying, ah, don't use them, right?  I mean, be careful when you use them because now all of a  sudden your logic has to be, you have to now think when  you're doing these type of queries, how is a null going  to behave in all these situations?  A lot of cases you don't care.  You give me everyone in Pennsylvania.  You don't care if they're not in Pennsylvania, they're  just not in Pennsylvania.  In most cases you don't care.  It's usually when you're trying to split up your result set  into two parts.  Like give me all the Pennsylvania people, give me  everyone who isn't in Pennsylvania.  That's when you usually get caught.  Because there is that set that isn't in Pennsylvania or not  in Pennsylvania.  What about the big, empty example of that?  That's been one of my favorite things in the official  of the defense.  I think the big difference is null.  I don't want to say a big difference.  Yeah.  Null is about the only choice I've seen with that.  In other words, when you get to use 1, 1, h, and 100,  I'm funky.  How do you deal with that?  Recasting that field is strange.  Making it something other than null.  Glitch, you asked.  I'm actually about to get to that.  I actually have some commands specifically for that case.  I think I have one more slide, two more slides.  So another, remember I told you about null is null and null.  There's another clause that we have, which is a little  different called distinct.  Distinct's a little hard to get your hand around.  But it allows you to test for whether a value is either  different from or null, and it returns a truth.  For example, I'm saying is null distinct from 1?  Millenia, what it means is is it 2 or is it just a null?  And both of those in distinct land are  different from the 1.  I'm sorry, I think I've distorted it by using a 1 here  and a 2 here.  So my apologies, I shouldn't have done that.  Effectively here I'm saying is 1 different than 2?  Yes, it is.  Is null different from 1?  Yes, it is.  Is null different from null?  No, it's not.  So what this allows me to do is instead of doing an equality  comparison with a value, null equals 1, which obviously  returns null, if I use is distinct from, it's  effectively saying, are you not equal to that value?  And if you're null, you're still not equal.  I realize I don't like the fact this is a negative.  So distinct from is effectively a not equals.  And in fact, what's really crazy is there is a not  distinct from which is effectively equals.  So I don't like that.  I don't know if I could have thought of a better solution.  But effectively it allows us to do comparisons between nulls  and potentially non-nulls, where we say a not equal or  an equal with a null returns false.  So if you need to do quality, unequality comparisons, not  is null, is null is testing if it's a null.  That's pretty easy.  But if you need to do a comparison between a column  and something else, distinct and not distinct are actually  pretty nice to use, although the negativity really  throws me for a loop.  Is that the answer to the question?  Yes, it is.  Yeah, we kind of got stuck there.  Here's an example.  So I've got two columns, 1, 1, 2, 3, and null and null.  And I say is x, which is the first column, distinct from y.  Now, 1 is not distinct from null.  Yeah, 1 is not distinct from 1, so we return it.  2 is distinct from 3, so we don't return it.  Is null not distinct from null?  I know it's like true.  And I know I just can't get ahead around a lot of times.  But effectively, it allows us to, again, map that null  behavior into a Boolean behavior, which is only going  to be true or false.  So think of it as mapping what would normally return a null  for equality comparisons into a true or false  that you can then work with.  Particularly, this is useful for joins.  Because in your application, say, oh, it's null,  do this or not equal.  But if you're joining two columns and some of them  might be null, this distinct form can be very helpful.  So the dense operator is only for nulls?  Is it reason for that?  If you look at the grammar, I don't even  think we have an is operator.  It's like it actually has to be those keywords in that order.  Yeah, where there is is optional, not distinct,  is optional, not null.  It's really, there is no is alone without either null.  I guess you could say it's for null behavior checking.  Makes sense.  Yeah, I can see that.  Yeah, you can say is true.  Yeah, yeah, yeah, yeah, yeah, yeah, yeah.  So you're right.  You could do integer, integer, or the same as null.  Ooh.  So maybe it's only for Boolean?  Maybe?  Because you're right, both of these return Booleans is null.  And you can say false is true, and that'll work.  So you're right, is, I guess, is an operator.  Never thought of that.  It's its own little thing.  But you're right, integer, is integer doesn't work.  Let's take a look at ordering.  Ordering is a case where I think we basically  sort of discard a lot of the rules that we talked about  already, and we make up new ones.  So here we have a table that has a null, a two, a one, and a  null.  It looks just like this.  What you're noticing is that these nulls are now all the  way at the end.  And when we talked about nulls equaling nulls, they don't.  They always return null.  But when you order them, all of a sudden, they're equal to  each other.  Magic.  And the reason that they do that is because they really  couldn't do anything else.  Sort of randomly sprinkling the nulls in the ordering really  wasn't going to do anyone any good.  So effectively, you just had to fall back and say, well,  what's the practical behavior that would make the most  sense?  We'll just put the nulls together, even though they  don't equal each other.  And we've hounded that to people and beaten it into their  heads all this time.  I think technically what they would say is that you get  the unknowns at the end.  So the unknowns just seem to come at the end.  Same thing.  We can actually specify the nulls to go first.  And we can control and say we want the nulls to come first.  Similar to indexes, we throw the baby out a little bit.  Or maybe not.  I don't know.  We're sort of heading into crazy land here.  So if I create a unique index, I can actually put multiple  nulls in that unique index just fine.  Because they don't equal each other, so therefore, they're  unique, I think.  Sierra can obviously put nulls in there just fine.  And I think there is a, is it oracle that only rounds one  null in a unique field?  There is some.  Yeah.  There's some other databases that don't.  I don't remember if the standard requires us to do this,  or we break the standard, or I don't know.  We just thought it made the most sense to allow multiple  nulls in a field, in a unique field.  Because yeah, the idea that one null would equal another null  doesn't make any sense.  So they are kind of unique, if you look at it sideways.  Aggregates, I promise to bend your brain on this one.  Again, you sort of get a practical behavior that may  not match what you saw before.  Here we have a table.  I'm putting in 7, 8, and null.  Drag test, very easy to understand.  I'm going to just run a bunch of aggregates on this.  Count, star, count, and a field name.  Some, min, max, and average.  When you do count star, it counts rows, irregardless of the  values in the row.  So you get three, because there's three rows there.  When you specify a field in the count parentheses, then we  only count non-null values.  How convenient.  So in fact, we have two here, because there are only two  non-null values up there.  Not very obvious that that's what it would do, but you just  have to burn it into your brain.  Count star and count on the column actually have these  different behaviors.  Some, that's pretty clear.  Well, 15, well, not really.  Because 7 plus 8 plus null is null.  We talked about that in the earlier slides.  But I would get to throw that out, just discard that.  I would argue that just as count doesn't look at nulls in  the field, sum, and min, and max, and indeed, average, don't  look at nulls in the field.  And you just have to learn that.  So the sum is 15.  It's the sum of the non-null values.  The min and the max make perfect sense.  The average, again, you're only looking at two rows.  So the average, it's sort of 7.5.  Odds are with the null in there.  It really isn't 7.5.  It's something else.  And it really is null, in my mind.  But I guess returning null for that field just wouldn't  make a lot of sense.  There'd be a lot of cases where you had to say, is not null  all over the place for aggregate.  So they just throw them away.  I'm sorry?  You're also dividing by null.  Yeah, I'm dividing.  Thank you.  I'm even dividing by null.  It just takes me to a whole new level.  What's also interesting, if I go delete the table and I just  run the same query on empty table, I start to get  reasonable values.  I get count of 0, no rows, count, both of them are 0.  I get a sum not of 0, which kind of surprises me, but null.  And I think they did that because the min and the max and  the average problem, the min and the max particularly, would  have to be null.  You couldn't guess what a max of no rows would be.  So they just kind of across the board said, we're just  going to return all nulls if there's no rows there.  Is that obvious?  I don't know.  And I don't think so.  But that's what they did.  Put some rows back in there again.  Now we're going to put two null values in the table.  And we're going to run the same query with a group by.  And this is where we're really out in space here.  Because as I said before, a null never equals a null.  But group by, it's like, hey, let's go.  We're all together as nulls here.  So just as the aggregate kind of had this weird behavior for  nulls, group by, all of a sudden, all the nulls equal  to each other.  Hey, there you go.  So my takeaways, don't assume aggregate with nulls is going  to behave just as though you typed out the sum or the, you  know what I'm saying?  It's just not the same.  It has some sort of super behavior.  And it doesn't even ignore nulls, which we could  understand here we're ignoring nulls.  I get that.  But wait, when I do a group by, nulls are now good  citizens again.  And they are all equal to each other.  This makes no sense at all.  And I don't pretend it makes sense.  And I don't think the committee even pretends it makes  sense.  They sort of punt and they say, well, we think this is the  most reasonable behavior.  Even though it's quite inconsistent, even from the  previous slide.  Yes, sir?  AUDIENCE member asks, do you guys use nulls as  consistent?  Yes.  Internally, they'd have to use as null to make that work and  then sort of put them in their same bucket.  So the rows, the 7 and the 8 is pretty easy.  If I do a count star, this is really, really interesting.  If I do a count star on my null rows, I get 2.  But if I do a count with the x there, I get 0.  Because remember, the count with the field in there ignores  the nulls, even though the group by is clearly processing  the nulls.  And I have a sum of null, which makes sense, and a min and  a max in average old null.  That part I get.  Mapping nulls to strings, this is what the gentleman in the  green shirt asked about, and dates and things.  Again, there are some cases where the nulls are just  going to propagate.  How do we concatenate something that has a null?  This is driving me crazy.  We do have two commands.  One is called coalesce.  And what coalesce does is basically it says, if it  basically allows you to have as many parameters as you want,  and will return the first non-null parameter.  Crazy definition, frankly.  It's like the distinct being not equal to.  It's like, throw as many parameters as you want, and I'll  give you the first non-null one.  I don't know why anyone would want more than two, but they  support it.  What I'm basically doing here is I'm saying, if the first  fields null return a zero, that's pretty stupid.  If the first fields null return that string, I am null.  Good.  That's what we could use for a date.  We could say, if we've got an f, a g, and a null in a  table, I'm going to say, instead of returning x, I'm  going to return coalesce of x and na.  Not applicable.  Not unknown.  In this case, I know null means not applicable.  Don't ask me how I know that.  I'm just saying.  In this case, I'm saying not applicable.  Yeah?  If you do a where clause, it'll use the previous table  value, not the coalesce.  If you do a where clause, you're going to get x.  You're not going to get the coalesce.  If you want the coalesce, you have to duplicate the coalesce  in your where clause, and then compare that to a string.  That would work.  Yeah?  If you name the coalesce, the problem is the way the SQL  is executed, it's everything from the from down and then  the target list.  So the names are typically not accessible only at the end of  the query.  Now, there are some cases where that's not true,  particularly in order by.  You can, I think, reference the column names that you  specify.  But in the where clause and the from clause, you don't have  access to those names up there.  Unless Postgres did something wonky.  But typically, those names are really for display only, and  kind of also for group by and order by.  And again, I'm not proud of that behavior, and I think it  was defined that way by the standard.  So we're kind of stuck.  But it's, again, a very inconsistent behavior, I think.  Because I tell people, well, the target list is only  evaluated at the end, but then you can mention the, in  Postgres, I believe you can mention the aliases in the  order by.  But you can't mention them in the where clause.  Crazy.  I'm not proud of it, but I think we did the best we could.  I think that's what it was.  So here I'm getting Na in my field instead of null.  So this would be what we use for my date.  Here's another case.  Here's my exact case.  I want to concatenate three fields.  And this one, if there's a null there, I just want single  quotes.  So I would normally put the column here, the column.  Instead of a null, I'd have a column.  And I'd say, if that column is null, give me zero length  string.  Just disappear.  If it was null, just disappear.  So the next one actually gave me a case  where I had a solution.  Great.  And this one is kind of a fix for the previous one.  So remember I said that when you do a sum on a column, if  it's null, you get null.  So basically what I'm saying is, if you do a sum and the  sum returns null, I want a zero there.  It allows you to kind of whack around this thing so you  don't get a null here.  So I'm kind of combining areas and coalesce there to make  it work.  Mapping strings to nulls, this is the opposite.  So instead of taking a null and mapping it to a string, I'm  going to map a string to a null.  So here I'm saying null if x is null.  No, no, no.  What am I saying?  Return null if x is equal to that string.  So now instead of taking nulls and wanting strings out, I'm  testing for strings and I'm having nulls come out.  So remember, nulls in, string out is coalesce.  String in, nulls out is null if.  I still can't remember them sometimes.  But basically what I'm saying here is I'm saying if x is  na, return a null.  In fact, I'm doing that right here.  I'm actually specifically putting in na.  This is the sort of combined.  So I'm taking a null, converting it to na, and then  na is going to match that.  It's going to return a null.  So I'm doing both in and out.  Just as an illustrative, confusing thing that I just  like to do.  Yes?  Yeah?  Can you repeat the last one?  Yeah.  In the beginning of the thing, you argued that you  could be involved in the US methodology.  You faced this in the real life application.  And the iPad told us some.  You could argue the problem was that there should never  have been nulls in that column I summed in the first place.  Yes, you definitely could do that.  Yes, sir?  Or it's an outer join which you can't avoid.  That's where you start to get some interesting stuff.  A little bit to wrap up.  We also support row expressions.  Again, there's some pretty unusual behavior here.  Is a null integer array null?  Yes, it is.  Is an empty integer array null?  No, it isn't.  Is a null inside of an integer array null?  It is not.  Getting a little crazier.  We have row expressions.  Is an empty row a null?  Yes.  Is a row containing a null null?  It is.  Is a row containing two nulls a null?  It is.  Is a row containing a null and a non-null null?  It is not.  Is a row containing a null and a non-null not null?  It is not.  And then is one or two is not null.  That's true.  So effectively, the behavior is if there's only nulls in there,  we can return true.  We know what it is.  There's only non-nulls in there.  We can return true.  We can test it.  If there's a mix, we're going to return false.  Just that's the way it did it.  Distinct from this problem.  Yeah, that would kind of clean it up.  Another case where if you have an empty table  in your select clause, that effectively return a null row.  I know that's kind of weird, but it's  assumed that every select clause in a target list  is going to return one row.  If it doesn't return any rows, we will effectively  throw a null in there.  And this is a surprising behavior, I think,  and somebody had noticed on the email list a while back.  So I think I get it.  No, you don't.  It's really a crazy mix of behaviors.  Jeff Davis, who you will see walking around, very nice guy.  He says, he had a blog post, and I pulled this out of there.  He said, oh, that makes sense.  When you see individual behaviors in null,  they look systematic, and your brain quickly  sees a pattern and extrapolates what might happen  in other situations.  Often, that extrapolation is wrong,  because null semantics are a mix of behaviors.  I think the best way to think about null  is as a Frankenstein monster of several philosophies  and systems stitched together by a series of special cases.  And I think I have adequately illustrated that here.  I wish there was a systematic way I could present it to you,  but for practical reasons, and because of the way  the SQL standard has defined it, it  is a Frankenstein of behaviors.  But hopefully, this presentation has helped you  to identify that, not to feel like you've  missed the boat somehow, and to realize  that when you see nulls in different circumstances,  you can now sort of map how you would think those would behave.  And with that, there's nothing left to say.  So thank you very much.  The actual series of blog posts is right at this URL.  So if you want to share this pain with your friends,  you can actually look at the original 11 blog post with text.  That explains it to you.  And of course, this entire presentation  is at that URL right here.  So I think we're a couple minutes late.  I'm afraid I'm not going to be able to take questions.  We did take some during the class.  And I thank you for that.  I believe lunch is being served right now.
Nulls_Make_Things_Easier__2_0_683.png Nulls_Make_Things_Easier__2_684_743.png Nulls_Make_Things_Easier__2_744_2291.png Nulls_Make_Things_Easier__2_2292_2819.png Nulls_Make_Things_Easier__2_2820_3575.png Nulls_Make_Things_Easier__2_3576_3815.png Nulls_Make_Things_Easier__2_3816_4115.png Nulls_Make_Things_Easier__2_4116_5675.png Nulls_Make_Things_Easier__2_5676_9107.png Nulls_Make_Things_Easier__2_9108_9671.png Nulls_Make_Things_Easier__2_9672_9851.png Nulls_Make_Things_Easier__2_9852_10643.png Nulls_Make_Things_Easier__2_10644_11015.png Nulls_Make_Things_Easier__2_11016_11411.png Nulls_Make_Things_Easier__2_11412_12347.png Nulls_Make_Things_Easier__2_12348_14099.png Nulls_Make_Things_Easier__2_14100_14207.png Nulls_Make_Things_Easier__2_14208_14567.png Nulls_Make_Things_Easier__2_14568_15491.png Nulls_Make_Things_Easier__2_15492_17231.png Nulls_Make_Things_Easier__2_17232_17651.png Nulls_Make_Things_Easier__2_17652_17855.png Nulls_Make_Things_Easier__2_17856_18479.png Nulls_Make_Things_Easier__2_18480_18947.png Nulls_Make_Things_Easier__2_18948_34379.png Nulls_Make_Things_Easier__2_34380_34787.png Nulls_Make_Things_Easier__2_34788_34979.png Nulls_Make_Things_Easier__2_34980_37199.png Nulls_Make_Things_Easier__2_37200_37619.png Nulls_Make_Things_Easier__2_37620_38195.png Nulls_Make_Things_Easier__2_38196_39095.png Nulls_Make_Things_Easier__2_39096_39911.png Nulls_Make_Things_Easier__2_39912_40559.png Nulls_Make_Things_Easier__2_40560_41279.png Nulls_Make_Things_Easier__2_41280_44111.png Nulls_Make_Things_Easier__2_44112_44879.png Nulls_Make_Things_Easier__2_44880_45107.png Nulls_Make_Things_Easier__2_45108_45575.png Nulls_Make_Things_Easier__2_45576_46763.png Nulls_Make_Things_Easier__2_46764_47507.png Nulls_Make_Things_Easier__2_47508_47735.png Nulls_Make_Things_Easier__2_47736_47867.png Nulls_Make_Things_Easier__2_47868_48023.png Nulls_Make_Things_Easier__2_48024_48227.png Nulls_Make_Things_Easier__2_48228_48755.png Nulls_Make_Things_Easier__2_48756_49511.png Nulls_Make_Things_Easier__2_49512_49703.png Nulls_Make_Things_Easier__2_49704_53375.png Nulls_Make_Things_Easier__2_53376_53603.png Nulls_Make_Things_Easier__2_53604_54251.png Nulls_Make_Things_Easier__2_54252_55031.png Nulls_Make_Things_Easier__2_55032_55115.png Nulls_Make_Things_Easier__2_55116_57323.png Nulls_Make_Things_Easier__2_57324_58019.png Nulls_Make_Things_Easier__2_58020_58403.png Nulls_Make_Things_Easier__2_58404_59807.png Nulls_Make_Things_Easier__2_59808_60059.png Nulls_Make_Things_Easier__2_60060_60203.png Nulls_Make_Things_Easier__2_60204_60923.png Nulls_Make_Things_Easier__2_60924_62039.png Nulls_Make_Things_Easier__2_62040_62171.png Nulls_Make_Things_Easier__2_62172_62411.png Nulls_Make_Things_Easier__2_62412_62579.png Nulls_Make_Things_Easier__2_62580_63371.png Nulls_Make_Things_Easier__2_63372_65531.png Nulls_Make_Things_Easier__2_65532_66203.png Nulls_Make_Things_Easier__2_66204_73079.png Nulls_Make_Things_Easier__2_73080_82571.png Nulls_Make_Things_Easier__2_82572_82703.png Nulls_Make_Things_Easier__2_82704_82883.png Nulls_Make_Things_Easier__2_82884_83531.png Nulls_Make_Things_Easier__2_83532_84587.png Nulls_Make_Things_Easier__2_84588_84683.png Nulls_Make_Things_Easier__2_84684_84947.png Nulls_Make_Things_Easier__2_84948_85103.png Nulls_Make_Things_Easier__2_85104_86567.png Nulls_Make_Things_Easier__2_86568_86615.png Nulls_Make_Things_Easier__2_86616_87431.png Nulls_Make_Things_Easier__2_87432_91247.png Nulls_Make_Things_Easier__2_91248_92423.png Nulls_Make_Things_Easier__2_92424_93071.png Nulls_Make_Things_Easier__2_93072_96237.png