Query Filtering Continued BETWEEN, IN and NULL

Imtiaz Ahmad
A free video tutorial from Imtiaz Ahmad
Senior Software Engineer & Trainer @ Job Ready Programmer
4.6 instructor rating • 12 courses • 285,272 students

Learn more from the full course

The Complete Oracle SQL Certification Course

Don't Just Learn the SQL Language, Become Job-Ready and Launch Your Career as a Certified Oracle SQL Developer!

16:23:53 of on-demand video • Updated June 2021

  • Become Job Ready to Start Contributing as a Database Developer Day 1
  • Program in the SQL Language to Solve a Variety of Database Problems
  • Code along with me to PRACTICE and IMPLEMENT everything you learn
  • Become a SQL Ninja and Understand How the Oracle Database Works
  • MASTER the Content Required to Pass the Oracle 1Z0-071 Database SQL Exam
  • Obtain the Skills that are Necessary to Land a Job as a SQL Developer
  • Have the Ability to Solve any SQL Problem
English [Auto] Hey there in this lesson, we'll start by going over the solution to the to the problem I assigned you in the previous lesson and then will learn about the in clause as well as the between clause. These are crucial to the school syntax. And once you have these, it'll give you a lot more horsepower with what you can do with a single table, SQL queries. And then we'll work on some problems so you can actually apply this knowledge that you're that you're learning. Now, let's start with the solution. In the previous lesson, I left you with an assignment and that was to write a query to return the names and hiring dates of those employees that work in Dallas or Chicago. OK, now this is the output of the employee table. When I select this first part I had run, I get the data in the employee table and there are 14 rows. Now we know the name column. This contains a name and we know that the hiring date is right here in this column. Hire date. But where do we get Dallas and Chicago from? I mentioned to you that you need to actually query the department table as well. So let's run the query for this and figure out what is Dallas or Chicago. So let's run that. This is the department table. And lo and behold, we see Dallas as well as Chicago and notice that they are assigned department numbers of twenty and thirty. OK, so to identify those employees that work in Dallas or Chicago, I'd have to filter in the employee table those employees that work in these departments. Twenty and thirty. OK, so I could do this by doing where? Let me first eliminate this. I don't really need this anymore. I figure it out. I figure it out from the department table that Dallas and Chicago have department numbers, twenty and thirty. So I could do select staff from EMP where department number is equal to twenty. All right. Or with the knowledge that you've gained so far, you can use the or keyword and filter for the department number is equal to 30. OK. Where that number is equal to 20 or that number is equal to 30, and if I highlight this whole thing and had run, this should give me all of the employees that work in either Dallas or Chicago. And lo and behold, all of these Blake Jones, Scott and so on, they belong to either the department number 30 or department number 20. OK, so I filtered the criteria. There's 11 rolls that fall within our criteria. And remember, the assignment was that I want to see the name as well as the hire date. So I don't need to see the whole record. I don't want to see all of these columns. So eliminate the the store there, the asterisk. We don't need all of the columns. We need some specific columns. And those columns are in name as well as higher date. OK, and I'm just reminding you that you can have lower case here as well because is syntax insensitive unless you are comparing string literal literal characters where you're doing equal, open and closed quotations. Other than that equal is case insensitive. So I highlight this thing and then hit run and this will only give me those employee names and their hiring dates. Right. That work in Dallas or Chicago. All right. So hopefully you were able to solve this problem on your own. Now, there is a better way to solve this same problem, OK? Rather than having this condition where department number is equal to 20 or department numbers equal to 30, I can use something called the in clause. And what that allows us to do is not have as many conditions like this where we were repeating the column name twice for a different condition here. I could do with department number. In all, it could be lowercase or capital. Doesn't really matter. I like to use capital so in department twenty as well as thirty like that. So in these parentheses, I'm stating the department numbers that I expect in the part number column. OK, so now if I run this, this will return me again, the same results 11 wrote. So this is accurate. I could either use the or clause or I could just use this in clause and put in all of the departments that I'm interested in. In this case, Dallas and Chicago are 20 and 30 respectively. So these are the only two data points that I want filtered for the department no column. So this in clause is actually very handy. It reduces the amount of skill that we need. Let's say if we wanted to identify the records for specifically some particular employees, let's say we wanted Martin Ward, Allen Smith, and for these specific these five employees, Martin Ward, Allen Smith and Ford, we wanted the records for these particular employees. If I was to just use or the secretary would look something like this, I'd have to do where each name is equal to Ford or you name is equal to. Smith, or E name is equal to Allen or you name is equal to. Ward or name is equal to Martin, and if I had run, only these five employees will be will be shown here at five rows Ford, Smith, Allen, Ward, Martin. But notice that I'm having to repeat this condition over and over again with different values to compare against. Right. So it's checking for each name is equal to Ford or inimical to Smith or Allen or Ward or Martin. So it's much easier. It's much, much more succinct to just use the in clause. So to to to convert this query into an end clause, all I would have to do is just to select, um, and we can use the same. We only need these two columns. Right. So let's copy paste those IR. You name her it from EMP. Where you came in and then I can give these particular employees. Ford, Smith, Alan Ward and Martin. Like that, all right, this is all I would need, as opposed to repeating this name over and over again with this or so, this is much more succinct and easier to read. It reads more like English. So let's run this and notice that we'll get only those five records. OK, so both of these queries do essentially the same thing is just that this is much more succinct and I like to use the end clause a lot. So this reads more like English, we're saying select each name and date columns from the employee table where the column each name contains one of these, right, where the E name is in one of these possibilities, either a Ford could be there, either Smith could be there in either an Allen or Ward or Martin. So this where condition, of course, is going to be checked for every single record. All right. And if E name contains any one of these values, that is a valid record that will be returned. Now, since each name contains textual data, these are English words, these are names we need to wrap the possibilities in quotation marks, right, Lightford, Smith, Alan Ward, Martin. All of these are possibilities that could exist inside of the E name column. So each name contains textual data. So we need to wrap those these values in quotations. Right. Just as you would comparing a regular string, if we were to do where each name equals to the word Ford. Well, we would have to wrap that in quotations, wouldn't we? Same thing applies here just a few moments ago when I said department number in 20 and 30. In that case, recall that we didn't use quotations because department numbers are not textual data. They're actually numeric data. They're numbers. So we don't have to wrap quotations around numbers in school. But textual data like this requires quotations, and this has to do with the data types that's stored in a particular column. Remember, several lessons ago I was talking about data types. Every column has a particular data type. So in this Inamed column, this is textual data. If I try to insert a number into this, it's not going to work unless I'm using quotations. I have to treat it as textual data. And by the way, in school we can't use double quotes. It needs to be single quotes. OK, so that's just another thing to keep in mind. Now, sometimes you want to filter for not in, OK, where you want to reject those records that contain a certain set of values. So, for example, let's say let's do a select star from AMP. I'm just going to get this first line to run the E name and the date from AMP. This will give us all of the employees because I'm not including any conditions. Let's just run that first line here. And we have about 14 rows and we have all of these names. OK, let's say that I wanted to get the E name and age from AMP where the E name is not there. It is not one of these values. OK, so I want to reject these particular employees from showing up in the results at Fort Smith and so on, what would I do in that case? Well, believe it or not, it's as simple as doing not in. OK, just like that when we do not. And then it would give me all of those records that contain the data, anything other than these values in the name column. Right now we are getting all 14 rolls. So let's run this now using the not in and had run. And notice that we get all of the names other than the names that are in here. OK, I don't see any fraud. I don't see Smith, I don't see Alan Ward or Martin in the ResultSet, OK? Those records have been rejected and it's only given us the nine rows with different names different than these. OK, so keep this not in in your arsenal because it's actually pretty useful. Now, the next key word in the school language they're going to look at is called the between operator. It's often referred to as an operator and basically this is used to filter based on a on a on a range of data. It does exactly what it sounds like. If I want to get data based on a given range between Value A. and Value B, it would give me the data that matches the given range. So let me give you an example. Let's say that we want to get all of the employees. I want to return all the employees that were hired between nineteen eighty one. Well, let's say between this date right here and this date right here. OK, so I could do this, I could do select. I'm just going to do select star from AMP because I want to see all of the, all of the columns rather than just the E name in the heart. It so select star from AMP where higher date between and I'm deviating from the capital syntactic and just so that I keep reminding you that this is case insensitive. So between this state right here. And we need to wrap it in quotes. And. This state right here, nineteen eighty to December 9th, 1980, to. OK, and this will give me all of the records, all of the employees that had a higher rate between this range so high, like this particular query and had run. And notice that we get nine rows, OK, so all of these guys were hired between 1981 and 1980 to, you know, 12 09, 1980 to now this between Claw's or keyword could be used with numbers, textual data, as well as dates like we're using here in most database systems. You need to wrap dates in quotes. And Oracle is no exception. That's what we're doing it here. Now, let's see how the between operator works with numbers. I want to filter for those records that contain data given a particular range of numbers. Let's say, for example, the salaries column, if I wanted to get all of those employees that have a salary between, say, a thousand and two thousand. Then I would do this, I would do well, why don't you try that, I'll give you this as an assignment, write a query that returns those employees that have a salary between a thousand and two thousand. And when you're done, you can resume to watch my solution. All right, hopefully you took a shot at completing that I'm just going to make some space down here in the ED. So let's start from and where the cell between. One thousand and two thousand, OK? And I'm going to highlight this particular statement and hit run, and this will give me all of those employees that have a salary between one thousand and two thousand. OK, now, just out of curiosity, you might be wondering whether someone with a salary of a thousand, would that be included in this between Claw's and there were someone with a salary of two thousand, would that be included in this between Klos? Yes, it would. Those records would also qualify this between a range A and B, this is inclusive. All right. Just to confirm, let's select star from AMPE, just this first line right here so that we can see all of the data and let's see if there's anyone making a thousand dollars or two thousand dollars and there is no one making a thousand specifically or two thousand specifically. So let's take an example of let's say I'm going to do I'm going to use a smaller number such as 950. So between nine, 15 and 16 hundred. So. These records should also show up. So let's run that and we see that 950 is showing up as well as the end of the range. Sixteen hundred. All right. Now, just like before, we had in a certain set of values and then we had not in a certain set of values. This was basically negating this. We can use a similar type of thing we can do. We're still not. Rehearsal is not between nine, nine fifty and six hundred, so it will it will reject all of those records, all of those employees that make between nine, 50 and 60, 100. OK, that's what this not is going to do. It's negating this condition. So now highlight that and hit run and notice that we don't see the nine 50 anymore. We don't see sixteen hundred anymore, you know, and anything between this range, we don't see those values. I see something less than 950 and everything else here is greater than sixteen hundred. OK, so you can use this not using the between as well as in the in clause. Now, there is one more concept that we need to talk about before I give you an assignment for this lesson, and that is to identify empty cells. OK, so if you look at this table, notice that the name of the employee, all of these records have a name. All of these records have the particular job for that employee, the manager ID if if they have one, the higher data, the salary. But look look at the commission. There's just a dash here, OK? And this in this basically means that this is an empty cell. There's no data in here and in Apex that's just represented by Dasch. Other tools, other tools such as SQL, Developer or TODE. If you're familiar with those, they are going to say the word null and you elbel or depending on the version, just might be completely empty. But essentially what this means is that there's no data, there's absolutely no data for this particular cell for the record. So let's do a quick select star from EMP to see all of the data and noticed that some fields are populated. Turner Turner does not make any commission, but he has a value of zero. This is considered to be a field cell, right? There is data in here. There's a zero. But these cells. Right, these are dashes. They they contain absolutely no data. Think of these as just empty slots. They don't have any data in them. So to be able to identify empty slots, we use something called is null. OK, so the way that works is I can say select store from employee EMP where the commission column C o and is null like that. OK, and just run the statement and it'll return only those employees that have an empty cell for their commission. OK, all of these are just empty for the commission column. All right. So in a nutshell, the word null means empty. Now, I can do the same thing for guess what is not, I can use that not all right, and it can be a lower case and not as well. So I like this Korean hit run, and this will give us all of those records that do, in fact, have some data in the commission column. OK, so is no on a particular column identifies those records that are empty for that column and is not null returns. Those records that are not null for the given column or in this case the giving column is commission. Now going back to select Star from EMP, just highlight the first line. It gives us all the data. Again, notice that King does not have a manager ID. So this is actually empty. It's it's null. So we can identify those employees that don't have a manager by doing select staff from EMP where M.G. are is null. Let's give it a run. And of course it gives us that one record. Now we've come a long way, but I'm not going to let you go without an assignment. It's very important for you to get the practice. So write a query that returns those employees that don't make any commission and have a salary greater than eleven hundred, but less than five thousand. The only exception is that their salary cannot cannot be equal to three thousand. Now, if you have trouble with this, that's expected. This is a tricky problem. But give it your best shot and I'll go with a solution in the next lesson.