This guide and we're going to discuss ranges in SQL. A range is something that you could treat like a real-world range, range of numbers, or anything like that. If you have a range going from 1 to 100 that means that that your range contains all of the numbers from 1 all the way through 100.

Now let's see how we can use this in SQL! If I'm going to say select all from guides.

SELECT * FROM guides

If I run this query you can see it pulls up all of the guides.

Now one thing that I want to do is I want to start grabbing guides that are specific to a certain range. So, in other words, they're contained within a range. The way that I can do that is I can say select all from guides but then I can add a new clause in. It can say where guides_revenue between 1000 and 5000.

Now if I run this code this is going to bring back results of only two items because we only have two items that are actually included inside of that range of 1000 and 5000.

If we updated this to say something like 1 in 5000.

It would then bring back the full range but I want to keep this at 1000 and 5000. So we're able to grab those values. Now, this is very helpful and there are many times where I will run a range query like this. Just quickly see if a value between one and another endpoint.

Now another thing that is just as helpful is to see which values don't fall inside of that category. Let's very quickly create another master query just to see all the data that's inside. So select all from guides. SELECT * FROM guides. If I run this we can see that we have values ranging from 500 all the way up to 1500. Let's say that we want to say we do not want the values between a certain range. The syntax for that is just to “add” a not.

So we're going to say where guides not between and then pass in a range. So this will work with what we have right here.

So it brings all the ones that are 500, 750 and it leaves out the bigger numbers. The other cool thing is I say not between 600 and 1200.

Now if I run this query you can see that this actually bringing back the exact things that we're looking for. So right here we have the record of 500 and then we have 1500. It did not include the 750 items and It didn't include the 1000 record because we said we're looking for everything that's not between these two numbers.

That's really where I find the power of this type of query is that it's not just bringing me the items that are below a certain range or above a certain range. It's actually just wrapping up a range and then giving me anything. In this case, it doesn't fit inside that box.

This is a very powerful tool and can imagine you're building financial reports for an organization. This is something you're going to be doing a lot because you're going to be asked to say “bring in me all the sales figures that have x, y, and z type of numbers contained inside of them”. Then you want to be able to quickly run a query that can pull those numbers or it can pull the numbers that don't contain those. It is very helpful. So you now should have a good idea of how to run a query with ranges in SQL.

Code

USE devcamp_sql_course_schema;

SELECT *
FROM guides;

SELECT *
FROM guides
WHERE guides_revenue BETWEEN 1000 AND 5000;

SELECT *
FROM guides
WHERE guides_revenue NOT BETWEEN 600 AND 1200;