Jose: Hi guys, I hope you're doing well.
In this video, we're going to learn about the WHERE clause
in SQL that we can use to filter results
from a SELECT statement.
So the WHERE clause goes after SELECT * FROM users
or SELECT your columns from users.
So you can do something like SELECT * FROM users,
where first name is John, or where age is greater than 18,
Or where salaries less than 35,000
or where surname is not equal to Smith.
We're going to look at these operators in a bit more detail.
But I just wanted to show you where the clause goes
which is after the table name,
so you select then you put your columns from your table
and at the end, goes your WHERE clause.
So like you would say in English.
For our comparison operators,
we've got lower than, we've got greater than,
then less than equal to, or greater than or equal to.
Notice that the order of the operators
with the crocodile clip first
and the equal sign later does matter,
so make sure to keep it that way.
A single equal sign means exactly equal to
so this is different from Python
where we use two equal signs
to mean exactly equal to.
In SQL, we only use one.
That's because normally in SQL,
we're not actually assigning anything.
So there's no point to having the double equal sign in here.
The exclamation mark equal means not equal to
just like in Python, we can use AND and OR
to chain comparisons logically.
So here for example, the first example
where years experience is greater than 10.
And then AND being the key word
in SQL salary less than 35,000.
So this would give you the rows
that have a number greater than 10
for the years on the scored experience column,
and the number less than 35,000 for the salary column.
Or you can do where age less than or equal to 18
or age greater than or equal to 65.
So this would give you people
that are not in their working age, you could say.
You can use more than one chaining.
So you can have two ORs like in here
where age less than or equal to 18,
or age greater than or equal to 65, or salary equals zero.
This may tell you some information
about people who are currently not working maybe.
So let's take a look at this statement
in a bit more detail with these three OR statements,
these would match at least one of these must be true
for the the WHERE clause to match.
The person is 18 or under, or the person is 65
or over, or the person salary is zero.
So, somebody who is 50 years old
and with a salary of zero would match,
they are not 18 or under or 65 or over,
but their salary is zero.
So, therefore, the clause matches.
Similarly, someone who is 17 with a salary of 10,000
would match as well because their age
is matching the first condition in this WHERE clause.
You can group comparisons with brackets.
For example, you've got here
three examples of using brackets.
You've got where age less than or equal to 18,
or age greater than or equal to 65
and salary greater than zero.
So here, in this first comparison,
and this could be read as
you have to be less than or equal to 18,
Or you have to be 65 or more
and your salary be zero or greater than zero.
However, this is not really what that means.
So using brackets is going to make the intent much clearer.
Here we should do and that's the age is less than
or equal to 18, or the age greater than 65.
This will evaluate first
and it will either be true or false depending on their age,
And the salary must be greater than zero.
So they must be less than 18 or greater than 65.
And their salary must be greater than zero
for this to match.
But in this example down here, they can be 18 or less,
or they can be 65 or more with a salary greater than zero.
So I know that these are a bit difficult
to explain with words, I'm just saying the same thing
with different intonation.
But hopefully the brackets
help you understand
which one is going to evaluate first.
So how about think about how these three could be different
depending on what data you feed them.
But do remember
that they do mean completely different things.
So we've got some more examples
and information in the e-book.
Remember, you've got access to that.
And you can always access that at any point
and the URL is pysql.tecladocode.com.
All right, I'm here in DB Browser for SQLite.
And we're going to start filtering our data
that comes back from the SELECT statement.
What I've got here is SELECT * FROM users.
This select all the data from the users table
and shows it to us.
And you can see I've got these five rows
that we had earlier on in a previous video as well.
Now after SELECT * FROM users, we can type
a WHERE clause that filters the data coming back.
So it will remove some results that don't match
the conditions that we set.
For example, we can get data only where
the surname is equal to Smith.
Here, surname equal Smith is the condition.
And we will only get data that satisfies this condition.
Notice that it's very important the WHERE clause
goes after SELECT * FROM users.
So this goes after that.
If we run this, you can see that
we only get two rows back, Rolf Smith and John Smith,
like we saw in the presentation,
we can use other conditionals
like greater than or less than
to filter via those types of condition.
We can also use not equal to,
to filter the values
that don't match a specific one we provide.
For example, surname not equal, Smith
can be used to remove the people that have a Smith surname
and only get those that don't, just like that.
A lot of the other conditionals, including AND and OR
we saw in the presentation, so we won't go into them here.
But I wanted to mention something about long queries,
because as you start writing more and more conditionals,
your queries can get a bit long.
So, sometimes it's good to split them out
into multiple lines.
And that's something that SQL totally allows.
So, for example, if you were selecting
many different columns that could grow.
If you were selecting from long table names,
this could grow.
And if you had long WHERE clauses with many ANDs and ORs,
then this could grow as well.
In three separate lines, we'll make it a bit easier to read.
You can normally split these queries wherever you want,
but it is quite common to leave
the key words SELECT from WHERE and so forth,
at the start of the line.
I think it just makes it a little bit easier
to read as well and to understand
where different parts of the query go.
So this is my recommended structure
as your queries get longer.
All right, thanks for watching this video.
I hope you've learned something
I'll see you in the next one.