Let us understand how we can filter the data as part of our queries.
- We use
WHEREclause to filter the data. - All comparison operators such as
=,!=,>,<,<=,>=etc can be used to compare a column or expression or literal with another column or expression or literal. - We can use operators such as
LIKEwith%or~with regular expressions for pattern matching. - Boolean
ORandANDcan be performed when we want to apply multiple conditions.- Get all orders with order_status equals to COMPLETE or CLOSED. We can also use IN operator.
- Get all orders from month 2014 January with order_status equals to COMPLETE or CLOSED
- We can also use
BETWEENalong withANDto compare a column or expression against range of values. - We need to use
IS NULLandIS NOT NULLto compare against null values.
WHERE Clause and Basic Operators
SELECT * FROM orders
WHERE order_status = 'COMPLETE'
LIMIT 10
OR and AND Operators
SELECT * FROM orders
WHERE order_status IN ('COMPLETE', 'CLOSED')
LIMIT 10
SELECT * FROM orders
WHERE order_status = 'COMPLETE' OR order_status = 'CLOSED'
LIKE Operator and Date Filtering
SELECT * FROM orders
WHERE order_date = '2014-01-01'
LIMIT 3
SELECT * FROM orders
WHERE to_char(order_date, 'yyyy-MM-dd') LIKE '2014-01%'
LIMIT 3
SELECT * FROM orders
WHERE order_status IN ('COMPLETE', 'CLOSED')
AND to_char(order_date, 'yyyy-MM') = '2014-01'
LIMIT 10
NULL and NOT NULL Comparison
SELECT * FROM users
WHERE user_password IS NOT NULL
Hands-On Tasks
- Count the number of orders with status ‘COMPLETE’.
- Get all the users where the password field is NULL.
Conclusion
In this article, we covered the concepts of filtering data in SQL queries. We discussed the usage of WHERE clause, comparison operators, pattern matching with LIKE, date filtering, and handling NULL values. It is important to practice these concepts to become proficient in SQL querying. Feel free to engage with the community for further learning.
Video Tutorial
Watch the video tutorial here to understand the concepts visually: