Big SQL Energy Beginner Course: Lesson 9 Boolean Logical Operators
Got BDE?⚡️
Hi BDEs! Welcome to Lesson 9 of the free Big SQL Energy Beginner Course⚡️ Today we’re learning about filtering data using Boolean Operators in the WHERE clause in SQL!
Return to Big SQL Energy Beginner homepage here to access the other lessons:
If you’re new here:
💁🏽♀️ Who Am I?
I’m Jess Ramos, the founder of Big Data Energy and the creator of the BEST SQL course and community: Big SQL Energy⚡️. Check me out on socials: 🔗LinkedIn, 🔗Instagram, 🔗YouTube, and 🔗TikTok. And of course subscribe to my 🔗newsletter here for all my upcoming lessons and updates— all for free!
⚡️ What is Big SQL Energy?
🔗Big SQL Energy Intermediate is an intermediate SQL course designed to solve real-world business problems hands-on using realistic data and a modern tech stack. You’ll walk away with 2 SQL portfolio projects, portfolio building guidance, access to the Big Data Energy Discord community, and lots of confidence for you upcoming coding interviews! You also get access to all monthly events including masterclasses, office hours, and guest speakers. This course is all of the most important things I’ve learned on the job as a Senior Data Analyst in tech who grew from a $72K salary to over a $150K salary. And now I’m sharing it with YOU! It’s more than just another course— it’s a challenging program designed to upskill your SQL to the intermediate level, get ready to ace your live coding interviews, and build connections in the data community through the Discord and events.
Lesson 9: Boolean Logical Operators
We’ve learned how to filter datasets using the WHERE clause to look for certain conditions to be met (strings, numbers, dates), but data analysis is rarely as simple as a quick filter. Very often, we’re creating complex logic and conditions to look for something really specific. Boolean logical operators allow us to combine multiple conditions into a single TRUE or FALSE output to filter a dataset.
Imagine we want to filter a dataset for customers who live in NYC, are over 30 years old, and have a dog. We’re not just looking for one thing anymore— we’re looking for a specific subset of our data that meets these 3 conditions. These 3 conditions can be combined to create one single condition that evaluates to TRUE or FALSE (a boolean data type). If the entire condition of all of the combined conditions evaluates to TRUE, it will be in our query output, and if not— SEE YA LATER! It will be excluded.
Boolean conditions can be very powerful to string together multiple conditions in the WHERE clause to refine a query search. There are 3 logical boolean operators:
AND: returns true if both/all of the conditions connected with AND evaluate to TRUE
OR: returns true if either/at least 1 of the conditions connected with OR evaluate to TRUE
NOT: negates or reverses the result of the condition (True → False and False → True)
When working with Boolean conditions, it helps to think about your logic in plain English. Do you want BOTH or ALL of the conditions (i.e. AND)? Or do you want EITHER or ONE OF the conditions (i.e. OR). Of course ANDs and ORs can be combined in the same WHERE clause as well to work together to produce the bigger picture you’re looking for.
When working with Boolean conditions, ALWAYS be sure to consider your grouping and parenthesis because if you don’t you can produce logical errors. The default order of precedence in SQL for logical Boolean Operators is:
NOT → AND → OR
which means that NOT will be evaluated first, then AND, and finally OR unless you change the grouping and specify a different order of evaluation with parentheses. Try out the 3 queries below which are all the same except have different groupings with parentheses. Notice how the parentheses change the output.
For example:
--1.
-- Returns the customers that have CA as a state.
-- No customers have New York as the city AND Los Angeles as a city.
-- But some customers have CA as a state.
-- Same logic as query 3 below since AND is before OR in the default order of precedence
SELECT
*
FROM
customers
WHERE
city = 'New York' AND city = 'Los Angeles' OR State = 'CA';
--2.
--Returns NULL because:
-- No customers have New York as the city AND California as a state.
-- No customers have New York as the city AND Los Angeles as a city.
SELECT
*
FROM
customers
WHERE
city = 'New York' AND (city = 'Los Angeles' OR State = 'CA');
--3.
-- Returns the customers that have CA as a state.
-- No customers have New York as the city AND Los Angeles as a city.
-- But some customers have CA as a state.
SELECT
*
FROM
customers
WHERE
(city = 'New York' AND city = 'Los Angeles') OR State = 'CA';
Boolean operators open so many doors for filtering in SQL!
If you’ve enjoyed the free BEGINNER Big SQL Energy course, grab my intermediate course. There are no deadlines or timelines for this course— you start and take as long as you want. It will take your beginner skills to the next level and get you ready to ace your interviews. DM me or email me at courses@bdeanalytics.com if you have questions on the intermediate course!
Return to Big SQL Energy Beginner homepage here to access the other lessons: