Now let's look at a slightly different set of operators called as logical operators.

Just like in
every major programming language like C cetera, you have a bunch of logical operators.
There are some logical operators that are unique to SQL. We all know the and or not, etc.
There are some special ones like all and any between a exists in like and some. Right. We'll
go through some of them in this video. The others we'll learn when the right examples or
when we learn some additional techniques or additional methods, right? So now let's start
here. Now you might wonder what is the use of these logical operators? So let me give you a
real world example so that you can better appreciate what is happening right here. Again,
I'm in a t shirt search page for a retail company called Amazon here. If I want to filter all
those t shirts whose material is cotton, look at this. The material here is cotton. I've ticked
the cotton part. And if I want the brand to be flying machine, this is a very simple and useful
thing, right? If you think about it logically, I want the material to be cotton and I want the
brand to be flying machine. It's a logical. And so when you have multiple filters like this,
when you pick two filters, you will have to use logical operators. So let's go back to our
example of movie database and see some examples, right? So imagine if I want all the
movies whose rank score is greater than nine, which means they're highly rated and which
are released after the year 2000. So here I have two conditions. This is condition one, which
says that rank score should be greater than nine. And I have the second condition, which is
year greater than 2000. And both of them I am combining using the and operator, right? So
if you see this. So let's execute this and see, right? So if you take this and execute it in the
terminal that we have. So these are all the movies, these are all the movies that are released
after the year 2000 with a rank score of greater than nine, right? Because this is important
because if I want to only watch movies post 2000 and if I'm looking for good movies or
highly rated movies, I will have to use this logical operator. And between these two
conditions, this is condition one and this is condition two. Very simple thing. Very, very
simple example. Now let's look at just the way we have seen logical and imagine if I want,
imagine if I want a slightly different answer. I want to look for all the movies where the year
is not great, less than equal to 2000. So look at this. Look at this. This is a condition here,
right? Where not year less than equal to 2000, what does this mean? This means where the
year, okay, so this is a condition, right? Year less than equal to 2000 and we are placing a
knot in front of it, which means this is equivalent to, this is equivalent to, this is equivalent
to year greater than 2000. Whether you write year greater than 2000 or you write not year
less than 2000, this is a simple logical operation, right? So this will either evaluate to true or
false and the knot will invert it. This is like a logical, not that you see in all programming
languages, right? Similarly, if you want to use logical, or suppose if you want to watch
movies whose rank score is greater than nine, which means they're very highly rated
movies, or the movie is recent, right? If the year is greater than 2000, that means it's a
recent movie. So you want to either watch a recent movie or this is the logical R here, or you
want a highly rated movie from any year, right? So this is where you see these logical
operators of and or and not very, very simple. These are just like your logical or logical and,
and logical, not that you see in most programming languages like C, Python, Java, et cetera.
There are these two special logical operators called all and any. We will discuss these two
things when we learn of a concept called sub queries. We will learn. That is one of the future
videos in this chapter, right? So without learning about sub queries, learning about any and
all will feel like theory. I don't want to teach just blunt theory. So it's better we learn these
two logical operators when we learn about sub queries, so that you understand the context
in which these logical operators are useful. So I'll hold these two for now. Okay, we'll look at
both these a little later. Now let's look at the rest of them, right? So now let's look at this
operator called between. Okay, let's see an example here. Here I have select name year rank
score from table. These are the columns I have and this is the table I have where my year.
Look at this. This is the condition where the year between 1999 and 2000. This is how you
use the logical operator between. So what this does, this is same as, this is same as if you
want to write it without using the between, you can write it as year greater than equal to
1999 and. Right. Year less than equal to 2000, right? This is called the inclusive, this is called
inclusive range because whatever values, I say these values are included. So whether it,
even if it is equal to 1999, you'll get it. Or if it's equal to 2000. You will get it. So this will list
all the movies which are released, right? Where the year is greater than equal to 1999 and
the year is less than equal to 2000. This is what it is equivalent to. That's why I've written it.
We call it inclusive range because both these values are also included in the results, right?
So this is how you use between. There is a subtle detail about between that I wanted to
explain you, right? So for between, right, you have these two values, right? You have these
two values. Look at this. You have these two values. This is called the low value. This is
called the low value and this is called the high value. Now, instead of using these two, if you
invert them, instead of writing this query, if I change it to select so and so columns from
movies where year between 2019 90 99, here, my low value is greater than my high value,
right? So this will not work. What this is equivalent to is this is equivalent to year greater
than equal to 2000 and year less than equal to 1999. That's impossible, right? So whenever
your low value is less than your high value, right? Whenever your low value is less than high
value, everything will work well. So your low value should always be less than equal to high
value. Otherwise. Else, for this query, if you execute this query, if you execute this query,
this SQL query, you will get an empty set. Because a year cannot be greater than 2000 and at
the same time being less than 1999. That's impossible. It's against the rules of simple
numbers, right? So you should always, whenever you're using the logical operator between,
you should have the first value or the low value to be less than equal to the high value.
Otherwise you'll get an empty set. This is an important detail that we should not forget.
Okay, so what all have we seen till now? Okay, so just to keep the context we have
understood and or not, we said we'll discuss them later. We have also discussed between
now we have exists in like and sum. So let's go through each of them. Okay? These are all
logical operators, very simple operators. Now let's see how in is useful. Okay, suppose if I
want to print the director id and the genre of the movie from this table called directors
underscore genres. Where I want my genre to be either comedy or horror, let's say. Okay, so
if I want my genre, let me just edit this. Suppose if I want my genre to be either, if I want my
genre to be either comedy or horror, what do I do? One way to write it is select director id.
Comma, genre. From directors underscore genres where genre equals to comedy, or genre
equals to horror. This is one way of writing it. This is one way of writing it. But when you
write it like this, you're repeating this. Look at this, look at this. When you write it like this,
you're repeating the genre equals to here. The same thing. You're writing it here. If you
want it between three genres, right? Genre equals to comedy, or genre equals to horror. Or
let's say genre equals to. If you want equals to, let's say drama, right? So what is happening
in this case is you're repeating this. Genre equals to this text. You're repeating it every time.
Is there a shorter form of doing this? That's where you have this keyword called in. So let's
see. Let's see how it works. It works like this. You are saying select director Id, comma,
genre from the table name. This is my table name where. If my genre belongs to, or if my
genre is in this set. See, look at this. This set I'm representing using two braces or
parentheses. Within the parentheses, I'm mentioning all the genres that I want. So if the
genre is equal to comedy, or if the genre is equal to horror, right? This will return the
director Id and genre corresponding director Ids and genres. So this is genre equals to
comedy, or genre equals to horror. Is same as saying if my genre in is the keyword here. If it
is in this set, if my genre is in this set, then make this condition equals to true and output
that corresponding row, right? That's how you use the comparison operator or the logical
operator in. Right? Now let's look at the other ones. There is something called as like. Very
interesting. Actually. I really like the like comparison operator. So let me explain that again.
I'll share this whole text file with you. You can open it, and you can run all of these
commands in your terminal, right? You can comfortably run. So for example, if you see this,
you can simply copy paste this, copy it. Control C, go to your mysql terminal. Control B. So
wherever this is the director ID, horror, comedy, wherever you have horror or comedy,
you'll get the output. It's that simple, right? Okay, so you can run all of these commands will
work. Now let's look at this logical operator called like. Let's see what like does. Okay, so,
like, does something called as text matching. So let me explain this for you. That's easier.
Suppose if I want the name, year, and rank score of movies where the name starts with Tis.
After this, there could be anything. Okay, if I want the names of all the movies, the
information of name, year and rank score from my movies table where the movie name has
Tis, followed by anything else. Okay, so let's see this. Let's see this. Okay, let's see this. Copy
this and paste it, and let's see what happens. Okay, if you see. If you see the result that you
get, the result set that you get, everything starts with Tis. And Tas can be followed by
anything. Here, Tis is followed by oy. Here tis is followed by something else. Here tis. But the
only thing here common is all of these names start with capital Tis. So the operator like,
right? What it does is you can input something like this. Let me explain what this is. This
means that the first character has to be t, the second character has to be I, the third
character has to be s. The percentage here, the percentage here is called as a wild card
character. This percentage symbol is called as a wild card character. And this percentage
implies zero or more characters. So what it means is, as long as the name of the movie starts
with Tis, and if Tis is followed by anything, it could be empty. If Tis is followed by anything,
even empty stuff. So this condition will match where, if the name is just Tis, it will match. If
Tasu, it will match tasam, it will match all of them. So percentage basically means this is
followed by Tis is followed by zero or more characters. So this logical condition will match
tas. Because there are zero characters after Tas, it will match Tasu. Because there is one
character after it will also match tasam. Because there are two characters after Tas, the only
requirement is the first three characters are tas. This percentage is called a wildcard
character. This is used in lots of languages, right? This is used in like, if you know shell
scripts, this is used in similar type of wildcards are used in a command called grep in Linux.
Most programming languages, like Python, java, et cetera, have ways to match strings like
this. Using these are also called as regular expressions. Okay, so your grep or regular
expressions again, we will learn about regular expressions when we learn about automata
theory, right? So grep, we learn in operating systems. Okay, so these are concepts that are
across multiple subjects in computer science, right? If you're a computer science student,
you would have come across something called grep in operating systems, or if you have
learned automata theory, regular expressions. If you don't know these, it's okay. Don't
worry about them. It's perfectly okay. For those of you who know, you'll be able to
appreciate these relationships. If you don't know, that's perfectly okay. Or if you're not a
computer science student, that's also perfectly okay. Okay, so this is done. So let's go and
understand the next one. Okay. Suppose if I want all the actor names, so we'll see some
variations of like. Right? If I want the first name and last name from my actors table where
the first name is like, say, look at this. My first name like this. Look at this condition. It says
my first name like percentage es. What do we know? Percentage to be? Percentage basically
means zero or more characters. Zero or more characters. That's what percentage means. So
what this is saying here is. As long as my first name ends in Es, as long as my first name,
that's what the last two characters are fixed here. Here the first three characters are fixed.
Here the last two characters are fixed. So this will match if my first name is Es, or if my first
name is Z. Es, or if my first name is f, a, c, e, s, it'll match all of them. Because here I have zero
characters before Es, I have nothing. Here I have one character before. Yes. Here I have
three characters before. Yes. Since percentage represents zero or more characters, right?
It'll match all of them. So let's actually execute this and see, right? So let's execute this and
see. Okay. Go to a terminal, clear the screen. That's it. If you look at this, look at all these
folks. All of their first names are ending in Es. Look at everyone. All of their first names are
ending in es, okay? Yes. All of them are ending in es. That's what we asked, isn't it? Isn't that
what we asked? That's exactly what we asked, right? Is a very useful thing to match strings.
It's very useful to match strings. And these special characters like percentage, are very, very
powerful. So since we have seen percentage as a wildcard character that matches zero or
more characters, right? Is there some wildcard character that will exactly match one
character? Exactly one character. So there is a wildcard character called underscore, right?
This underscore symbol. This implies exactly one character. This is also a wildcard
character. Just like your percentage, which is used in like a lot. Let's see an example where
this is useful. Imagine if I want to find the first name and last name from actors, where the
first name should consists of. Should starts with a, followed by G, followed by n, followed by
any character but a single character. Just exactly one character followed by S. What is my
condition, my condition is that my first name should be like this. So let's look at some words
that will match this type of string, right? Of course, Agnes matches because there is exactly
one character. Similarly, Agnus will match because there is exactly one character. But AGNs
will not match because there are zero characters between N and s. Even though A and a will
match G and g matches, n and n matches, SNs matches. There are zero characters here, so it
will not match. Similarly, Agnus will not match because there are two characters, right? So
underscore here is also a wild card character, just like percentage. While percentage is a
wild card character which implies zero or more characters, underscore basically implies
exactly one character, right? Now comes an interesting twist. What if. Okay, let me take a
slightly interesting example. Imagine if I have a table t, right? This is my table. My table has,
let's say, name and the percentage of marks, the percentage of marks that each of the
students has secured, right? So let's say I have a name, n one, and let's assume the
percentages are stored actually not as just a number, but as a string. So they've written 59
percentage. They've actually used the percentage symbol here. Let's say similarly, there is
another n two equals to 63 percentage. Similarly, there is a student whose name is n three.
I'm just taking an example here with, let's say 96 percentage. Now comes the tricky part.
Now, if I want to select, okay, if I want to say select star from right, select all the columns
from my table t where, let's say where percentage equals to, where percentage equals to.
Let's say, if I say 96 percentage, if, when I say this, there is a problem. The problem is
because percentage is a wild card character. Let's not forget that is a wild card character
which represents zero or more characters, okay? So SQL will think of this as a wildcard. SQL
does not think of it as a symbol percentage. There is a difference between wildcard
character percentage and the symbol percentage. We want it to be thought of as a character
or symbol percentage. That's what we wanted to think of it. If you look at your keyboard, on
your keyboard above the five numeric pad, above the five number, you have the percentage
symbol. That's what we wanted to represent, not the wild card character. In such a case,
what do we do? So, instead of writing it like this, because this is interpreted as a wild card
character, the default interpretation is a wild card character. The appropriate way to write
this, the appropriate way to write this would be where percentage, everything stays the
same select star from t where percentage equals to 96. We can use something called, as an
escape character called Backslash. Look at this. This is how it should look like. This is
exactly how it should look like. It should be. Backslash percentage. Now, whenever you say
backslash percentage, this will be interpreted as the symbol percentage, not as the wildcard
character percentage. So this backslash is often referred to as the escape character. It is
called the escape character. Every language, every programming language has escape
characters. This is the escape character in SQL, right? I hope this is clear. So if you don't
want a percentage, if you think a percentage is there in one of your data, in one of your
columns, and if you don't want percentage to be treated as a wild card character, but as a
symbol, then you should use the escape character, which is a backslash. Actually this,
because we have two, remember we have two wildcard characters, right? We have
percentage and underscore. Underscore becomes very important. Let me tell you an
example there, right? Suppose I have a table t. I'll give you an example for that. Also, let's
assume I have the email addresses, okay? I have person with n one whose email address is n
one, underscore ABC at Gmail, let's say, right? Let's assume this is a person's email address.
Similarly, there is a user n two whose this thing is underscore. X Y z. Underscore ABC at
Gmail. Now here, underscore, remember this is very, very important. Here we are using
underscore as a symbol. Underscore as a symbol. Underscore, not as a wild card character.
Not as a wild card character. So here, if you want to search for somebody based on their
email address, I can say select star from my table t where email equals to. If I just say email
equals to n one, underscore ABC at Gmail. Let's say I said this. Let's assume I said this. Now,
automatically, automatically, this underscore is treated as the wild card character. The wild
card character, right? Where email, like, let's say I say instead of equal to, if I said like, not
equal to, but I said like is the logical operator that we are using here, right? Like is the
operator that we are using. Sorry, it shouldn't be equal to. It should be like, right? Like n one
underscore ABC. Now this underscore is now being treated as a wildcard character, but we
want to treat it like a symbol. In such a case, what should we do? In such a case, we should
write like where email, like, right? N one backslash. Underscore ABC@gmail.com so the
moment you use this escape character, you are saying whatever comes next should not be
treated as a special character. It should be treated as a symbol. Right. Very simple. So even
here, we shouldn't say percentage equals to, we should say percentage like because this is a
string matching. Right. That we want to do with wildcard characters. Right. That's a small
correction that I just wanted to make here. So overall, in a nutshell, overall, if you think
about it, what we have here is that we can use escape characters to treat these special
symbols like percentage or underscore as symbols and not as wildcard characters. Right. So
we have gone through almost all the major logical operators that we started off with, except
any and all. So we have gone through and or not these two will go through when we learn
about sub queries little later. We went through between exists in like and sum. So we'll also
come across some little later. Actually, we haven't gone through some here. Right. So very,
very simple examples, nothing complex, very useful logical operators in practice.