sql

# 5. Advanced Data Filtering

---[SIDEBAR]---
"type":"objectives",
"title":"Learning Objectives",
"text":"After completing this lesson you can:

Use the WHERE clause of a SELECT statement to combine filtering conditions.
Understand the order of operations for combined filtering conditions.
Use the keyword IN to filter based on a list of values.
Use the keyword NOT to negate a filter condition.

"
---[SIDEBAR]---

## Combining WHERE Clauses

All of the filtering conditions presenting in lesson 4 used a single criterion. SQL allows you to create more sophistocated filters to precisely retrieve the data you need. One powerful way to do this is to combine logical tests in the WHERE clause using the keywords AND and OR.

---[SIDEBAR]---
"type":"term",
"title":"AND",
"text":"Combines logical tests in the WHERE clause in such as way that both conditions must be met in a row of data for it to be included in the query result."
---[SIDEBAR]---

The keyword AND is used to combine logical tests in such as way that both conditions must be met before the that row of data is included in the query result. The keyword OR is used to combine logical tests in such a way that is either of the conditions is satisfied by a row of data, it will be included in the query result.

---[SIDEBAR]---
"type":"term",
"title":"OR",
"text":"Combines logical tests in the WHERE clause in such as way that if either condition is met in a row of data it will be included in the query result."
---[SIDEBAR]---

## Using the AND Operator

Generally, you will use the AND operator to filter a query result on more than one column at the same time. You can use as many AND operators in a WHERE clause as you need to precisely define the data you need. The AND operator requires that *all* of the combined conditions are met within a single row of data for that row to be included in the query result.

Suppose you want to list the name, nationality, and birth for French artists born after 1800. You can use a chat with ^ai_name^ to create the SELECT statement that will do this:

---[AI]---
[
{
"prompt": "Write a SQL statement to list French artists born after 1800. Display their name, nationality, and birth.",
"pretext": "Certainly! Here's the SQL statement to list French artists born after 1800 and display their name, nationality, and birth:",
"response": "SELECT full_name, nationality, birth \nFROM artist \nWHERE nationality='French' AND birth>1800;",
"posttext": "This query selects the full_name, nationality, and birth columns from the artist table. The WHERE clause filters the results to include only artists with the nationality 'French' and a birth year greater than 1800. The resulting rows will contain the artist's full name, nationality, and birth year for French artists born after 1800."
}
]
---[AI]---

Suppose you want to list the name, nationality, and birth for French artists born after 1800. A SELECT statement that retrieves this data would be:

*SELECT full_name, nationality, birth
FROM artist
WHERE nationality='French' AND birth>1800*

**Try the SQL statement.**
---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas|visible
owner:atlas-query
result:museum-name-sorted
---[SQL]---
SELECT full_name, nationality, birth
FROM artist
WHERE nationality='French' AND birth>1800;
---[SQL]---

Take a minute to review the data retrieved by the last example query. Notice that all 82 artists returned meet both conditions in the WHERE clause. All of the artists are French. Additionally, all of the artists were born after 1800. This demonstrates how the AND operator works. Certainly there are artists in the database with different nationality and those born before 1800. None of these artists will be included in the query result, because they would not meet one or both of the conditions of the WHERE clause.

---[SIDEBAR]---
"type":"review",
"title":"WHERE Clause Syntax",
"text":"The basics of the WHERE clause were discussed in lesson 4. Notice in the last example that both conditions are expressed as a logical test a mathematical operator between two values. In this case, two different operators are used. The = sign means that the nationality must be exactly 'French' and the greater than sign means that the birth has to be larger (after) 1800. Also notice the single quotes around the word 'French'. Anytime non-numeric values are used in a condition, they must be enclosed in single quotes."
---[SIDEBAR]---

Consider a different example. Suppose you want to modify the last example to show French artists born after 1800 who do not have a middle name. You can use a chat with ^ai_name^ to create the SELECT statement that will do this:

---[AI]---
[
{
"prompt": "Modify the previous SQL statement to show only those artists with a NULL value for middle_names.",
"pretext": "To modify the last SQL statement to show only those French artists born after 1800 with a NULL value for middle_names, you can use the following SQL statement:",
"response": "SELECT full_name, nationality, birth \nFROM artist \nWHERE nationality='French' AND birth>1800 AND middle_names IS NULL;",
"posttext": "This query selects the full_name, nationality, and birth columns from the artist table. The WHERE clause filters the results to include only artists with the nationality 'French' and a birth year greater than 1800. The resulting rows will contain the artist's name, nationality, and birth year for French artists born after 1800."
}
]
---[AI]---

Consider a different example. Suppose you want to modify the last example to show French artists born after 1800 who do not have a middle name. A SELECT statement that retrieves this data would be:

