Query Filtering Continued BETWEEN, IN and NULL

A free video tutorial from Imtiaz Ahmad
Senior Software Engineer & Trainer @ Job Ready Programmer
Rating: 4.6 out of 5Instructor rating
12 courses
398,147 students
Query Filtering Continued BETWEEN, IN and NULL

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:27:26 of on-demand video • Updated November 2022

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 to the solution to the to the problem I assigned you in the previous lesson, and then we'll learn about the in clause as well as the between clause. These are crucial to the SQL syntax. And once you have these, it'll give you a lot more horsepower with what you can do with 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. Okay, now this is the output of the employee table, right? When I select this first part, I hit run. I get the data in the employee table and there are 14 rows. Now we know the E name column. This contains the 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 20 and 30 OC. 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 20 and 30 OC So I could do this by doing where let me first eliminate this. I don't really need this anymore. I figured out I figured out from the department table that Dallas and Chicago have department numbers 20 and 30. So I could do select star from EMP where department number is equal to 20. 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 OC. Where depth number is equal to 20 or a depth number is equal to 30. And if I highlight this whole thing and hit 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. Okay, so I filtered the criteria. There's 11 rows that fall within our criteria. And remember, the assignment was that I want to see the name as well as the higher 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 star there, the asterisk. We don't need all of the columns. We need some specific columns. And those columns are E name as well as higher date. And I'm just reminding you that you can have lowercase here as well because SQL is syntax insensitive unless you are comparing string literals, write literal characters where you're doing equal, open and close 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. All 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. Rather than having this condition where a department number is equal to 20 or department number is 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 where department number in. Right. It could be lowercase or capital. Doesn't really matter. I like to use capital. So in department 20 as well as 30 like that. So in these parentheses, I'm stating the department numbers that I expect in department number column. So now if I run this, this will return me again the same results 11 rows. 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 number column. So this in clause is actually very handy. It reduces the amount of SQL 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 Ford. 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 SQL query would look something like this, I'd have to do where in name is equal to Ford or E name is equal to. Smith or e name is equal to Allen or in-game is equal to. Ward or e name is equal to Martin. And if I had run, only these five employees will be will be shown here, right? Five rows forward. SMITH Ellen 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 where E name is equal to Ford or E name equal 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 and we can use the same. We only need these two columns. Right. So let's copy paste those here. You name hired it from AMP. Where? In name in. And then I can give these particular employees. Ford, Smith, Allan 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. Okay, so both of these queries do essentially the same thing is just that this is much more succinct and I like to use the N clause a lot. So this reads more like English. We're saying select E name and higher date columns from the employee table where the column E name contains one of these, right where the E name is. In one of these possibilities, either a Ford could be there, either a Smith could be there, either either an Allen or Ward or Martin. So this rare 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 e name contains textual data. These are English words. These are names. We need to wrap the possibilities in quotation marks Ford Smith, Allan Ward, Martin. All of these are possibilities that could exist inside of the EA name column. So EA 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 E 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 SQL. But textual data like this requires quotations, and this has to do with the data types that are 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 e name 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 SQL, we can't use double quotes, it needs to be single quotes. So that's just another thing to keep in mind. Now sometimes you want to filter for not in 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 start from AMP. I'm just going to get this first line to run the E name and the higher data 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. Okay, let's say that I wanted to get the E name and hired H from AMP where the E name is not. It is not one of these values. Okay. 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 OC, just like that. When we do not end, then it would give me all of those records that contain the data. Anything other than these values in the EA name column. Right now we are getting all 14 rows. So let's run this now using the not in and hit run. And notice that we get all of the names other than the names that are in here. I don't see any Ford. I don't see Smith. I don't see Allen Ward or Martin in the results yet. Those records have been rejected and it's only giving us the nine rows with different names different than these. So keep this not in in your arsenal, because it's actually pretty useful. Now the next keyword in the SQL language that we'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 1981. Well, let's say between this date right here and this date right here. Okay. 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 and the higher date. So select star from AMP where higher date between and I'm deviating from the capital syntax again just so that I keep reminding you that this is case insensitive. So between this date right here. And we need to wrap it in quotes. And. This date right here. 1982. December nine, 1982. Okay. And this will give me all of the records, all of the employees that had a higher date between this range. So highlight this particular query and had run. And notice that we get nine rows. So all of these guys were hired between 1981 and 1982, you know, 1209, 1982. Now, this between clause 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 why 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, 1002 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 1002 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 editor select star from imp where the cell between. 1002 thousand, 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 1002 thousand. Now, just out of curiosity, you might be wondering whether someone with a salary of 1000 would that be included in this between clause or there were someone with a salary of 2000? Would that be included in this between clause? Yes, it would. All right. 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 AMP, just this first line right here so that we can see all of the data. And let's see if there's anyone making $1,000 or $2,000 and there is no one making 1000 specifically or 2000 specifically. So let's take an example of let's say I'm going to I'm going to use a smaller number such as 950. So between 950 and 1600. 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 1600. 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 where cell not. Where cell is not between nine, nine, 50 and 1600. So it will. It will reject all of those records, all of those employees that make between 950 and 1600. Okay. 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 950 anymore. We don't see 1600 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 1600. 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. 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. And this in this basically means that this is an empty cell. There is no data in here and in Apex that's just represented by Dash. Other tools, other tools such as SQL Developer or Toad. If you're familiar with those, they're going to say the word null and you LL or depending on the version, just might be completely empty. But essentially what this means is that there is no data. There's absolutely no data for this particular cell, for the record. So let's do a quick select start from AMP to see all of the data and notice 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 filled cell. Right? There is data in here. There's a zero. But these cells, right. These are dashes. 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. So the way that works is I can say select star from employee amp where the commission column com is null like that and just run the statement and it'll return only those employees that have an empty cell for their commission. 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 could be a lowercase note as well. So highlight this query and hit run. And this will give us all of those records that do, in fact, have some data in the commission column. So is NULL 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. In this case, the given column is commission now going back to select star from AMP. 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 star from AMP where MJR 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 100 but less than 5000. The only exception is that their salary cannot cannot be equal to 3000. 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 over the solution in the next lesson.