WHERE: Multiple Filters AND/OR

WHERE: Multiple Filters with AND/OR

So far, we’ve been filtering on one thing at a time. Let’s filter on a few things at once.

StackOverflow Example of Multiple Filters

Like you did in the last lesson, at the top of StackOverflow, click Users, then at the top, click the New Users tab, then Creation Date. You’ll arrive here:

At the top left, click in the Type to find users box, put in your first name, and hit enter.

If you’ve signed up recently, you might even see yourself in the list.

Now, let’s write a query to find users who’ve signed up in 2016, who also have Brent anywhere in the name:

SELECT * 
  FROM dbo.Users
  WHERE CreationDate &gt;= '2016/01/01'
    AND DisplayName LIKE '%Brent%';

SELECT *

FROM dbo.Users

WHERE CreationDate >= '2016/01/01'

AND DisplayName LIKE '%Brent%';

That query is broken up across multiple lines because as our queries get more complicated, we want to make ’em easier to read. (Not just for other people, but for ourselves – you’re going to go back to some query you wrote last month and go, “Who wrote this ugly thing?”)

As with so many other topics on StackOverflow, there’s actually a SQL formatting question – and a lot of answers.

Anyhoo, when you run that query, you get:

where-multiple-fields-and — So many Brents, and yet Mom told me I was special

You can daisy-chain as many AND filters together as you’d like.

You can also throw in ORs and parenthesis. Let’s find people who signed up in 2016 who have either Brent or Grimes anywhere in their names:

SELECT * 
  FROM dbo.Users
  WHERE CreationDate &gt;= '2016/01/01'
    AND (DisplayName LIKE '%Brent%' OR DisplayName LIKE '%Grimes%');

SELECT *

FROM dbo.Users

WHERE CreationDate >= '2016/01/01'

AND (DisplayName LIKE '%Brent%' OR DisplayName LIKE '%Grimes%');

Run that query, and you see:

The only place where I'm more popular than Grimes — The only place where I’m more popular than Grimes

The order of the parenthesis is important there – SQL Server follows the same order of operations as other programming languages and your college logic professor.

Now, you try.

Click on the Users tab of StackOverflow, put your city’s name in the “type to find users” box, and hit enter:

It finds all of the people with Chicago in their name, but…that’s not really what I’m looking for. Write a query to find people who live in your city (hint: there’s a column with their geographic data in it), OR who have your city in their name, who have accessed StackOverflow in the last year.

When you’re done with that, move on to the next lesson.