So greater than less than not equal to and equal to our technically what we call comparison operators.
They compare two values, right?
Is this less than this other thing?
Is this not equal to the other thing?
Is this equal greater and so on.
But now we're actually going to take a look at an example of a logical operator.
Now, if you have any experience with other programming languages, you probably are familiar with logical
and in logical or logical not.
But if you're not, don't worry at all.
It's simple enough to pick up.
And it is one of the most useful pieces of my SQL syntax when writing complicated queries.
So we'll take it step by step from the beginning.
Imagine that I wanted to write a query to select all the books written by Dev Dave Eggers that were
published after the year 2010.
Well, there's two basic pieces of information here that we're filtering by.
We're selecting by author where author is equal to Dave Eggers or just Eggers, maybe for last name.
And then secondarily, we're also finding books released after 2010 so we can write those two queries
individually, right?
We can select the books written by Dave Eggers, select star from books where author last name is equal
to Eggers, and we can select the books published after the year 2010.
Select star from books we released here is greater than 2010.
And if I ran this here we can see there's three Dave Eggers books.
And then if I ran this one here to find the books released after 2010, there's five books there.
And what I want is the overlap.
I want the books that were released after 2010 and have author last name of Eggers, which is these
two books here, A Hologram for the King and the Circle.
And that's what this slide shows here.
I want author l name equal to Eggers and released year greater than 2010.
Both of those need to be true.
I want those books.
And the way that we do that is by using and capital A and doesn't have to be capitalized, but A and
will join together two or more different pieces of logic.
So here's our first condition.
Author Last name is equal to Eggers, and at the same time release year is greater than 2010.
So in order for a book to be included in the results of this query to be selected, this has to come
out to be true and this has to come out to be true.
If only one of them is true, then that book doesn't count.
It's not included.
Right.
If the author last name is Eggers, but release here is 2005, then this thing is not true.
The whole statement is untrue.
So why don't we try writing this?
Let's.
Let's do it in a separate file.
So instead of select star, let's do title author, last name and released here.
From books.
And then I'll do my first piece where let's do it based off of released.
Your first or released year is equal, or rather is greater than 2010.
And then afterwards I'm going to say and.
I also want to know where author your last name is exactly equal to Eggers.
So let's run this query.
And there we go.
Only two rows that matched.
Both of them have author last name of eggers and release year is greater than 2010.
So that's our first example.
Here's a more complicated example.
We can chain on as many as we want.
I can keep going and say, Arthur, last name is Eggers and release year is greater than 2010.
And also the title is like this string.
It includes novel in the middle.
So if I do that, let me just build up this query over here.
So do the same thing as before, But we'll add on another and I can keep going as many times as I want.
Now, all three of these pieces, these three different comparisons all have to be true.
Released Release year Greater than 2010.
The last name is exactly Eggers and the title is like novel, meaning it contains not novel somewhere
in there because we've got the percent signs on either side.
So all three of those have to be true.
And there's only one match a hologram for the King.
It contains novel author Last name Eggers released here is greater than 2010.
2012 is greater than 2010.
So this is incredibly useful.
We can combine all of the different tools to different comparisons that we've learned so far and make
more complicated queries.
Now let's let's actually dial it back for a moment and talk about how and works.
So there's a left and a right side.
And in order for and to evaluate to true this whole thing to be true, the left side has to be true
and the right side has to be true.
Now that makes sense just based off of how we think about this and what we mean generally when we say
the word.
And if I was just reading this release here is greater than 2010 and author last name is Eggers.
We know in order for and to be true, both of those have to be true, right?
If we had a book that was released in 2010, but author last name was Smith, well, the left side would
be true.
Well, actually, it wouldn't be true because I said greater than 2010.
So let's say it was 2011.
Greater than 2010.
So that's true.
But then the right side would be false.
And that means the entire thing is false.
So I can actually prove that this is how it works.
If we just do something simple like this, select, remember how we can do this.
Three is greater than one.
If it is true, we get one as the answer.
And if I did, three is greater than four, we get zero for false.
So I can do this select.
Let's do something we know is going to be true.
One is greater than zero.
That is true.
We get one and let's say eight is equal to eight.
Stupid, but true.
So the whole thing evaluates to true.
But if I change this a little bit.
So now the right side is false.
The whole thing evaluates to false.
Remember, zero means false.
So this is true.
This is false.
When we have and in the middle that means the whole thing is false.
The only way for end to be true is when both sides are true.
So let's try one more example.
Why don't we do something sort of silly?
Let's find the books that have a title that are that is at least, let's say, 15 characters, so long
titles and also a large number of pages.
So we'll start with the first bit.
This is a little trickier than what we've done so far.
How do I know how many characters are in the title and how do I compare that to 15?
Well, I can do something like this.
We're going to use a string function, remember char length.
So we'll do something like select char length.
Of the title from books, but that's just going to print to me or select the value of the character
length, the number of characters in the title.
Maybe not that useful.
What I want to do is select the title.
And then from books where the character length of the title.
Let's say it's greater than 30.
So now I'm getting the titles of the books that have long titles.
And there we are.
So these are long titles.
So we have nothing to do with end at the moment.
But now I want to add in this condition, this additional stipulation that says not only do I want the
ones that have long titles, but they also have to be long books.
So let's just do it on a separate line.
And pages, I don't know, pages is greater than 500.
Do we have any?
I think there's at least one.
There's two of them.
Why don't we also display pages or retrieve pages so we can verify?
Okay, So we've got a title over here, The Amazing Adventures of Kavalier and Clay that has to be greater
than 30 characters.
And again, we're not just directly comparing title, but we're comparing the character length of the
title.
And then pages has to be greater than 500 at the same time.
So there's other books that are more than 500 pages, but they don't have long titles.
And there's other long titles like what we talk About when we talk about love that don't have 500 plus
pages.
So we're getting the intersection, only the rows that have this true and this true.
So that's logical.
And we can use it to create really complex queries and keep adding on if we wanted to, you know, different
conditions and make things more and more specific.