*SELECT full_name, nationality, birth
FROM artist
WHERE nationality='French' AND birth>1800 AND middles_names IS NULL*

Suppose you want to list all of the artists born between 1800 and 1900. You can use a chat with ^ai_name^ to create the SELECT statement that will do this:

---[AI]---
[
{
"prompt": "Write a SQL statement to list the artists and their birth for those artists born in or after 1800 and in or before 1900.",
"pretext": "To list the artists and their birth for those artists born in or after 1800 and in or before 1900, you can use the following SQL statement:",
"response": "SELECT full_name, birth \nFROM artist \nWHERE birth>=1800 AND birth<=1900;",
"posttext": "This query selects the full_name and birth columns from the artist table. The WHERE clause filters the results to include only artists whose birth year is greater than or equal to 1800 and less than or equal to 1900. The resulting rows will contain the artist's full name and birth year for artists born in or after 1800 and in or before 1900."
}
]
---[AI]---

Suppose you want to list all of the artists born between 1800 and 1900. A SELECT statement that retrieves this data would be:

*SELECT full_name, birth
FROM artist
WHERE birth>=1800 AND birth<=1900*

**Try the SQL statement.**
---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
SELECT full_name, birth
FROM artist
WHERE birth>=1800 AND birth<=1900;
---[SQL]---

In the example query, the keyword AND is used to apply two criteria to the same column (birth>=1800 AND birth<=1900). In order for both of these conditions to be met, the artist's birth must have happen between 1800 and 1900 (including either 1800 or 1900). It is important to note that the same results would be achieved using the BETWEEN and AND keywords in a single condition (WHERE birth BETWEEN 1800 AND 1900).

## Using the OR Operator

The keyword OR can also be used to combine conditions in the WHERE clause. Where the keyword AND requires that all conditions are met within row for that row to be included in a query result, OR requires that only one condition is met. This is particularly helpful if you want to apply two different conditions to the same column.

For example, you may want to list both the English and French artists and their nationality. You can use a chat with ^ai_name^ to create the SELECT statement that will do this:

---[AI]---
[
{
"prompt": "Write a SQL statement to list the English and French artists and their nationality. Use the keyword OR.",
"pretext": "Certainly! Here's the SQL statement to list the English and French artists and their nationality using the OR keyword:",
"response": "SELECT full_name, nationality \nFROM artist \nWHERE nationality='English' OR nationality='French';",
"posttext": "This query selects the full_name and nationality columns from the artist table. The WHERE clause filters the results to include only artists whose nationality is either 'English' OR 'French'. The resulting rows will contain the artist's full name and nationality for artists who are either English or French. The OR keyword is used to combine multiple conditions for the nationality column."
}
]
---[AI]---

For example, you may want to list both the English and French artists and their nationality. A SELECT statement that retrieves this data would be:

*SELECT full_name, nationality
FROM artist
WHERE nationality = 'English' OR nationality = 'French';*

**Try the SQL statement.**
---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
SELECT full_name, nationality
FROM artist
WHERE nationality = 'English' OR nationality = 'French';
---[SQL]---

In this example, the keyword OR is used to combine two conditions on the nationality column. If an artist is either English or French, they will be included in the query result. You might be wondering what would happen if you combined these two conditions using the keyword AND instead of OR as in this SQL statement

*SELECT full_name, nationality
FROM artist
WHERE nationality = 'English' AND nationality = 'French';*

**Try the SQL statement.**
---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
SELECT full_name, nationality
FROM artist
WHERE nationality = 'English' AND nationality = 'French';
---[SQL]---

Notice that this example query does not return any rows. This happens because there is only one nationality for each artist on the table. It would be impossible for an artist to be English *and* French in the database. Therefore, none of the artists meet *both* conditions and the query does not return any rows.

The keyword OR can be used to combine more than two conditions. It can also be used to combine conditions on different columns. Perhaps you want to list the artists, their nationality, birth, and death for those artists whose nationality is Spanish, who were born before 1400, or who died after 1970. You can use a chat with ^ai_name^ to create the SELECT statement that will do this:

---[AI]---
[
{
"prompt": "Write a SQL statement to list the artists, their nationality, birth, and death for those artists whose nationality is Spanish, who were born before 1400, or who died after 1970.",
"pretext": "To list the artists, their nationality, birth, and death for those artists whose nationality is Spanish, who were born before 1400, or who died after 1970, you can use the following SQL statement:",
"response": "SELECT full_name, nationality, birth, death \nFROM artist \nWHERE nationality = 'Spanish' OR birth < 1400 OR death > 1970;",
"posttext": "This query selects the full_name, nationality, birth, and death columns from the artist table. The WHERE clause filters the results to include only artists who meet any of the following conditions:

Nationality is 'Spanish'
Birth year is before 1400
Death year is after 1970

The resulting rows will contain the artist's full name, nationality, birth year, and death year for Spanish artists, artists born before 1400, or artists who died after 1970."
}
]
---[AI]---

