Logical Operators (AND, OR, NOT): Combining Conditions

Logical operators are tools that help you combine conditions in queries for more advanced data filtering. Chances are, you’ve already seen them in programming languages. If you think of queries like a spam filter program in your email inbox, logical operators are like the rules that decide whether an email goes to the "Spam" folder or stays in "Inbox".

SQL is a tool that’s often used for selecting or analyzing data. Logical operators (AND, OR, NOT) let you combine several conditions in one query, so you can answer more complex questions, like:

• Which students are older than 18 and have a grade of "A"?
• Which products belong to the "Electronics" or "Home Appliances" category?
• Which customers haven’t made any purchases?

Let’s break down the three main logical operators in SQL:

1. AND: both conditions must be true.
2. OR: at least one of the conditions must be true.
3. NOT: inverts (negates) the condition.

The AND Operator: Both Conditions Are True

The AND operator lets you specify that both parts of the condition have to be met for a row to show up in the result.

Syntax

SELECT column1, column2
FROM table
WHERE condition AND condition2;

Example 1: Filter students older than 18 and with grade "A"

Let’s say we have a students table:

Query:

SELECT name, age, grade
FROM students
WHERE age > 18 AND grade = 'A';

Result:

Example 2: Filter employees from the "HR" department and with salary over 50000

SELECT name, department, salary
FROM employees
WHERE department = 'HR' AND salary > 50000;

The OR Operator: At Least One Condition Is True

The OR operator is used when you want to select rows that match at least one of the conditions.

Syntax

SELECT column1, column2
FROM table
WHERE condition OR condition2;

Example 1: Filter students with grade "A" or "B"

SELECT name, age, grade
FROM students
WHERE grade = 'A' OR grade = 'B';

Result:

Example 2: Products from the "Electronics" category or with price less than 100

Table products:

Quotes in SQL — important: string values are always enclosed in single quotes ('Electronics'). Double quotes are used for identifiers — column or table names with mixed case or special characters (for example, "My Column"). If you mix them up, PostgreSQL will think "Electronics" is a column name and fail with ERROR: column "Electronics" does not exist.

SELECT name, category, price
FROM products
WHERE category = 'Electronics' OR price < 100;

Result:

The NOT Operator: Condition Inversion

If AND and OR set conditions that must be true, NOT does the opposite: it selects rows where the specified condition is false.

Syntax

SELECT column1, column2
FROM table
WHERE NOT condition;

Example 1: Filter students who don’t have grade "A"

SELECT name, age, grade
FROM students
WHERE NOT grade = 'A';

Result:

Example 2: Get products that don’t belong to the "Electronics" category

SELECT name, category, price
FROM products
WHERE NOT category = 'Electronics';

Combining Logical Operators

You can combine logical operators to create even more complex conditions. For example, if you want to select students older than 18 and with grade "A" or "B", you can combine AND and OR.

Syntax with combination

SELECT column1, column2
FROM table
WHERE (condition1 AND condition2) OR condition3;

Example: Students older than 18 with grade "A" or students with grade "B"

SELECT name, age, grade
FROM students
WHERE (age > 18 AND grade = 'A') OR grade = 'B';

Result:

Operator Execution Priority

It’s important to remember that SQL executes logical operators in this order:

1. NOT
2. AND
3. OR

You can use parentheses to change the order of execution. Without parentheses, the result might be unexpected!

Example: Comparing with and without parentheses

What happens in this query?

SELECT name, age, grade
FROM students
WHERE age > 18 AND grade = 'A' OR grade = 'B';

This query selects students:

1. Who are older than 18 and have grade "A".
2. Who have grade "B".

The operator execution priority leads to the same result as with parentheses:

SELECT name, age, grade
FROM students
WHERE (age > 18 AND grade = 'A') OR (grade = 'B');

But if we add parentheses:

SELECT name, age, grade
FROM students
WHERE age > 18 AND (grade = 'A' OR grade = 'B');

Now it will select students older than 18 with grade "A" or "B". Parentheses really change everything!

Common Mistakes When Using Logical Operators

1. Forgotten parentheses. This can lead to unexpected results, especially when combining AND and OR.
2. Using NOT without a clear understanding of which rows are being excluded.
3. Incorrect use of logical operators. For example, using AND instead of OR or vice versa.
4. Complex conditions. Really long conditions with multiple AND, OR, NOT without comments can get hard to read.

These operators are the foundation of any complex queries. Now you’re ready to write queries that not only work, but find exactly what you need!

2

Task

SQL SELF, level 2, lesson 2

Locked

Using the `AND` operator to filter data

Using the `AND` operator to filter data

Open

2

Task

SQL SELF, level 2, lesson 2

Locked

Using the `OR` operator to filter data

Using the `OR` operator to filter data

Open