Query Filtering Continued BETWEEN, IN and NULL

A free video tutorial from Job Ready Programmer
Senior Software Engineers and Trainers
Rating: 4.6 out of 5Instructor rating
13 courses
480,240 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 December 2023

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 [CC]
Imtiaz: Hey there in this lesson we'll start by going over the solution 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 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 "ENAME" column, this contains the name and we know that the hiring date is right here in this column, "HIREDATE". 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, okay? 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. Okay? So I could do this by doing where. Let me first eliminate this. I don't really need this anymore. 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, okay? Where dep number is equal to 20 or dep 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 hire date. So I don't need to see the whole record, I don't wanna see all of these columns. So eliminate the star there, the asterisk, we don't need all of the columns, we need some specific columns. And those columns are "ename" as well as "hiredate", okay? 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, right? Literal characters where you're doing equal open and closed quotations. Other than that, SQL 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, okay? Rather than having this condition where 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're 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, okay? 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 ename is equal to Ford or ename is equal to Smith or ename is equal to Allen or ename is equal to Ward or ename is equal to Martin. And if I hit run, only these five employees will be shown here, right? 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 where ename is equal to Ford or ename equal to Smith or Allen or Ward or Martin. So it's much easier, it's much more succinct to just use the in clause. So to convert this query into an in 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. Ename, hiredate from emp, where ename in and then I can give these particular employees, Ford, Smith, Allen, Ward, and Martin like that, right? This is all I would need as opposed to repeating this ename 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, it's just that this is much more succinct, and I like to use the in clause a lot. So this reads more like English, we're saying select ename and hiredate columns from the employee table where the column ename contains one of these, right? Where the ename is in one of these possibilities, either a Ford could be there, either Smith could be there, 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 ename contains any one of these values that is a valid record that will be returned. Now since ename contains textual data, these are English words, these are names, we need to wrap the possibilities in quotation marks, right? Ford, Smith, Allen, Ward, Martin, all of these are possibilities that could exist inside of the ename column. So ename contains textual data, so we need to wrap these values in quotations, right? Just as you would comparing a regular string. If we were to do where ename 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'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 ename column, this is textual data. If I try to insert a number into this, it's not gonna 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, okay? So that's just another thing to keep in mind. Now, sometimes you want to filter for not in, okay? 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 emp. I'm just gonna get this first line to run the ename and the hire date from emp. 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 ename and hiredate from emp where the ename is not, right? Is not one of these values, okay? So I want to reject these particular employees from showing up in the results, Ford, Smith, and so on. What would I do in that case? Well believe it or not, it's as simple as doing not in, okay? Just like that. When we do not in then it would give me all of those records that contain the data, anything other than these values in the ename 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, okay? I don't see any Ford, I don't see Smith, I don't see Allen, Ward, or Martin in the results set, okay? Those records have been rejected and it's only giving us the nine rows with different names, different than these, okay? So keep this not in in your arsenal because it's actually pretty useful. Now the next key word in the SQL language that we're gonna 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 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 wanna 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 gonna do select star from emp because I wanna see all of the columns rather than just the ename and the hiredate. So select star from emp where hiredate 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 9th, 1982, okay? And this will give me all of the records, all of the employees that had a hire date between this range. So highlight this particular query and hit run, and notice that we get nine rows, okay? So all of these guys were hired between 1981 and 1982, 12/09/1982. Now this is 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 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 gonna make some space down here in the editor. Select star from emp where the sal between 1000 and 2000, okay? And I'm gonna highlight this particular statement and hit run. And this will give me all of those employees that have a salary between 1000 and 2000, okay? Now just out of curiosity, you might be wondering whether someone with a salary of a thousand would that be included in this between clause or is there 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 emp, 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 $2,000. 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 gonna 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 where you 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 is not between 950 and 1600, so 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, 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, okay? So you can use this not using the between as well as in the in clause. Now, there's 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, okay? 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 they have one, the hire date, the salary. But look, look at the commission, there's just a dash here, okay? And this basically means that this is an empty cell, there's no data in here, and in APEX that's just represented by dash. Other tools such as SQL Developer or Toad, if you're familiar with those, they're gonna say the word null, N-U-L-L, 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 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, okay? So the way that works is I can say select star from employee, emp, where the commission column, C-O-M-M, is null, like that, okay? And just run the statement and it'll return only those employees that have an empty cell for their commission, okay? 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 lowercase N-O-T 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, okay? 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, all right? In this case, the given 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 null. So we can identify those employees that don't have a manager by doing select star from emp where M-G-R 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 gonna 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 1100, 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.