MySQL Where Clause With Logical Operators (AND, OR, NOT)

MySQL Where Clause With Logical Operators (AND, OR, NOT)

In this MySQL tutorial point, You will learn how to use Logical AND, OR and NOT operators with WHERE Clause in MySQL with simple example.

In our previous tutorial, you have learned how to Select,Insert,Update, data from database tables using SELECT,INSERT INTO,UPDATE and also learn JOINS OF MySQL.

Here, you will learn how to write the syntax of MySQL logical operators “AND, OR, NOT” with WHERE Clause. And how to use this logical operators with MySQL queries.

Table of Content

  • AND Operator using WHERE Clause MySQL
  • OR Operator using WHERE Clause MySQL
  • NOT Operator using WHERE Clause MySQL

AND Operator using WHERE Clause

In MySQL, You can use “AND logical operators” with the WHERE clause to get results in the mysql database table. If all the conditions are not FALSE or specified criteria met , then all the results are fetch from the query.

Syntax

The syntax for the “AND Logical Operator” of MySQL is :-

 SELECT * FROM table_name
WHERE condition_1
AND condition_2;

For the above example of AND logical operator, we will only obtain data for which both condition_1 and condition_2 are TRUE.

Example Of AND Operator using WHERE Clause MySQL

 SELECT * FROM users
WHERE age > 20
AND created_at >= '1980-08-01';

In the example below we will get all the users while fulfilling all the below conditions.

  • Users with age > 5
  • And users created_at on or after ‘1990-08-01’
+------------+-----------+----------+---------------------+-------+------------+--------
 | user_id  | firstname | lastname | email_address       | age | created_at|
 +------------+-----------+----------+---------------------+-------+------------+-------
 | 5        | john      | Doe      | [email protected] |  23 | 1991-01-01 05:01:04 |
 | 6        | mac       | mac      | [email protected]  |  25 | 2000-01-01 03:02:06 |
 +------------+-----------+----------+---------------------+-------+------------+-------
 2 rows in set (0.00 sec)

OR Operator using WHERE Clause

In MySQL, You can use “OR logical operators” with the WHERE clause to get results in the mysql database table. If the any one conditions is not FALSE or specified criteria met, then all the results are fetch from the query.

Syntax

The syntax for the “OR Logical Operator” of MySQL is :-

 SELECT * FROM table_name
WHERE condition_1
OR condition_2;

For the above example of “OR logical operator”, we will only obtain data for which either condition_1 or condition_2 is (specified criteria met) TRUE.

Example Of OR Operator using WHERE Clause MySQL

 SELECT * FROM users
WHERE age > 20
OR created_at >= '1980-08-01';

In the example below, we will get all the users, while fulfilling any one of the conditions provided.

  • Users with age > 20
  • OR users created_at on or after ‘1990-08-01’
+------------+-----------+----------+---------------------+-------+------------+--------
  | user_id   | firstname | lastname | email_address       | age |      created_at     |
  +------------+-----------+----------+---------------------+-------+------------+-------
  | 5         | john      | Doe      | [email protected] |  23 | 1991-01-01 05:01:04 |
  | 6         | mac       | mac      | [email protected]  |  25 | 2000-01-01 03:02:06 |
  | 10        | you       | wld      | [email protected]  |  21 | 1975-01-01 03:02:06 |
  | 11        | mar       | bob      | [email protected]  |  19 | 1992-01-01 03:02:06 |
  +------------+-----------+----------+---------------------+-------+------------+-------
  2 rows in set (0.00 sec)

NOT Operator using WHERE Clause

In MySQL, You can use “NOT logical operators” with the WHERE clause to get results in the mysql database table. If the given conditions specified criteria not met, then all the results are fetch from the query. In other words, If the condition is not TRUE.

Syntax

The syntax for the “NOT Logical Operator” of MySQL is :-

 SELECT * FROM table_name
WHERE NOT condition;

For the above example of “NOT logical operator”, we will only obtain data for condition_1 is not (specified criteria not met) TRUE.

Example Of NOT Operator using WHERE Clause MySQL

 SELECT * FROM users
WHERE NOT lastname = 'bob';

In the example below, we will get all the users from the users table where the last name is not as ‘BOB’.

  • Users with NOT lastname = ‘bob’;
 +------------+-----------+----------+---------------------+-------+------------+--------
 | user_id   | firstname | lastname | email_address       | age |      created_at     |
 +------------+-----------+----------+---------------------+-------+------------+-------
 | 5         | john      | Doe      | [email protected] |  23 | 1991-01-01 05:01:04 |
 | 6         | mac       | mac      | [email protected]  |  25 | 2000-01-01 03:02:06 |
 | 10        | you       | wld      | [email protected]  |  21 | 1975-01-01 03:02:06 |
 | 15        | hey       | dev      | [email protected]  |  28 | 1975-01-01 03:02:06 |
 +------------+-----------+----------+---------------------+-------+------------+-------
 2 rows in set (0.00 sec)

Conclusion

In this MySQL tutorial point, You have learned how to use Logical AND, OR and NOT operators with WHERE Clause in MySQL with fast & easy example.

Leave a Reply

Your email address will not be published. Required fields are marked *