Don't Afraid of Source Code

MySQL AND, OR, IN, NOT IN

0

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
Leave A Reply

Your email address will not be published.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More

Privacy & Cookies Policy