WHERE CLAUSE

This lesson on WHERE CLAUSE is hands-on and example-driven. You will learn how to use the WHERE clause to filter rows from a dataset based on specific criteria. You will master combining comparison and logical operators, including pattern matching with LIKE, to precisely define the data you retrieve. This skill is fundamental for targeted data analysis and manipulation in SQL.

What You'll Be Able To Do

• Filter records using standard comparison operators like =, >, and !=
• Combine multiple filtering conditions using the AND, OR, and NOT logical operators
• Apply parentheses to enforce a specific order of logical evaluation in complex queries
• Construct pattern matching queries using the LIKE operator with % and _ wildcards
• Differentiate the function of the WHERE clause (row filtering) from the SELECT clause (column selection)

Detailed Concept Walkthrough

1. Filtering Rows with WHERE

The WHERE clause restricts the output of a query to only those rows that satisfy a specified condition. It acts as a gatekeeper, evaluating each row against the criteria before inclusion in the result set.

• Mechanism: The database engine evaluates the condition for every row returned by the FROM clause. Only rows where the condition evaluates to TRUE are passed to the final result set.
• Syntax Rule: Conditions use comparison operators (e.g., =, >, <>) to check values against constants or other column values.
• Best Practice: When filtering string or date data types, always enclose the values in single quotes (e.g., 'Leslie', '2023-01-01').

SELECT *
FROM employee_demographics
WHERE salary > 50000; -- Filters for employees earning over 50,000

Key Takeaway: WHERE filters rows; SELECT filters columns.

2. Logical Operators and Precedence

Logical operators (AND, OR, NOT) allow you to combine multiple simple conditions into complex filtering rules. Parentheses dictate the order in which these conditions are evaluated, ensuring correct logic.

• Execution Flow: SQL evaluates conditions within parentheses first, similar to PEMDAS in mathematics, before applying AND and then OR operations.
• Mechanism: AND requires both conditions to be TRUE; OR requires at least one condition to be TRUE; NOT reverses the truth value of a condition.
• Best Practice: Always use parentheses in complex WHERE clauses involving both AND and OR to explicitly control the evaluation order and prevent logical errors.

SELECT first_name, age
FROM employee_demographics
WHERE (first_name = 'Leslie' AND age = 44) OR age > 55;

Key Takeaway: Use parentheses to force the intended logical grouping, especially when mixing AND and OR.

3. Pattern Matching with LIKE

The LIKE operator is used specifically for partial string matching, allowing you to find records where a column value matches a specified pattern. This is essential when exact equality is not known or required.

• Mechanism: LIKE compares the column value against a pattern string containing special wildcard characters.
• Syntax Rule: The percent sign (%) matches zero, one, or multiple characters in any position.
• Syntax Rule: The underscore (_) matches exactly one single character.
• Example: LIKE 'A___%' matches any string starting with 'A' that has at least four characters.

SELECT first_name
FROM employee_demographics
WHERE first_name LIKE '%er%'; -- Finds names containing 'er' anywhere

Key Takeaway: LIKE is for flexible string comparison; use = only for exact string matches.

Topics Covered in WHERE CLAUSE

1. The WHERE Clause (0:02 - 0:17) — The WHERE clause is introduced as the primary tool for filtering records based on a condition.
2. Comparison Operators (0:57 - 3:43) — Detailed examples demonstrate the use of standard comparison operators like equals, greater than, and not equals.
3. Filtering Data Types (3:44 - 4:27) — The video shows how to correctly filter using integers, quoted strings, and dates in the YYYY-MM-DD format.
4. Logical Operators (4:28 - 6:11) — The functions of AND, OR, and NOT are explained for combining or negating filtering conditions.
5. Order of Operations (6:12 - 7:45) — The importance of using parentheses to control the precedence of logical operators is demonstrated with complex examples.
6. LIKE and Wildcards (7:46 - 12:06) — The LIKE operator is detailed, showing how the percent sign and underscore enable flexible pattern matching.
7. Next Steps (12:10 - 12:11) — The video briefly mentions GROUP BY and ORDER BY as topics for the subsequent lesson.

SQL Cheat Sheet

• WHERE — Filters records based on a specified condition

  WHERE salary > 50000
  

• = / > / < / <= / >= — Standard comparison operators

  WHERE age <= 30
  

• != / <> — Checks if two values are not equal

  WHERE first_name <> 'Leslie'
  

• AND — Requires both conditions to be true

  WHERE age > 30 AND gender = 'F'
  

• OR — Requires at least one condition to be true

  WHERE age < 25 OR salary > 70000
  

• % (Wildcard) — Matches zero, one, or multiple characters

  WHERE name LIKE 'Jer%'
  

• _ (Wildcard) — Matches exactly one single character

  WHERE name LIKE 'A__'
  

Comparison Table

Common Pitfalls

• Mistake: Using WHERE first_name = Leslie without quotes for strings. Avoid: Always quote string and date literals: WHERE first_name = 'Leslie'.
• Mistake: Mixing AND and OR without parentheses, leading to incorrect logic. Avoid: Use parentheses to group conditions: WHERE (A AND B) OR C.
• Mistake: Using = when trying to find partial matches in a string. Avoid: Use the LIKE operator with wildcards (% or _) for pattern matching.

FAQs

• Does SQL evaluate AND or OR first? SQL evaluates AND before OR by default, but parentheses override this order of operations.
• Can I use != and <> interchangeably? Yes, both != and <> are standard ways to express the 'not equal to' comparison operator in SQL.
• How do I filter dates? Dates are treated like strings and must be quoted, typically using the YYYY-MM-DD format for reliable comparison.
• What is the difference between SELECT and WHERE? SELECT determines which columns appear in the output, while WHERE determines which rows are included in the output.