AND, OR, NOT logical operators in MySQL

Introduction

Logical operators help you combine or change conditions in database queries to get exactly the data you want.

Finding customers who live in a city AND have made a purchase.

Searching for products that are either in stock OR on sale.

Excluding employees who are NOT in the sales department.

Filtering orders that are from a specific date range AND have a high total.

Selecting users who are NOT admins OR have not logged in recently.

Syntax

MySQL

SELECT column_names FROM table_name WHERE condition1 AND condition2;
SELECT column_names FROM table_name WHERE condition1 OR condition2;
SELECT column_names FROM table_name WHERE NOT condition;

AND means both conditions must be true.

OR means at least one condition must be true.

NOT reverses the condition.

Examples

Finds employees who work in Sales and are currently active.

MySQL

SELECT * FROM employees WHERE department = 'Sales' AND status = 'Active';

Finds products that are either in stock or on sale.

MySQL

SELECT * FROM products WHERE stock > 0 OR on_sale = TRUE;

Finds users who are not admins.

MySQL

SELECT * FROM users WHERE NOT is_admin;

Sample Program

This creates a simple customers table, adds some data, then finds customers who live in New York and have made a purchase.

MySQL

CREATE TABLE customers (
  id INT,
  name VARCHAR(50),
  city VARCHAR(50),
  has_purchased BOOLEAN
);

INSERT INTO customers VALUES
(1, 'Alice', 'New York', TRUE),
(2, 'Bob', 'Los Angeles', FALSE),
(3, 'Carol', 'New York', FALSE),
(4, 'Dave', 'Chicago', TRUE);

SELECT name FROM customers WHERE city = 'New York' AND has_purchased = TRUE;

Important Notes

Use parentheses to group conditions when mixing AND and OR to avoid confusion.

NOT applies only to the condition immediately after it unless parentheses are used.

Summary

AND requires all conditions to be true.

OR requires at least one condition to be true.

NOT reverses the truth of a condition.