Logical operators enable us to use more than one condition in WHERE clause.

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

Practice #1: Using AND operator.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
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

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
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...

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

-- 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, ...)

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

-- 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

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

-- 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

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

-- 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

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
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: