Searching Tables with 'WHERE'

7:27 with Andrew Chalkley

The real power of SQL comes from filtering rows of information. You've filtered columns, now you're going to filter rows.

Teacher's Notes
Video Transcript
Downloads

SQL Used

A `WHERE` Clause

SELECT <columns> FROM <table> WHERE <condition>;

Equality Operator

Find all rows that a given value matches a column's value.

SELECT <columns> FROM <table> WHERE <column name> = <value>;

Examples:

SELECT * FROM contacts WHERE first_name = "Andrew";
SELECT first_name, email FROM users WHERE last_name = "Chalkley";
SELECT name AS "Product Name" FROM products WHERE stock_count = 0;
SELECT title "Book Title" FROM books WHERE year_published = 1999;

Inequality Operator

Find all rows that a given value doesn't match a column's value.

SELECT <columns> FROM <table> WHERE <column name> != <value>;
SELECT <columns> FROM <table> WHERE <column name> <> <value>;

The not equal to or inequality operator can be written in two ways != and <>. The latter is less common.

Examples:

SELECT * FROM contacts WHERE first_name != "Kenneth";
SELECT first_name, email FROM users WHERE last_name != "L:one";
SELECT name AS "Product Name" FROM products WHERE stock_count != 0;
SELECT title "Book Title" FROM books WHERE year_published != 2015;

See all of the SQL used in SQL Basics in the SQL Basics Cheat Sheet.

0:00
Remember, the syntax for retrieving all information from a table, but
0:04
there's more that you can do with the SELECT statement.
0:07
We can search to retrieve the information we want by using a WHERE clause.
0:12
We create a WHERE clause with the word keyword followed by a condition.
0:16
Let's take a closer look at what a condition looks like.
0:20
First, you have the column name, then a special character or
0:24
set of characters called an operator.
0:26
And finally, you have the value.
0:29
Let's take a look at our library example again.
0:32
Let's start with a question.
0:34
What are all the titles and authors of the books in the library published in 1997?
0:40
As you can see, when we run this query J.K.
0:42
Rowling's first Harry Potter book is retrieved.
0:45
Let's take a quick look at what the database did with this query.
0:48
It used the condition in the where clause to see if the first underscore
0:53
published column had the value of 1997.
0:56
If it did, it returned to the title and author.
1:02
Since there is only one entry that matches this condition in the books table,
1:08
we only got one result back.
1:10
We also didn't need to include the first_published column in
1:15
the select section of our query.
1:17
We didn't need to include the column we were testing there.
1:21
This is particularly handy when you're generating reports and
1:24
keeping quota to a minimum.
1:26
Also, keeping the data to a minimum in our reports maximizes the efficiency
1:31
of the report being returned.
1:34
See how we use this equal sign?
1:35
This is known as the equality operator.
1:38
We use that to test of the contents in the column were equal to the value of 1997.
1:45
Note that we didn't include quotes around 1997, since it's a numeric type.
1:51
Numeric types will never have spaces in them,
1:54
so just writing the number is all that's needed.
1:57
Now you might remember we have a date data type even though 1997 is a year,
2:02
we're storing it as an integer here.
2:05
Because we don't need a day, month or
2:08
a specific time to be stored in our database.
2:11
We'll look at a date example shortly.
2:14
Let's take a look at what the database does when running this query.
2:18
Here's all the information in the books table.
2:21
When the query is executed,
2:23
each row is processed to see if it matches the condition after the word keyword.
2:29
Let's see this first row.
2:30
The value in the first_published column happens to be 1997.
2:35
It's a match, so it'll be returned, then every other row is evaluated.
2:41
In this case, all of the rows don't match.
2:45
Only the selected columns from the first row are returned.
2:50
Let's modify this query to answer another question.
2:53
What are all the books authored by J.K Rowling?
3:01
And what year were they first_published?
3:04
Since the author is of a text type, we must use quotes around the value.
3:11
If we didn't include quotes, a syntax error would occur.
3:15
See how this has now retrieved all books by J.K. Rowling?
3:18
She's a busy lady.
3:20
An important thing to realize is that the equality operator is strict.
3:25
Meaning, if I were to search for J.K. Rowling with a lower case
3:29
r, It would return no results.
3:34
In other words, the equal to operator is case sensitive.
3:39
In other programming languages, the equal operator is a double equal sign.
3:44
With SQL, it's just one.
3:46
Let's ask another question.
3:48
Let's bring back books that are not by J.K. Rowling.
3:52
We can use the not equal to or inequality operator to do this.
3:57
We write the inequality operator by using an exclamation mark before the equal sign.
4:02
Let's bring back the title, author and first_published date for
4:08
all books that are not written by J.K. Rowling.
4:12
Wow, J.K. Rowling has a lot of books in our library.
4:15
Other authors haven't been anywhere near as busy.
4:18
We've searched using numbers with the first_published column and
4:23
text with the author.
4:24
The next type we can search by is date.
4:27
In our library database, we have a loans table.
4:30
Let's just do a quick select statement to see what we've got available for columns.
4:39
There are three date columns, loaned_on, return_by and returned_on.
4:45
We can ask all sorts of questions related to a particular date.
4:50
For example, what books are loaned on the 10th of December 2015?
4:56
First, we select the book_id
5:01
FROM the loans table WHERE loaned_on
5:07
= "2015-12-10"
5:13
with quotes around it.
5:17
The query returns the book with the ID of 15, but which book is book 15?
5:23
We can figure it out if
5:28
we SELECT title FROM books
5:34
WHERE id is = 15.
5:39
The book with the id of 15 is Emma.
5:45
Remember, I mentioned before that the ID is a unique identifier for
5:50
a row entry in a table.
5:51
People who design databases often use the ID in other tables,
5:56
these are known as foreign keys.
5:58
Let's look at the loans table again.
6:01
You can see that the book_id and
6:04
the patron_id are being used in the loans table.
6:07
These are foreign keys.
6:09
Instead of duplicating information from the book or the patron tables using the ID
6:14
serves on duplication and disc space, because the data is tightly coupled,
6:20
you'll often hear that these databases are referred to as relational databases,
6:25
because the data has relationships with other data.
6:29
These relationships are where a lot of the power and
6:32
complexity come from when writing more advanced SQL queries.
6:36
We'll take a look at those types of queries in another course.
6:40
So to recap, this is the syntax for
6:42
writing queries for searching for an exact match of a value.
6:47
You do your regular select from query with the WHERE keyword followed by condition.
6:53
The condition is a column name, an operator and a value.
6:57
When you want to find an exact match, we use the equality operator,
7:01
which is represented by an equal sign.
7:04
To find all non-matching entries, you'd use the inequality operator,
7:09
the exclamation mark followed by an equal sign.
7:13
You also don't need to list the column you included in the condition or
7:16
the work course in your columns list.
7:19
Finally, if you're using a numeric value,
7:22
you don't need to use any quote marks for the text types and date types you do.

You need to sign up for Treehouse in order to download course files.

SQL Basics

Searching Tables with 'WHERE'

SQL Used

A WHERE Clause

Equality Operator

Inequality Operator

Sign in

A `WHERE` Clause