MySQL where clause with and, or, in not in; Through this tutorial, i am going to show you how to use Logical AND, OR and NOT operators with WHERE Clause in MySQL query with the help of examples.
MySQL WHERE Clause: AND, OR, IN, NOT IN Query Example
See the following usage of and, or , in not in operator with msyql query; as follows:
- MySQL Where With AND
- MySQL Where With OR
- MySQL Where With NOT
MySQL Where With AND
Using “AND logical operators” with the MySQL 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 of MySQL Where With AND
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)
MySQL Where With OR
Using OR logical operators with the MySQL 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 of MySQL Where With OR
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)
MySQL Where With NOT
Using NOT logical operators with the Mysql 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 of MySQL Where With NOT
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.