**Try the SQL statement.**
---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
SELECT full_name, nationality, birth, death
FROM artist
WHERE nationality = 'Mexican' OR birth<1400 OR Death>1970
---[SQL]---

The example query demonstrates a few important concepts relating the keyword OR. First, OR can be used to combine more than two conditions. Second, OR can be used to combine conditions on different columns in the table. Third, this example clearly demonstrates how OR works. The query returns three rows. Each of the rows is returned because it satisfies only one of the three conditions in the WHERE clause. Angel Zarraga has a nationality of Mexican, Jan Van Eyck was born before 1400, and Salvador Dali died after 1970. None of these three artists satisfy more than one of the conditions in the WHERE clause.

## Understanding Order of Evaluation

The WHERE clause can use any number of AND and OR keywords. Using combinations of the two enables you to build a sophistocated set of conditions that precisely retrieve the data you need. However, combining AND and OR creates an unexpected problem. This problem is best explained using an example. Assume you want to list the Irish and Canadian artists who died after 1900. You might use this SQL statement to retrieve these artists:

*select full_name, nationality, death
from artist
where nationality = 'Irish' OR nationality = 'Canadian' AND death>1900;*

**Try the SQL statement.**
---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
select full_name, nationality, death
from artist
where nationality = 'Irish' OR nationality = 'Canadian' AND death>1900;
---[SQL]---

Notice that the rows in the query result are not quite right. Earnest Lawson and Jack Butler Yeats are artists that meet the conditions you were hoping to set. However, Charles Cromwell Ingham does not. Charles is of Irish nationality, but died before 1900. The SELECT statement does not return the desired artists because of the order of operations for AND and OR. When AND and OR are combined in the WHERE clause, the conditions combined with the keyword AND run before those combined with OR.

In this case, nationality = 'Canadian' AND death>1900 will be checked together and first. Canadian artists who died after 1900 will be retrieved by the query. With this pair of conditions checked, the remaining OR condition is applied (nationality='Irish'). Any Irish artists will be returned by the query regardless of when they died (the condition death>1900 is only applied to Canadian artists). This query can be revised to ensure that only the Irish artists returned by the query are those who died after 1900. The revised syntax would be:

*select full_name, nationality, death
from artist
where nationality = 'Irish' AND death>1900 OR nationality = 'Canadian' AND death>1900;*

**Try the SQL statement.**
---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
select full_name, nationality, death
from artist
where nationality = 'Irish' AND death>1900 OR nationality = 'Canadian' AND death>1900;
---[SQL]---

Duplicating the condition death>1900 and combining it with the nationality='Irish' condition with the keyword AND will ensure that only those Irish artists who died after 1900 will be returned by the query. A more elagant way to review this query would be to use parentheses. Parentheses are used to group condition statements that must be executed together first, much like parentheses used in arithmetic force operations to be completed first. The revised SQL statement that uses parentheses would be:

*select full_name, nationality, death
from artist
where (nationality = 'Irish' OR nationality = 'Canadian') AND death>1900;*

**Try the SQL statement.**
---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
select full_name, nationality, death
from artist
where (nationality = 'Irish' OR nationality = 'Canadian') AND death>1900;
---[SQL]---

The parentheses in this last example force the conditions nationality = 'Irish' OR nationality = 'Canadian' to be combined first. When checked, the Irish and Canadian artists are added to the query result. Once these conditions are checked, the remaining condition (death>1900) is applied to the set ot Irish and Canadian artists. This will ensure only the Irish and Canadian artists who died after 1940 are retrieved by the query.

---[SIDEBAR]---
"type":"tip",
"title":"Parentheses and Grouping Conditions in the WHERE Clause",
"text":"When a WHERE clause must include a combination of the keywords AND and OR, use parentheses to group the combinations of conditions must be executed together."
---[SIDEBAR]---

## Using the IN Operator
The keyword IN can be used in the WHERE clause to compare the values in a column to a set of alternative values. You can use IN and an alternative to combining conditions against a single column using the keyword OR. It is particularly helpful when you have more than two or three alternative values to check for in a column.

---[SIDEBAR]---
"type":"term",
"title":"IN",
"text":"The keyword IN is used in the WHERE clause to compare the values in a column to a set of alternative values."
---[SIDEBAR]---

>***NEW TERM:* IN**
A keyword used in a WHERE clause to specify a list of values to be matched using an OR comparison.

