The SQL WHERE clause

Tip

When we are looking for all the information that matches some criteria, we'll likely use a WHERE clause. You might think of this as a kind of search, but it's also referred to as filtering the data. We'll use filter here, as that's likely the term you'll see in more advanced lessons about SQL

Let's check first how to filter data with SQL using the WHERE keyword.

SELECT id, root, nicodemus, english, originator 
FROM roots
WHERE root = "bc"

The first part means the same as before:

Hey SQL database 👋, please go and SELECT some data for me.

I'll first tell you the columns I want:

• id
• root
• nicodemus
• english
• originator

And I want you to get them FROM the table called "roots".

Then the WHERE keyword adds the following:

So, SQL database, I already told you what columns to SELECT and where to select them FROM. But I don't want you to bring me all the rows, I only want the rows WHERE the root column has a value of "bc".

Then the database will bring a table like this:

2

bc

buts

boots.

Lawrence Nicodemus

Tip

Even if the result is only one row, the database always returns a table.

In this case, it's a table with only one row.

You can try that out in DB Browser for SQLite:

DB Browser screenshot showing the result of running a SQL SELECT statement with a WHERE filter against the roots table.

WHERE and FROM are "clauses"

These additional keywords with some sections like WHERE and FROM that go after SELECT (or others) have a technical name, they are called clauses.

There are others clauses too, with their own SQL keywords.

I won't use the term clause too much here, but it's good for you to know it as it will probably show up in other tutorials you could study later. 🤓

SELECT and WHERE

Here's a quick tip that helps me think about it.

• SELECT is used to tell the SQL database what columns to return.
• WHERE is used to tell the SQL database what rows to return.

The size of the table in the two dimensions depend mostly on those two keywords.

SELECT Land

If the table has too many or too few columns, you can choose which ones to SELECT from.

Starting with some table:

id	root	nicodemus	english	originator
1	a	a	hello.	Lawrence Nicodemus
2	bc	buts	boots.	Lawrence Nicodemus
3	bs	tbilso	dragonfly.	null

...and changing the number of columns:

id	nicodemus
1	a
2	buts
3	tbilso

...is all SELECT land.

WHERE Land

If the table has too many or too few rows, that's changed in the WHERE part.

Starting with some table:

id	root	nicodemus	english	originator
1	a	a	hello.	Lawrence Nicodemus
2	bc	buts	boots.	Lawrence Nicodemus
3	bs	tbilso	dragonfly.	null

...and changing the number of rows:

2

bc

buts

boots.

Lawrence Nicodemus

...is all WHERE land.

Conclusion

You can now select the columns you want with SELECT and filter the rows you want with WHERE. 🚀

What's Next

What if you want the table returned by your SQL command to have rows appear in a specific order? You can use an ORDER BY statement! 🚀