• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL AND, OR, NOT

← Previous Next →

In MySQL, the AND, OR, and NOT operators are logical operators used to combine or negate conditions in SQL queries. They are typically used with the WHERE clause to filter data based on multiple criteria.

Understanding these operators is crucial because in real-world databases, you rarely filter data using a single condition. Often, you need to specify complex rules — for example, selecting students who are older than 18 and live in a specific city, or students who are not enrolled in a particular course.

Overview of Logical Operators

AND

The AND operator requires both conditions to be true for a record to be selected.

Syntax:

SELECT columns
FROM table_name
WHERE condition1 AND condition2;

Example:

SELECT * FROM students
WHERE age > 18 AND city = 'Mumbai';

  • This query fetches students who are older than 18 and live in Mumbai.

  • If either condition is false, the record is excluded.

OR

The OR operator requires at least one condition to be true.

Syntax:

SELECT columns
FROM table_name
WHERE condition1 OR condition2;

Example:

SELECT * FROM students
WHERE city = 'Delhi' OR city = 'Pune';

  • This retrieves students who live in Delhi or Pune.

  • Records meeting either condition are included.

NOT

The NOT operator negates a condition, returning records that do not satisfy the condition.

Syntax:

SELECT columns
FROM table_name
WHERE NOT condition;

Example:

SELECT * FROM students
WHERE NOT city = 'Mumbai';

  • This fetches students who do not live in Mumbai.

Combining AND, OR, and NOT

You can combine these logical operators to create more complex conditions.

Example:

SELECT * FROM students
WHERE age > 18 AND (city = 'Mumbai' OR city = 'Delhi');

  • Parentheses () are used to group conditions.

  • Without parentheses, MySQL evaluates AND before OR, which can lead to unexpected results.

  • Here, students must be older than 18 and live in either Mumbai or Delhi.

Operator Precedence

In MySQL, logical operators have a priority:

  1. NOT – highest precedence

  2. AND – medium precedence

  3. OR – lowest precedence

This means:

SELECT * FROM students
WHERE age > 18 OR city = 'Mumbai' AND name LIKE 'A%';

  • MySQL evaluates city = 'Mumbai' AND name LIKE 'A%' first, then applies the OR with age > 18.

  • To avoid confusion, use parentheses:

SELECT * FROM students
WHERE (age > 18 OR city = 'Mumbai') AND name LIKE 'A%';

Using AND, OR, NOT with Comparison Operators

Logical operators are often combined with comparison operators (=, >, <, BETWEEN, IN) for precise filtering.

Example 1:

SELECT * FROM students
WHERE age BETWEEN 18 AND 22 AND city = 'Mumbai';

Example 2:

SELECT * FROM students
WHERE city IN ('Delhi', 'Pune') OR age > 20;

Example 3:

SELECT * FROM students
WHERE NOT (city = 'Mumbai' OR city = 'Delhi');

  • This fetches students who live in cities other than Mumbai or Delhi.

Practical Examples

Consider a table students:

id name age city
1 Aditi 19 Mumbai
2 Meera 20 Delhi
3 Pooja 18 Pune
4 Riya 21 Mumbai
5 Ananya 22 Delhi

Example 1 – AND operator:

SELECT * FROM students
WHERE age > 18 AND city = 'Mumbai';

Result: Aditi and Riya

Example 2 – OR operator:

SELECT * FROM students
WHERE city = 'Pune' OR age = 22;

Result: Pooja and Ananya

Example 3 – NOT operator:

SELECT * FROM students
WHERE NOT city = 'Delhi';

Result: Aditi, Pooja, Riya

Example 4 – Combined operators:

SELECT * FROM students
WHERE age > 18 AND (city = 'Mumbai' OR city = 'Delhi');

Result: Aditi, Meera, Riya, Ananya

Best Practices

  1. Use parentheses to clarify the order of operations.

  2. Avoid overly complex queries without breaking them into smaller parts.

  3. Combine AND and OR carefully; remember operator precedence.

  4. Test NOT conditions separately to ensure they produce expected results.

  5. Use indexes on columns used in WHERE conditions with logical operators to improve performance.

Real-Life Use Cases

  1. Student Filtering:
    Select students older than 18 and enrolled in a particular course:

    SELECT * FROM students
WHERE age > 18 AND course_id = 1;

  2. City-Based Queries:
    Find students from multiple cities:

    SELECT * FROM students
WHERE city = 'Mumbai' OR city = 'Delhi';

  3. Excluding Records:
    Fetch students who are not from a specific city:

    SELECT * FROM students
WHERE NOT city = 'Pune';

  4. Combined Criteria:
    Retrieve students aged between 18 and 22 and not living in Mumbai:

    SELECT * FROM students
WHERE age BETWEEN 18 AND 22 AND NOT city = 'Mumbai';

Summary of the Tutorial

  • AND, OR, NOT are logical operators used with the WHERE clause.

  • AND requires all conditions to be true, OR requires at least one condition, and NOT negates a condition.

  • Operator precedence matters: NOT > AND > OR.

  • Parentheses can be used to clarify evaluation order.

  • Logical operators can be combined with comparison operators, IN, BETWEEN, and LIKE for precise filtering.

  • Using these operators effectively allows you to handle complex queries and extract exactly the data you need from your MySQL database.

Practice Questions

  1. Retrieve all students whose age is greater than 18 and live in Mumbai.

  2. Select students who live in Delhi or Pune.

  3. List students who are not from Mumbai.

  4. Fetch students whose age is between 18 and 22 and city is either Delhi or Mumbai.

  5. Retrieve students whose names start with ‘A’ and live in Pune.

  6. Find students who are either older than 20 or live in Delhi.

  7. Select students who are not older than 18.

  8. Fetch students whose city is not Delhi and age is greater than 19.

  9. List students who are younger than 20 or whose names end with ‘a’.

  10. Retrieve students whose age is greater than 18 and (city is Mumbai or name starts with ‘R’).

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top