Logical operators: AND, OR, NOT

Similar to other programming languages, SQL allows us to combine multiple conditions using the logical boolean operators AND, OR, and NOT. Using these operators allows you to build more complex and interesting queries.

Basic syntax for AND, OR, NOT

SQL
SELECT column_names
FROM table_name
WHERE a AND b;

Here's an example of how to use these logical operators in a SELECT statement:

SQL
SELECT * FROM courses
WHERE category = 'art' AND level = 'intermediate';

In this example, the SELECT statement is returning all columns from the courses table where the category is 'art' and the level is 'intermediate'. Pretty straightforward!

Order of operations

Here’s a more complex example with multiple conditions. Let’s say we want to find all the intermediate and advanced courses in the “art” category. At first glance, this looks like it might be what we want:

SQL
SELECT *
FROM courses
WHERE category = 'art' AND level = 'intermediate' OR level = 'advanced';

In this case, the query will return any row where:

• The category is equal to ‘art’ and the level is "intermediate", or
• The "level" is "advanced".

But that’s not quite what we wanted! Note that AND comes before OR in the order of operations. Luckily, we can alter the order of operations by adding parentheses to our query, just like a math equation:

SQL
SELECT *
FROM courses
WHERE category = 'art' AND (level = 'intermediate' OR level = 'advanced')

Now our query will work as expected!

Try it yourself

Let's try another example. Given a customers table, find the customer(s) with first name "Bruce" and last name "Wayne".