MySQL Operators

What are MySQL operators?

We can use MySQL operators in conditions with more than one condition. Those are AND, OR, NOT.

We will use the same students table we have created in MySQL SELECT Statement.

We will see conditions with example one by one :

AND 

This is used when all the conditions are added in the WHERE should be TRUE.

Syntax: 

SELECT columnName1, columnName2, ...
FROM schema.table_name
WHERE condition1 AND condition2 AND condition3 ...;

As shown in the following example where we want to get the records for M as gender and class greater or less than 3.

Example 1:

select * from students where gender = 'M' AND class >= 3;

Example 2:

select * from students where gender = 'M' AND class >= 3 AND marks > 300;

OR

If we want to get the records for either one condition is TRUE.

Syntax: 

SELECT columnName1, columnName2, ...
FROM schema.table_name
WHERE condition1 OR condition2 OR condition3 ...;

Example 1:

select * from students where gender = 'M' OR class >= 5;

Example 2:

select * from students 
where gender = 'M' OR class >= 5 OR marks > 300;

NOT

If we want to get the details which is not like the mentioned value then we can use NOT operator.

Syntax:

SELECT columnName1, columnName2, ... 
FROM schema.table_name 
WHERE NOT condition

Example 1:

select * from students where NOT gender = 'M';

Example 2:

select * from students where NOT marks > 300;

We can also use the combinations of above operators in where condition to get the records.

Example for AND + OR 

If we want to get the students of gender M and class >=3 or student having name as KMS then we can write following condition.

Note that the bracket is added for AND condition. If we remove/change the bracket we may get the different records set.

select * from students 
where (gender = 'M' AND class >= 3) OR marks < 300;

Now we will just change the bracket’s position to the the records.

select * from students 
where gender = 'M' AND (class >= 3 OR marks < 300);

Example for AND + NOT

select * from students 
where gender = 'M' AND NOT class < 4;

Example for OR + NOT

select * from students 
where gender = 'M' OR NOT class < 4;

Example for AND + OR + NOT

select * from students 
where (gender = 'M' AND NOT class < 4) OR class > 3;

Changing the NOT position:

select * from students 
where (gender = 'M' AND class < 4) OR NOT class > 3;

-A blog by Shwetali Khambe