Suppose you wish to list the English, French, and Spanish artists and their nationality. You can use a chat with ^ai_name^ to create the SELECT statement that will do this:

---[AI]---
[
{
"prompt": "Write a SQL statement to list the English, French, and Spanish artists and their nationality.",
"pretext": "To list the English, French, and Spanish artists and their nationality, you can use the following SQL statement:",
"response": "SELECT full_name, nationality \nFROM artist \nWHERE nationality IN ('English', 'French', 'Spanish');",
"posttext": "This query selects the full_name and nationality columns from the artist table. The WHERE clause filters the results to include only artists whose nationality is either 'English', 'French', or 'Spanish'. The IN keyword is used to specify a list of multiple values for the nationality column. The resulting rows will contain the artist's full name and nationality for artists who are either English, French, or Spanish."
}
]
---[AI]---

Suppose you wish to list the English, French, and Spanish artists and their nationality. A SELECT statement that retrieves this data would be:

*SELECT full_name, nationality
FROM artist
WHERE nationality IN ('English', 'French', 'Spanish');*

**Try the SQL statement.**
---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
SELECT full_name, nationality
FROM artist
WHERE nationality IN ('English', 'French', 'Spanish');
---[SQL]---

This query uses the keyword IN to return the English, French, and Spanish artists. You could also return the same records using this syntax:

*SELECT full_name, nationality
FROM artist
WHERE nationality='English' OR nationality='French' OR nationality='Spanish';*

**Try the SQL statement.**
---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
SELECT full_name, nationality
FROM artist
WHERE nationality='English' OR nationality='French' OR nationality='Spanish'
---[SQL]---

Since both produce the same results, you may be wondering why you would use IN in place of OR. Here are a few reasons:

1. The IN syntax is shorter and easier to read, particularly when there are a lot of options to check.
2. It is easier to manage the order of operations using IN when it is combined with other criteria using the AND keyword.
3. Queries that use the keyword IN will process faster than those that use OR, though the differences in processing speed may not be noticeable.
4. The keyword IN can be used with more advanced queries that you will learn about later in this resource. OR cannot be used with these queries.

## Using the NOT Operator

The keyword NOT has a unique function in the WHERE clause. It is used to negate the condition that appears after it in the clause. There are times that it is easier to describe a condition in terms of what is isn't than what it is. NOT provides simplest way to create conditions that accomplish this.

---[SIDEBAR]---
"type":"term",
"title":"NOT",
"text":"The keyword NOT is used in the WHERE clause to negate the condition that appears after it in the clause."
---[SIDEBAR]---

For example, you may wish to list the artists and their nationality who are not 'French'. You can use a chat with ^ai_name^ to create the SELECT statement that will do this:

---[AI]---
[
{
"prompt": "Use the keyword NOT to write a SQL statement to list the artists and their nationality who are not 'French'.",
"pretext": "Certainly! Here's the SQL statement to list the artists and their nationality who are not 'French' using the NOT keyword:",
"response": "SELECT full_name, nationality \nFROM artist \nWHERE NOT nationality = 'French';",
"posttext": "This query selects the full_name and nationality columns from the artist table. The WHERE clause uses the NOT keyword to negate the condition (nationality = 'French'). This means that the condition will be true for all nationalities except 'French'. The resulting rows will contain the artist's full name and nationality for artists who are not 'French'."
}
]
---[AI]---

For example, you may wish to list the artists and their nationality who are not 'French'. A SELECT statement that retrieves this data would be:

*SELECT full_name, nationality
FROM artist
WHERE NOT nationality = 'French';*

**Try the SQL statement.**
---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
SELECT full_name, nationality
FROM artist
WHERE NOT nationality = 'French';
---[SQL]---

In this example the condition that is negated by the keyword NOT is nationality = 'French'. The query returns all of the rows in the table that don't meet this condition. Alternatively, the same records could be returned if you created a condition to include artists with each of the other nationalities. Notice how much easier it is to use the NOT keyword to exclude French artists than it would be to write the condition to include the artists with all of the other nationalities.

There is a third way to retrieve the same records. The WHERE clause could have also used the <> operator.
The syntax of this SQL statement would be:

**Try the SQL statement.**
---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
SELECT full_name, nationality
FROM artist
WHERE nationality <> 'French';
---[SQL]---

The <> operator can often be used instead of the keyword NOT to retrieve the same rows. There are situations, when creating more advanced SQL statements, that where this will not be the case.

## Summary

This lesson described how to use the WHERE clause of a SELECT statement to combine filtering conditions. It also discussed the order of operations for combined filtering conditions. Finally, it covered the use of the specialized keywords IN and NOT.