Read Prerequisites for this tutorial and practices if you haven't done so.

List of logical operators in MySQL:

Consider the following facts when using logical operators in a SELECT statement:

1. In MySQL, the result of logical operations is evaluated as 1 (TRUE), 0 (FALSE), or NULL.

2. MySQL evaluates any non-zero or non-NULL value to TRUE.

3. Order of Precedence for logical operators is shown below. Arithmetic operators and comparison operators take higher precedence than logical operators.

   Order Evaluated	Operator
   1	Arithmetic operators
   2	Comparison operators
   3	NOT
   4	&&, AND
   5	XOR
   6	||, OR

4. NOT operator can be used with a comparison operator to negate the result of the comparison.

   NOT BETWEEN...AND...

   NOT IN (value1, value2, value3,...)

   NOT LIKE

   IS NOT NULL

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

Practice #1: Using AND operator.

/*
Find products in Beverages category and their 
number of units in stock is less than reorder level.
Query 1 and 2 return the same result.
*/
 
-- Query 1: use AND operator
SELECT ProductID, ProductName
FROM products
WHERE UnitsInStock < ReorderLevel
      AND CategoryID=1;
 
-- Query 2: use && operator
SELECT ProductID, ProductName
FROM products
WHERE UnitsInStock < ReorderLevel
      && CategoryID=1;

AND (&&) operator requires both conditions to be TRUE.

Query result set - 3 rows returned:

AND Truth Table

The following table shows the result of comparing two expressions with AND operator:

Practice #2: Using OR operator

/*
Find products in Beverages or Seafood category.
Query 1 and 2 return the same result.
*/
 
-- Query 1: use OR operator
SELECT ProductID, ProductName
FROM products
WHERE CategoryID=1 OR CategoryID=8;
 
-- Query 2: use || operator
SELECT ProductID, ProductName
FROM products
WHERE CategoryID=1 || CategoryID=8;

OR (||) operator requires either condition to be TRUE.

Query result set - 24 rows returned:

OR Truth Table

The following table shows the result of comparing two expressions with OR operator:

Practice #3: Use NOT BETWEEN...AND...

-- List products which unit price is not
-- between 0 and 100, inclusive.
SELECT ProductID, ProductName, UnitPrice
FROM products
WHERE UnitPrice NOT BETWEEN 0 AND 100;

Query result set - 2 rows returned:

Practice #4: Use NOT IN (value1, value2, value3, ...)

-- List products which reorder level is not 
-- in the list of 0, 10, 15, 20, 25, and 30.
SELECT ProductID, ProductName, ReorderLevel
FROM products
WHERE ReorderLevel NOT IN (0,10,15,20,25,30);

Query result set - 8 rows returned:

Practice #5: Use NOT LIKE

-- Retrieve all products which product 
-- name does not begin with C
SELECT ProductID, ProductName
FROM products
WHERE ProductName NOT LIKE 'C%';

Query result set - 68 rows returned:

Practice #6: Use NOT NULL

-- Find all the employees who have someone  
-- to report to.
SELECT EmployeeID, FirstName, LastName
FROM employees
WHERE ReportsTo IS NOT NULL;

Query result set - 8 rows returned:

Practice #7: Use XOR operator

/*
Query 1: Get all employees who report to Vice  
President and the result should exclude Nancy 
if it exists in FirstName column.
 
XOR is equivalent to AND NOT. See Query 2.
*/
SELECT EmployeeID, FirstName, LastName, ReportsTo
FROM employees
WHERE ReportsTo=2 XOR FirstName='Nancy';
 
-- Query 2 returns the same result as Query 1.
SELECT EmployeeID, FirstName, LastName, ReportsTo
FROM employees
WHERE ReportsTo=2 AND NOT FirstName='Nancy';

The two queries return the same result.

Query result set - 4 rows returned:

Other tutorials in this category

1. The Basic SELECT Statement

2. Using Column Alias in SELECT Statement

3. Using Literal Character Strings

4. Perform Arithmetic Operations

5. Use WHERE Clause to Conditionally Select Rows

6. Sorting Data

7. Using LIMIT Clause to Constrain the Number of Rows Retrieved

8. Using Comparison Operators, Part I

9. Using Comparison Operators, Part II

10. Using LIKE Comparison Operator

11. Eliminating Duplicate Rows

Back to Tutorial Index Page