Data Types and Filtering Results in SQL

When we think about columns, we think about a kind of data. And it's very important to understand that the columns are assigned a data type by the person who creates the database. For example, we saw back here, I had mentioned four users.

For example, time information, that's where we see the little clock. We see numbers for user IDs. We see character data for the other stuff.

We see the length of characters. What we want to focus on right now is simply numbers versus text. But the person who creates that database determines how the data is stored.

What we care about is when we're writing our query, it will matter whether something is text versus numbers. So numerical data types that you might see over here could be listed as integers or numeric or decimal or serial numbers. But I like that DBeaver simply says, hey, instead of even knowing what these names are over here, we're just going to put a 1,2, 3 if it's a number.

And we're going to put A-to-Z if it's text. And we're going to put a clock if it's time. We'll get to time another day.

But right now, we're just going to say numbers or text. So you don't have to know the differences between these. But these are just names that you'll see for numeric data types.

Or these are potential names that you might see for text. But again, DBeaver just gives you those nice little icons. Not all apps will give you those nice little icons.

And so you might have to see that things like char is short for character data. Or you might see text, right? So all we care about is just text versus numbers. We call text strings.

So strings or text are character-type data. When we deal with those, we wrap them in single quotes. We quote things that are text.

We do not wrap numbers in quotes. Numbers are things that you might do mathematical operations on. You might say this plus this or this times this.

We do not wrap numbers in quotes. We only wrap text in quotes. In fact, if you do wrap a number in quotes, it is treated as text and not as a number.

So then things like math might not work depending on the flavor of SQL that you're in. So really, that's why we just care about text versus numbers. Numbers, you just write the number.

If it's text, you wrap it in quotes. Now, sometimes when you think something should be stored a certain way versus looking at how it's actually stored, you might guess the wrong data type. Don't guess data types.

Simply look. If you see a 1,2, 3, that's stored as a number. If you see A-to-Z, that's stored as text.

For example, a zip code. You might think a zip code should be stored as numbers. But when you look at it, what is it stored as? Text.

Now, that might go against what you think it should be stored as. But that actually might be correct. And in fact, in this case, it is correct.

But don't guess your data types. Look at what you're given. Because it doesn't matter what you think it should be.

It only matters what it was created as in the database. Now, why might zip codes be stored as text? Because zip codes have a dash. Is a dash a number? No.

So, if you try to store this as a number, what is it going to do? SQL would try to subtract this number from this number because you can't store a dash in a numeric value. Also, do we ever do math on zip codes? No. So, whenever you're just displaying text, it should really just be text.

Because of that dash that's in there. So, that's why it is actually correct to store a zip code as text, not numerical data. So, if we're ever querying, we're going to have to wrap this in quotes to search for a string versus searching for a number.

Also, numbers cannot start with a zero. But zip codes can start with a zero. And we need to keep that zero.

So, it's just another reason why we don't store them as numbers. Now, you don't have to know these things. You just have to look at what you're given.

That's the main takeaway. Don't guess your data types. You are told your data types.

Numbers, text—just look at what you're told. And then that tells you whether you need to put quotes or not. You put quotes around anything that's text.

You don't put quotes on numbers. And we're going to use this because we're going to search for things. We're going to filter down our results.

You might want to see who spent more than $500. So, you're looking for a number, right? You might want to see people from Florida, right? So, FL would be text, right? Just searching for the people from Florida. So, we don't want to see all the rows.

We want to see some of the rows. So, out of all the users or all the products, we might want to only see some of them. So, for example, I can use the WHERE clause.

WHERE will only give you rows where something is true. So, don't show me all the rows, only some of those rows. Only show me products where the price column contains a 10.

So, out of all the rows, it'll only show you rows where the price column contains a 10. 10 is a number, so we don't put quotes around that. Now, when we're doing comparisons here, right? Here, we're using equals.

So, we can say we're looking for something that exactly equals something. But you can also use greater than, less than, greater than or equal to, or less than or equal to. You can also look for something that's not equal to something called inequality.

Inequality can be written either with less than, greater than—which I think is a little weird, personally—or exclamation equals. The exclamation means not equal to something. This is personally the way that I like to do it because I've seen other programming languages do that.

But that is my personal preference. But either way works. And this would be searching for something that's not equal to a certain value.

So, for example, if you say that it's less than 10, these would be your cheap products, but you would not see 10 in that result set. If you say less than or equal to 10, then it would be 10 and everything less. 10 would be included in that result set.

You can also give a range. You could say, instead of an equal sign, you could say BETWEEN. You can say, show me products where the price is BETWEEN 10 AND 20.

It will be inclusive of the 10 and the 20. So 10,20, and everything in between.

You get that whole range of stuff. Notice the numbers are not in quotes because numbers don't get quotes. You could also do multiple things where you say OR.

OR is kind of like doing WHERE over and over again. You could say, show me all the products where the price is equal to this. Or the price is equal to this.

Or the price is equal to this. Now, keep in mind that you don't have to use the same comparison operator here. You could say, show me where the price is equal to this.

Or the price is less than a certain amount. Or greater than a certain amount. They don't all have to be equals. They can be different operators in each condition.

But you do always have to say where the information is. And what information are you looking at? For example, you could not say WHERE price is equal to $9.99 or $15.99 or $13.99. You as a smart human can figure that out. But SQL is not so smart.

Every time you do an OR, you must specify where you're looking, how you're comparing it, and what data you are comparing. That must always be there for each one.

It's kind of like doing WHERE multiple times. But you can only get one WHERE. Anything after that are things like OR, or AND, and those kinds of things.

Now, if you're listing this, this seems pretty redundant, doesn't it? Like, I'm repeating my same code a lot. If you're looking for multiple equalities, you can make it more efficient if you use IN to say, I'm looking for a price that's in this list of prices.

IN is always using equality. So you're saying, is the price equal to something in this list? Is it found in this list? So WHERE the price is in a list of—is it $9.99? Is it $15.99? Is it $13.99? If you find a price that's in this list, include that in my result set. If it's not a price in this list, do not include that in the result set.

This is a way more efficient way of doing it. But keep in mind that this is always equality. If you want to do some things that are equal, some things that are less than, some things that are greater than, you'd have to use the multiples with the OR, because each of these operators could be different.

IN is always equality.