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 • 245,191 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:22:14 of on-demand video • Updated November 2020

  • 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 problem I assigned you in the previous lesson and then will learn about the in clause as well as the between the clause. These are crucial to the sequel syntax and once you have these it will give you a lot more horsepower with what you can do with single table sequel 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 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 that 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. 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 20 and 30. OK. 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 temp 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. OK where DEP number is equal to 20 or that 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 play Joan Scott and so on. They belong to either the Department number 30 or department number 20. OK. So I filtered the criteria there's eleven rows that fall within our criteria. And remember the assignment was that I want to see their name as well as a higher date. So I don't need to see the whole record. I don't wanna 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 hired eight. Okay. And I'm just reminding you that you can have lowercase here as well because sequel is syntax insensitive unless you are comparing string literal straight literal characters where are you doing equal open and close quotations. I don't that's 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 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 n clause. And what that allows us to do is not have as many conditions like this where we're we're we're repeating the column name twice for a different condition here. I could do where department number in. All right. It could be lowercase or capital doesn't really matter. I like to use capital so in Department 20 as well as 30 so in these parentheses I'm stating the department numbers that I expect in department number column. OK. So now if I run this this will return me again the same results. Eleven 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 sequel 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 secret query would look something like this I'd have to do where you name is equal to Ford or e name is equal to Smith or a name is equal to Allen or e name is equal to Waad 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 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 it's checking for where each name is equal to Ford or a 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 N clause so to convert this query into an N 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 in a higher rate from AMP where you name in and then I can give these particular employees Ford Smith Allen 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. 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 N clause a lot so this reads more like English. We're saying select a name and hired eight columns from the employ table where the column E name contains one of the 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 a 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 for Smith. Allen 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 are actually numeric data their numbers. So you don't have to wrap quotations around numbers in sequel 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 e named column this is textual data. If I try to insert a number into this it's not going to work unless I'm using quotation I have to treat it as textual data. And by the way in sequel 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 EMP. I'm just gonna get this first line to run the E name and the higher data from and 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 hard data from EMP where the E name is not. 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 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 named column. Right now we are getting all 14 rows. 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 forward. I don't see Smith. I don't see Allan Ward or Martin in the results. 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 keyword in the sequel 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 it on an 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 can do select I'm just gonna 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 hard it so select star from EMP 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. Two. December 9th 1982 OK. And this will give me all of the records all of the employees that had a hired date between this range so highlight this particular query and hit run and notice that we get nine rows. OK so all of these guys were hired between 1981 and nineteen eighty two you know twelve o nine nineteen eighty two. Now this between Claus 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 1000 and 2000. 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 air. So let's start from and where the sell between 1000 and 2000. 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 1000 and 2000. 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 Claus or someone with a salary of two thousand. Would that be included in this between class. 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 and purchase 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 950 and sixteen hundred 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 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 we're cell not so is not between nine nine fifty and sixteen hundred. So it will it will reject all of those records all of those employees that make between 950 and sixteen hundred. OK. That's what this knot 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 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 that particular job for that employee. The manager idea. 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 is no data in here and in Apex that's just represented by Dash. Other tools other tools such as sequel developer or toad if you're familiar with those they're going to say the word no. And you L.L. or depending on the version it 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 star 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 failed sell right. There is data in here. There is 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 save select staff from employee emp where the commission column C O M M is no 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 call. All right. So in a nutshell the word no means empty. 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 how did 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. OK. So is no on a particular column identifies those records that are empty for that column and is not no returns those records that are not know for the given column. Right. In this case the given column is commission now going back to select start from and just highlight the first line it gives us all the data again notice that King does not have a manager I.D.. So this is actually empty. It's it's no. So we can identify those employees that don't have a manager by doing select star from AMP where M G R is not. 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 can not 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.