MySql - Logical operators

MySQL Logical Operators: AND, OR, NOT

Introduction

MySQL is a widely-used open-source relational database management system. It allows users to manage data efficiently through structured query language (SQL). One of the key features of SQL is the ability to combine multiple conditions in a query using logical operators. The most common logical operators in MySQL are AND, OR, and NOT.

These logical operators help to filter data by forming complex conditions in the WHERE clause or other conditional statements like HAVING. By using logical operators, SQL developers can precisely define the subset of data they want to retrieve or manipulate. Understanding how these logical operators work is essential to writing efficient and accurate SQL queries.

What are Logical Operators?

Logical operators are used to combine two or more expressions or conditions. In SQL, they are typically used in WHERE clauses to filter rows based on multiple criteria. The result of a logical operation is a Boolean value: TRUE, FALSE, or UNKNOWN (if NULL is involved).

MySQL supports the following logical operators:

• AND: Returns TRUE if all conditions are TRUE.
• OR: Returns TRUE if at least one condition is TRUE.
• NOT: Reverses the result of the condition (TRUE becomes FALSE and vice versa).

1. The AND Operator

1.1 Basic Syntax

SELECT column1, column2
FROM table_name
WHERE condition1 AND condition2;

1.2 Explanation

The AND operator is used when you want to retrieve rows that meet all specified conditions. If any of the conditions separated by AND is FALSE, the entire condition evaluates to FALSE.

1.3 Example

SELECT * FROM employees
WHERE department = 'Sales' AND salary > 50000;

This query returns all employees who are in the Sales department and whose salary is greater than 50,000.

1.4 Multiple AND Conditions

SELECT * FROM employees
WHERE department = 'Sales' AND salary > 50000 AND experience_years > 5;

1.5 Truth Table for AND

2. The OR Operator

2.1 Basic Syntax

SELECT column1, column2
FROM table_name
WHERE condition1 OR condition2;

2.2 Explanation

The OR operator is used when you want to retrieve rows that meet at least one of the specified conditions. If any condition evaluates to TRUE, the OR operation returns TRUE.

2.3 Example

SELECT * FROM employees
WHERE department = 'Sales' OR department = 'Marketing';

This query retrieves all employees who work in either Sales or Marketing.

2.4 Multiple OR Conditions

SELECT * FROM employees
WHERE department = 'Sales' OR department = 'Marketing' OR department = 'Support';

2.5 Truth Table for OR

3. The NOT Operator

3.1 Basic Syntax

SELECT column1, column2
FROM table_name
WHERE NOT condition;

3.2 Explanation

The NOT operator is used to negate a condition. It reverses the truth value of the condition that follows it.

3.3 Example

SELECT * FROM employees
WHERE NOT department = 'HR';

This query returns all employees who are not in the HR department.

3.4 Using NOT with Other Operators

SELECT * FROM products
WHERE NOT price BETWEEN 100 AND 200;

This returns products priced outside the range of 100 to 200.

3.5 Truth Table for NOT

4. Combining AND, OR, and NOT

You can combine logical operators to build more complex conditions. When doing so, use parentheses to control the order of operations.

4.1 Example Without Parentheses

SELECT * FROM employees
WHERE department = 'Sales' OR department = 'Marketing' AND salary > 60000;

This query may not behave as expected because AND has higher precedence than OR.

4.2 Example With Parentheses

SELECT * FROM employees
WHERE (department = 'Sales' OR department = 'Marketing') AND salary > 60000;

Now the condition is clear: first check the department, then filter based on salary.

5. Logical Operators and NULL Values

NULL represents unknown values in SQL. When NULL is involved in logical operations, the result may be UNKNOWN.

5.1 Example

SELECT * FROM employees
WHERE department = NULL; -- Will return no results

Use IS NULL or IS NOT NULL instead:

SELECT * FROM employees
WHERE department IS NULL;

6. Real-World Use Cases

6.1 Filtering Customers by Region and Spending

SELECT customer_id, name, region, total_spent
FROM customers
WHERE region = 'North America' AND total_spent > 1000;

6.2 Product Search with NOT

SELECT product_id, name
FROM products
WHERE NOT name LIKE '%refurbished%';

6.3 Using OR for Optional Filtering

SELECT * FROM orders
WHERE status = 'Pending' OR status = 'Processing';

6.4 Combining NOT with AND/OR

SELECT * FROM employees
WHERE NOT (department = 'Sales' OR department = 'Support');

7. Operator Precedence

Operator precedence determines the order in which conditions are evaluated. In MySQL, the precedence is:

1. NOT
2. AND
3. OR

Use parentheses to override precedence and ensure correct evaluation.

Logical operators like AND, OR, and NOT are fundamental tools in MySQL that enable you to create powerful and expressive queries. These operators allow you to apply complex filtering logic, retrieve meaningful subsets of data, and write conditions that mirror real-world logic.

Understanding how these operators work individually and in combination is crucial for writing accurate, performant, and maintainable SQL queries. Whether you're filtering rows, validating data, or building business logic, the thoughtful use of logical operators will make your SQL much more powerful.