MySQL AND, OR and NOT & Operators

What are Logical Operators in MySQL?

If you want to combine more than one condition, then you need to use the Logical Operators in MySQL. The Logical Operators are used to check for the truthness of some conditions. Logical operators return a Boolean data type with a value of TRUE, FALSE, or UNKNOWN. In MySQL, there are three Logical Operators available. They are as follows:

1. AND: TRUE if both Boolean expressions are TRUE.
2. OR: TRUE if one of the Boolean expressions is TRUE.
3. NOT: Reverses the value of any other Boolean operator.

The Logical Operators in MySQL are used to compare two conditions to check whether a row (or rows) can be selected for the output. 

The WHERE clause can be combined with AND, OR, and NOT operators. The AND and OR operators are used to filter records based on more than one condition:

• The AND operator displays a record if all the conditions separated by AND are TRUE.
• The OR operator displays a record if any of the conditions separated by OR is TRUE.

The NOT operator displays a record if the condition(s) is NOT TRUE.

AND Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

The Logical AND operator in MySQL compares two conditions and returns TRUE if both of the conditions are TRUE and returns FALSE when either is FALSE. If you want to select rows that must satisfy all the given conditions, then in such cases you need to use the AND operator.

In some cases, there are chances where we will have to use more than one condition to filter the data rows. In such a case, we use some special operators that are useful to create such compound conditions. The AND operator is useful to add multiple conditions in a single SQL statement. It only displays the data rows if all conditions are TRUE. If any one of the conditions is false the SQL statement will return an empty result set. The AND operator can be written as a word AND or && symbols without space.

We can use AND condition with SELECT, INSERT, UPDATE or DELETE statements to test two or more conditions in an individual query.

Examples to Understand Logical AND Operator in MySQL:

Let’s see some examples to understand the need and use of AND operator in MySQL. Adding the conditions in the bracket is optional. Using brackets for the conditions makes the code neat and clean.

Example: Fetch all employees whose Department is IT and Age is 28

Now we want to filter the data rows with two conditions simultaneously using AND operator. Our requirement is to find all the employees from the Employee table where the Department is IT and the employee age is 28, then we need to use the AND operator as shown in the below query.

SELECT * FROM employee WHERE (department = ‘IT’ AND age = 28); OR
SELECT * FROM employee WHERE department = ‘IT’ AND age = 28; — Bracket is optional

When we run the above SQL statement the system evaluates if the first condition, that is department value equal to IT is true. If the first condition is true the system, then evaluates the second condition. If both first and second conditions are true the system returns the data row. If any one of the conditions is false the system won’t return that data row. As in our employee table, three employees satisfy the above two conditions, so when you execute the above query, you will get the following data rows as the output.

OR Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

in some cases, we will have to evaluate only one of the conditions is TRUE to return the result set. In such a case, the logical OR operator is useful to create such compound conditions. Similar to AND operator, The OR operator is useful to add multiple conditions in a single SQL statement. It displays the data rows if any one of the multiple conditions is TRUE. If all the conditions are false the SQL statement won’t return any result set.

The OR operator can be written as a word “OR” or “||” two pipe symbols without space. Adding the conditions in the bracket is optional. Using brackets for the conditions makes the code neat and clean. The following is the syntax to use the logical OR operator in MySQL.

NOT Syntax

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Logical NOT Operator Example in MySQL:

We can also use the NOT keyword in the statement to revert one of the conditions. Suppose our requirement is to fetch all the employees whose Department is IT and Age does not equal to 28. Then in that case we can use NOT Operator along as shown in the below query.

SELECT * FROM employee WHERE department = ‘IT’ AND NOT age = 28;

When you execute the above query, you will get the following output. And this time, the result set contains only the data rows where the age column value is not equal to 28 and the department column value is IT as shown in the below image.

More Examples-

AND Example

The following SQL statement selects all fields from “Customers” where country is “Germany” AND city is “Berlin”:

Example

SELECT * FROM Customers
WHERE Country = 'Germany' AND City = 'Berlin';

OR Example

The following SQL statement selects all fields from “Customers” where city is “Berlin” OR “Stuttgart”:

Example

SELECT * FROM Customers
WHERE City = 'Berlin' OR City = 'Stuttgart';

The following SQL statement selects all fields from “Customers” where country is “Germany” OR “Spain”:

Example

SELECT * FROM Customers
WHERE Country = 'Germany' OR Country = 'Spain';

NOT Example

The following SQL statement selects all fields from “Customers” where country is NOT “Germany”:

Example

SELECT * FROM Customers
WHERE NOT Country = 'Germany';

Combining AND, OR and NOT

You can also combine the AND, OR and NOT operators. The following SQL statement selects all fields from “Customers” where country is “Germany” AND city must be “Berlin” OR “Stuttgart” (use parenthesis to form complex expressions):

Example

SELECT * FROM Customers
WHERE Country = 'Germany' AND (City = 'Berlin' OR City = 'Stuttgart');

The following SQL statement selects all fields from “Customers” where country is NOT “Germany” and NOT “USA”:

Example

SELECT * FROM Customers
WHERE NOT Country = 'Germany' AND NOT Country = 'USA';

Logical Operators Examples in MySQL:

Let us understand how to use Logical Operators in MySQL with Examples. We are going to use the following Employee table to understand the Logical Operators.

Please use the following SQL Script to create the company database and employee table with the required records.

CREATE DATABASE company;
USE company;
CREATE TABLE employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(45) NOT NULL,
  Department VARCHAR(45) NOT NULL,
  Salary FLOAT NOT NULL,
  Gender VARCHAR(45) NOT NULL,
  Age INT NOT NULL,
  City VARCHAR(45) NOT NULL
);
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1001, 'John Doe', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1002, 'Mary Smith', 'HR', 45000, 'Female', 27, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1003, 'James Brown', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike Walker', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda Jones', 'HR', 75000, 'Female', 26, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag Mohanty', 'IT', 35000, 'Male', 25, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanla Dewangan', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit Mohanty', 'IT', 50000, 'Male', 28, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya Kumar', 'IT', 50000, 'Male', 28, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina Sharma', 'HR', 75000, 'Female', 26, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1011, 'Pramod Panda', 'IT', 45000, 'Male', 27, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1012, 'Preety Tiwary', 'HR', 55000, 'Female', 28, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1013, 'Santosh Dube', 'IT', 52000, 'Male', 28, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1014, 'Sara Talour', 'HR', 85000, 'Female', 26, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1015, 'Pamela Kar', 'Finance', 70000, 'Female', 26, 'London');