PHPGurukul

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 :

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 ...;

EX : MySQL Table tblemployees

idEmp_NameEmp_CodeEmp_EmailEmp_JoiningDate
1Anuj Kumar10806121phpgurukulofficial@gmail.com2019-01-01
2Jonh Doe10899781jonhdie@abc.com
2019-01-01
3Sanjeev Kumar1908763sanjv @test.com
2018-06-11
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:

idEmp_NameEmp_CodeEmp_EmailEmp_JoiningDate
1Anuj Kumar10806121phpgurukulofficial@gmail.com2019-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:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

Ex:

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:

idEmp_NameEmp_CodeEmp_EmailEmp_JoiningDate
1Anuj Kumar10806121phpgurukulofficial@gmail.com2019-01-01
2Jonh Doe10899781jonhdie@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:

SELECT column1, column2, ...
FROM table_name
WHERE (condition1) AND (condition2 or condition3 ); 

Ex:

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:

idEmp_NameEmp_CodeEmp_EmailEmp_JoiningDate
1Anuj Kumar10806121phpgurukulofficial@gmail.com2019-01-01
2Jonh Doe10899781jonhdie@abc.com
2019-01-01

IN :

 IN() function finds a match in the given arguments.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

Ex:

Select * from tblemplyees where (id) IN (1,2);

Output:

idEmp_NameEmp_CodeEmp_EmailEmp_JoiningDate
1Anuj Kumar10806121phpgurukulofficial@gmail.com2019-01-01
2Jonh Doe10899781jonhdie@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:

SELECT column_name(s)
 FROM table_name
 WHERE column_name IN (value1, value2, …);

Ex :

Select * from tblemplyees where (id) NOT IN (1,2);

Output:

idEmp_NameEmp_CodeEmp_EmailEmp_JoiningDate
3Sanjeev Kumar1908763sanjv @test.com
2018-06-11

Anuj Kumar

This is Anuj Kumar. I’m a professional web developer with 4+ year experience. I write blogs in my free time. I love to learn new technologies and share with others.
I founded PHPGurukul in September 2015. The main aim of this website to provide PHP, Jquery, MySQL, PHP Oops and other web development tutorials.
.

Recommended Tutorials for you


Add comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.