SQL WHERE Clause

Understanding the SQL WHERE Clause

The WHERE clause is used in SQL to filter the results returned by a query. It allows you to specify conditions that rows must satisfy to be included in the result set.

Basic Syntax

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

Common SQL WHERE Clause Interview Questions

How do you filter data using multiple conditions?
How do you handle NULL values in the WHERE clause?
How can you use pattern matching in WHERE conditions?
What operators can you use in WHERE clauses?

SQL Comparison Operators for WHERE

Basic SQL Comparison Operators

SQL provides several operators to compare values in WHERE clauses.

-- Equal to
SELECT * FROM Videos WHERE user_id = 1;

-- Not equal to
SELECT * FROM Videos WHERE views != 1000;
-- Alternative syntax
SELECT * FROM Videos WHERE views <> 1000;

-- Greater than
SELECT * FROM Videos WHERE views > 1000;

-- Less than
SELECT * FROM Videos WHERE views < 1000;

-- Greater than or equal to
SELECT * FROM Videos WHERE views >= 1000;

-- Less than or equal to
SELECT * FROM Videos WHERE views <= 1000;

SQL Logical Operators

Combine multiple conditions using logical operators.

-- AND: Both conditions must be true
SELECT * FROM Videos 
WHERE views > 1000 AND user_id = 1;

-- OR: At least one condition must be true
SELECT * FROM Videos 
WHERE title LIKE '%Cat%' OR title LIKE '%Dog%';

-- NOT: Negates a condition
SELECT * FROM Users 
WHERE NOT username = 'alice';

-- Combining operators with parentheses
SELECT * FROM Videos 
WHERE (title LIKE '%Challenge%' OR title LIKE '%Tutorial%') 
      AND views > 500;

SQL BETWEEN Operator

Test if a value falls within a range (inclusive).

-- Find videos with views between 500 and 1000
SELECT * FROM Videos 
WHERE views BETWEEN 500 AND 1000;

-- Equivalent to
SELECT * FROM Videos 
WHERE views >= 500 AND views <= 1000;

-- Can also be used with dates
SELECT * FROM Videos 
WHERE upload_date BETWEEN '2023-02-01' AND '2023-02-10';

SQL IN Operator

Test if a value matches any value in a list.

-- Find users with specific IDs
SELECT * FROM Users 
WHERE user_id IN (1, 2, 3);

-- Equivalent to
SELECT * FROM Users 
WHERE user_id = 1 
   OR user_id = 2 
   OR user_id = 3;

-- Can also use subqueries with IN
SELECT * FROM Videos 
WHERE user_id IN (
  SELECT user_id 
  FROM Users 
  WHERE join_date >= '2023-01-05'
);

SQL LIKE Operator for Pattern Matching

Used for pattern matching with wildcards.

-- % wildcard: matches any sequence of characters
SELECT * FROM Videos 
WHERE title LIKE 'Funny%';  -- Titles starting with "Funny"

SELECT * FROM Videos 
WHERE title LIKE '%Tutorial%';  -- Titles containing "Tutorial"

-- _ wildcard: matches any single character
SELECT * FROM Users 
WHERE username LIKE '_lice';  -- Usernames like "alice" (with first letter variable)

SQL NULL Value Handling

Test for NULL values (absence of data).

-- Find interactions with no comment text
SELECT * FROM Interactions 
WHERE comment_text IS NULL;

-- Find interactions with comments
SELECT * FROM Interactions 
WHERE comment_text IS NOT NULL;

Note: You cannot use the = operator with NULL values. Always use IS NULL or IS NOT NULL.

Practical SQL WHERE Clause Examples

Filtering Videos by Popularity and Creator

This example shows how to find popular videos from specific creators:

-- Find popular videos from alice or bob
SELECT 
  v.video_id,
  v.title,
  u.username,
  v.views,
  v.upload_date
FROM 
  Videos v
JOIN
  Users u ON v.user_id = u.user_id
WHERE 
  v.views > 800
  AND u.username IN ('alice', 'bob')
ORDER BY 
  v.views DESC;

Complex Filtering for Interaction Analysis

This example demonstrates how to analyze interactions with videos:

-- Find meaningful interactions on popular videos
SELECT 
  i.interaction_id,
  v.title AS video_title,
  u.username,
  i.interaction_type,
  i.comment_text,
  i.timestamp
FROM 
  Interactions i
JOIN
  Videos v ON i.video_id = v.video_id
JOIN
  Users u ON i.user_id = u.user_id
WHERE 
  v.views > 500
  AND (i.interaction_type = 'comment' OR i.interaction_type = 'like')
  AND (i.comment_text IS NULL OR i.comment_text NOT LIKE '%spam%')
  AND i.timestamp BETWEEN '2023-02-01' AND '2023-02-10'
ORDER BY 
  v.views DESC, i.timestamp DESC;

Best Practices for SQL WHERE Clauses

1. Keep Conditions Simple

When learning SQL, start with simple conditions before combining multiple conditions. This helps build a strong understanding of how filtering works.

2. Be Careful with LIKE Patterns

Using wildcards at the beginning of a LIKE pattern (e.g., LIKE '%text') can make queries run slower. Try to use patterns that start with a known character (e.g., LIKE 'A%') when possible.

3. Use Clear and Readable Conditions

Write your WHERE conditions in a clear and readable way. Use parentheses to group conditions and make the logic easier to understand.

Note for Beginners

This page covers the fundamentals of the SQL WHERE clause. More advanced filtering techniques and optimizations will be covered in later sections of this tutorial.

Ready to Practice SQL WHERE Clauses?

Try writing different filtering conditions in our interactive SQL environment.

Practice WHERE Conditions

SQL Learning