Filtering Data

This lesson focuses on filtering data within SQL queries using the `WHERE` clause. You'll learn how to use various operators to specify conditions and extract specific data from your tables, becoming more efficient at retrieving targeted information.

Learning Objectives

  • Understand the purpose and function of the `WHERE` clause in SQL queries.
  • Learn to use comparison operators (e.g., `=`, `>`, `<`) to filter data.
  • Learn to use logical operators (e.g., `AND`, `OR`, `NOT`) to combine filtering conditions.
  • Practice writing SQL queries to extract data based on multiple criteria.

Text-to-Speech

Listen to the lesson content

Lesson Content

Introduction to the WHERE Clause

The WHERE clause is a fundamental part of SQL. It allows you to filter the data returned by your SELECT statement. Think of it as a filter that determines which rows from your table are included in the results. Without it, your query would return all rows from the table, which isn't always what you want! The WHERE clause comes after the FROM clause.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Comparison Operators

Comparison operators are used within the WHERE clause to compare values in your data. Here are the most common ones:

  • = (Equal to): Returns rows where the column value matches the specified value.
  • != or <> (Not equal to): Returns rows where the column value does not match the specified value.
  • > (Greater than): Returns rows where the column value is greater than the specified value.
  • < (Less than): Returns rows where the column value is less than the specified value.
  • >= (Greater than or equal to): Returns rows where the column value is greater than or equal to the specified value.
  • <= (Less than or equal to): Returns rows where the column value is less than or equal to the specified value.

Examples (using a hypothetical customers table):

-- Select customers with a city of 'New York'
SELECT * FROM customers WHERE city = 'New York';

-- Select customers whose age is greater than 30
SELECT * FROM customers WHERE age > 30;

-- Select customers who are not from 'London'
SELECT * FROM customers WHERE city != 'London';

Logical Operators

Logical operators allow you to combine multiple conditions within your WHERE clause. This lets you create more specific and complex filtering rules.

  • AND: Requires both conditions to be true for a row to be included.
  • OR: Requires at least one of the conditions to be true for a row to be included.
  • NOT: Negates a condition. NOT condition is true if the condition is false.

Examples (using a hypothetical orders table):

-- Select orders placed by customers in 'California' AND with a total amount greater than 100
SELECT * FROM orders WHERE customer_state = 'California' AND order_total > 100;

-- Select orders placed by customers in 'Texas' OR 'Florida'
SELECT * FROM orders WHERE customer_state = 'Texas' OR customer_state = 'Florida';

-- Select orders that are NOT marked as 'shipped'
SELECT * FROM orders WHERE NOT status = 'shipped';

The `BETWEEN` Operator

The BETWEEN operator provides a more concise way to specify a range of values. It includes both the starting and ending values.

Syntax:

WHERE column_name BETWEEN value1 AND value2;

Example (using a hypothetical products table):

-- Select products with a price between 50 and 100 (inclusive)
SELECT * FROM products WHERE price BETWEEN 50 AND 100;

The `IN` Operator

The IN operator allows you to specify multiple values in a WHERE clause. It's equivalent to using multiple OR conditions, but it's often more readable and efficient.

Syntax:

WHERE column_name IN (value1, value2, value3, ...);

Example (using a hypothetical customers table):

-- Select customers from 'New York', 'Los Angeles', or 'Chicago'
SELECT * FROM customers WHERE city IN ('New York', 'Los Angeles', 'Chicago');
Progress
0%