Expanding Filters with AND & OR

Combining Filters with AND & OR

Expanding Your Search

In real-world analysis, you often need to combine multiple conditions to find specific data. SQL uses AND and OR to connect these conditions in a WHERE clause.

Welcome! Sometimes a single filter isn't enough. To find exactly what you need in the <code>Movie</code> table, you'll use logical operators. Think of AND as a strict gatekeeper that only lets data through if it meets every single requirement. OR is more generous, allowing data through if it matches any of your criteria.

Combining Conditions: AND vs OR

Refining Your Search

In SQL, you often need to filter data using more than one rule. To do this, we use logical operators.

With OR, we cast a wider net. This would return every 'Comedy' ever made, plus every movie from '2022' regardless of its genre. Welcome to the world of complex filtering! When a single condition isn't enough, we use logical operators to combine rules. Think of AND as a strict filter where every rule must be satisfied. OR is more inclusive; if a row meets any of your criteria, it makes the cut. With AND, we look for the intersection. Only movies that are both 'Comedy' AND released in '2022' would appear here.

The Schema & The Quote Rule

Working with Data Types

Before writing queries, remember our Movie and Rating connection. When filtering, pay attention to data types:

Let's look at our database structure. The <code>Movie</code> table holds our titles and years, while the <code>Rating</code> table provides descriptions. When you filter text columns like <code>Genre</code>, you must wrap the value in single quotes. However, for numeric columns like <code>Year</code>, quotes are not required. Using the wrong format is a common cause of errors!

Filtering Text with Single Quotes

The Quote Rule

When you filter text data (like Genre), you must wrap the value in single quotes.

SELECT Title
FROM Movie
WHERE Genre = 'Comedy';

Before we combine filters, there is one golden rule for text data. Whenever you are searching for a word, like 'Comedy', you must wrap it in single quotes. Without these quotes, SQL thinks 'Comedy' is a column name, which will cause an error. Notice that numbers, like the Year, do not need quotes.

Narrowing Results with AND

Practical Example: AND

To find Comedy movies released after 2010, both conditions must be true.

SELECT Title, Year
FROM Movie
WHERE Genre = 'Comedy' 
AND Year > 2010;

Imagine you're looking for modern laughs. You want Comedies, but only those released after 2010. Watch as we apply the filters. First, we look for 'Comedy'. Then, we check if the year is greater than 2010. Only the movies that satisfy both conditions remain.

Practical Scenario: The AND Operator

Finding Recent Comedies

Let's find all movies with the Genre of 'Comedy' released after the Year 2010.

SELECT Title, Genre, Year
FROM Movie
WHERE Genre = 'Comedy'
AND Year > 2010;

Imagine you want a modern laugh. To find Comedies released after 2010, we need two rules. First, the Genre must be 'Comedy'. Second, the Year must be greater than 2010. Only rows that satisfy both conditions remain in our results.

Practical Scenario: The OR Operator

Expanding Choices

If you want a variety of 'Sci-Fi' or 'Action' movies, use the OR operator to include both.

SELECT Title, Genre
FROM Movie
WHERE Genre = 'Sci-Fi'
OR Genre = 'Action';

What if you're in the mood for high energy? You might want Sci-Fi OR Action. By using OR, we tell the database: 'Give me the movie if it's Sci-Fi, and also give it to me if it's Action.' This expands our list, providing more options for the user.

Broadening Results with OR

Practical Example: OR

To see movies that are either Horror or Sci-Fi, use the OR operator.

SELECT Title, Genre
FROM Movie
WHERE Genre = 'Horror' 
OR Genre = 'Sci-Fi';

Now, what if you're in the mood for either a scare or a space adventure? By using OR, we tell the database to give us rows that match 'Horror' as well as rows that match 'Sci-Fi'. Unlike AND, this expands our list to include both categories.

The Power of Parentheses

Operator Precedence

SQL evaluates AND before OR. Use parentheses () to control the logic flow if you combine them.

Drag the parentheses to change which condition is grouped first.

Be careful when mixing AND and OR! SQL has a specific order of operations. It always processes <code>AND</code> first. If you want to group your <code>OR</code> conditions together, you must use parentheses. Try dragging the parentheses in the example to see how the logic changes.

Exercise: Finding Classic Dramas

Write a query to retrieve the Title and Year of all movies with the Genre of 'Drama' that were released before the year 2000.

Now it's your turn. Write a query to find 'Drama' movies released before the year 2000. Pay close attention to your quotes and the logical operator you choose!

Write Your Own Query

Interactive Challenge

Write a query to retrieve the Title and Genre of all movies that are in the 'Action' genre OR were released in the year 1995.

It's your turn! Based on the schema we've discussed, write a query that finds all movies that are either 'Action' OR from the year '1995'. Remember your single quotes for the text!

Common Pitfalls to Avoid

Watch Your Logic!

Common mistakes for beginners include:

Watch out for these traps. First, forgetting quotes is the most common error. Second, be careful with AND. If you ask for a movie that is 'Comedy' AND 'Drama', you'll likely get zero results. Why? Because a single row in the Genre column can't be two different things at the same time! In that case, you usually want OR.