SELECT *
FROM customers
WHERE city IN ('Pune', 'Mumbai', 'Delhi');
IN checks whether a value belongs to a specified list.
🔹 23. NOT IN
You can also exclude multiple values.
SELECT *
FROM customers
WHERE city NOT IN ('Pune', 'Mumbai');
This returns customers whose city isn't Pune or Mumbai.
🔹 24. LIKE
LIKE is used for pattern matching.
Suppose we want names beginning with A.
SELECT *
FROM customers
WHERE name LIKE 'A%';
Here:
% → Any sequence of characters
So this could match:
Alice
Amit
Ananya
🔹 25. LIKE with %
Example:
SELECT *
FROM customers
WHERE name LIKE '%an%';
This searches for names containing the sequence an.
The exact behavior can depend on database collation and case-sensitivity settings.
🔹 26. LIKE with _
The underscore _ generally represents exactly one character.
Example:
SELECT *
FROM products
WHERE product_code LIKE 'A_1';
This could match:
A11
AB1
AX1
But not:
A123
A1
because _ represents one character.
🔹 27. NULL Values
One of the most important concepts in SQL filtering is NULL.
NULL generally means:
Missing, unknown, or unavailable value.
It does not mean:
Zero
Empty string
False
For example:
customer_id name phone
101 Alice 9999999999
102 Bob NULL
Bob's phone number is missing or unknown.
🔹 28. Checking for NULL
You should not normally write:
WHERE phone = NULL
Instead, use:
SELECT *
FROM customers
WHERE phone IS NULL;
To find records where the value exists:
SELECT *
FROM customers
WHERE phone IS NOT NULL;
This is extremely important in Data Analytics.
🔹 29. WHERE and NULL Logic
Suppose:
WHERE salary > 50000
What happens when salary is NULL?
The condition isn't considered true.
The row won't be returned.
SQL uses three-valued logic involving:
TRUE
FALSE
UNKNOWN
This is one reason NULL handling requires special attention.
🔹 30. WHERE with SELECT
WHERE works together with SELECT.
Example:
SELECT
customer_id,
name,
city
FROM customers
WHERE city = 'Pune';
The query:
1.
Retrieves selected columns
2.
From the customers table
3.
Keeps only rows satisfying the condition
🔹 31. WHERE in Real-World Data Science
Imagine a transaction database containing millions of records.
A Data Scientist needs:
Successful transactions above ₹10,000 from January 2026 onward.
A query might look like:
SELECT
transaction_id,
customer_id,
transaction_date,
amount
FROM transactions
WHERE status = 'Success'
AND amount > 10000
AND transaction_date >= '2026-01-01';
This is much more efficient for analysis than extracting the entire table and filtering everything later in Python.
🔹 32. WHERE Before Python
A common Data Science workflow is:
Database → SQL → Filter/Transform → Python → Analysis → Model
For example:
SELECT
customer_id,
amount,
transaction_date
FROM transactions
WHERE status = 'Success';
Then load the result into Pandas:
import pandas as pd
df = pd.read_sql(query, connection)
SQL handles the database-side filtering, while Python can then handle deeper analysis.
🔹 33. Common Mistakes
❌ Mistake 1: Using = with NULL
Incorrect:
WHERE phone = NULL;
Correct:
WHERE phone IS NULL;