MySQL where clause with logical operators(AND OR NOT); Through this tutorial, we will learn how to use Logical AND, OR and NOT operators with WHERE Clause in MySQL with simple example.
In our previous tutorial, we have learned how to Select,Insert,Update, data from database tables using SELECT,INSERT INTO,UPDATE and also learn JOINS OF MySQL.
Here, we 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.
MySQL WHERE Clause with AND, OR, IN, NOT IN Query Example
- 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, we 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, we 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)
Recommended Post
AGGREGATE FUNCTIONS MYSQL | SUM, AVG, MAX, MIN, COUNTNOT Operator using WHERE Clause
In MySQL, we 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
MySQL where clause with logical operators(AND OR NOT); You have learned how to use Logical AND, OR and NOT operators with WHERE Clause in MySQL with fast & easy example.
