AND,OR,IS in mysql

AND operator

We use AND operator to filter more than one column. It returns only rows that matching all the condition.

Simple Select Statement

>>SELECT * FROM user;
>>SELECT * FROM user WHERE age=21 AND sex='male';

Display:

Description

• The first SELECT Statement return everything
• The second SELECT Statement with the WHERE clause use the AND operator, it returns data when the age= 21 and the sex=male

OR operator

The OR operator is opposite of the AND operator. The OR operator retrieves rows that match either condition.

>>SELECT * FROM user WHERE age=23 OR sex IS NULL;

Display

Description

Return all the user who is 23 year old OR who is having undefined gender (NULL)

Null Value

We use IS operator to check whether the value is null or not null. NULL is no value, and it is not mean it contains 0 or have an empty string or space.

>>SELECT * FROM user WHERE sex IS NULL;
>>SELECT * FROM user WHERE sex IS NOT NULL;

Description

The first statement, it returns the user with the gender that is not defined (NULL)
The second statement, it returns the user with the gender that is defined (NOT NULL)