MySQL AND, OR, IN, NOT IN
AND, OR, In, NOT In these all are called logical operators:
AND(&&) :
The AND operator allows creating an SQL statement based on two or more conditions being met. It can be used in any valid SQL statement such as select, insert, update, or delete.
The AND operator requires that each condition must be met for the record to be included in the result set.
The MySQL engine will process all rows in a table and display the result only when all of the conditions specified using the AND operator are satisfied.
Syntax :
1 2 3 |
SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 ...; |
EX : MySQL Table tblemployees
id | Emp_Name | Emp_Code | Emp_Email | Emp_JoiningDate |
1 | Anuj Kumar | 10806121 | phpgurukulofficial@gmail.com | 2019-01-01 |
2 | Jonh Doe | 10899781 | jonhdie@abc.com | 2019-01-01 |
3 | Sanjeev Kumar | 1908763 | sanjv @test.com | 2018-06-11 |
1 2 3 |
Select * from tblemplyees where Emp_JoiningDate ='2019-01-01' and Emp_Code='10806121'; OR Select * from tblemplyees where Emp_JoiningDate ='2019-01-01' && Emp_Code='10806121'; |
Both queries will produce same output.
Output:
id | Emp_Name | Emp_Code | Emp_Email | Emp_JoiningDate |
1 | Anuj Kumar | 10806121 | phpgurukulofficial@gmail.com | 2019-01-01 |
OR (||) :
The OR condition allows creating an SQL statement where records are returned when any one of the conditions are met. It can be used in any valid SQL statement such as select , insert, update, or delete.
The OR condition requires that any of the conditions must be met for the record to be included in the result set.
Syntax:
1 2 3 |
SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...; |
Ex:
1 2 3 |
Select * from tblemplyees where Emp_JoiningDate ='2019-01-01' OR Emp_Code='10806121'; OR Select * from tblemplyees where Emp_JoiningDate ='2019-01-01' || Emp_Code='10806121'; |
Output:
id | Emp_Name | Emp_Code | Emp_Email | Emp_JoiningDate |
1 | Anuj Kumar | 10806121 | phpgurukulofficial@gmail.com | 2019-01-01 |
2 | Jonh Doe | 10899781 | jonhdie@abc.com | 2019-01-01 |
Combining the AND and OR Operator:
The AND and OR conditions can be combined in a single SQL statement. It can be used in any valid SQL statement such as select, insert, update, or delete.
When combining these conditions, it is important to use brackets so that the database knows what order to evaluate each condition.
The MYSQL engine will process all rows in a table and display the result only when all of the conditions specified using the AND operator are satisfied and when any of the conditions specified using the OR operator are satisfied.
Syntax:
1 2 3 |
SELECT column1, column2, ... FROM table_name WHERE (condition1) AND (condition2 or condition3 ); |
Ex:
1 2 3 |
Select * from tblemplyees where (Emp_JoiningDate ='2019-01-01') and (Emp_Code='10899781' OR Emp_Code='10806121'); or Select * from tblemplyees where (Emp_JoiningDate ='2019-01-01') && (Emp_Code='10899781' || Emp_Code='10806121'); |
Output:
id | Emp_Name | Emp_Code | Emp_Email | Emp_JoiningDate |
1 | Anuj Kumar | 10806121 | phpgurukulofficial@gmail.com | 2019-01-01 |
2 | Jonh Doe | 10899781 | jonhdie@abc.com | 2019-01-01 |
IN :
IN() function finds a match in the given arguments.
Syntax:
1 2 3 |
SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...); |
Ex:
1 |
Select * from tblemplyees where (id) IN (1,2); |
Output:
id | Emp_Name | Emp_Code | Emp_Email | Emp_JoiningDate |
1 | Anuj Kumar | 10806121 | phpgurukulofficial@gmail.com | 2019-01-01 |
2 | Jonh Doe | 10899781 | jonhdie@abc.com | 2019-01-01 |
NOT IN:
The WHERE clause when used together with the NOT IN keyword DOES NOT affect the rows whose values match the list of values provided in the NOT IN keyword.
Syntax:
1 2 3 |
SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, …); |
Ex :
1 |
Select * from tblemplyees where (id) NOT IN (1,2); |
Output:
id | Emp_Name | Emp_Code | Emp_Email | Emp_JoiningDate |
3 | Sanjeev Kumar | 1908763 | sanjv @test.com | 2018-06-11 |