To test two or more conditions in an individual query we have to use AND condition. AND condition is used with SELECT, INSERT, UPDATE or DELETE statements.
Syntax:
WHERE condition1
AND condition2
...
AND condition_n;
Example
SELECT *
FROM cus_tbl
WHERE cus_firstname = 'Ajeet'
AND cus_id > 3;
MySQL OR Condition
If we take two or more conditions then we have to use OR condition.
Syntax:
WHERE condition1
OR condition2
...
OR condition_n;
Example:
SELECT *
FROM cus_tbl
WHERE cus_firstname = 'Ajeet'
OR cus_id > 100;
MySQL LIKE condition
To perform pattern matching, then we need to use MySQL, LIKE condition. With the combination of WHERE clause LIKE condition is used in SELECT, INSERT, UPDATE and DELETE statement
Syntax:
expression LIKE pattern [ ESCAPE 'escape_character' ]
Consider our table has the following records:
1) Using % (percent) Wildcard:
SELECT officer_name
FROM officers
WHERE address LIKE 'Luck%';
After executing the code our output something like
2) Using _ (Underscore) Wildcard:
SELECT officer_name
FROM officers
WHERE address LIKE 'Luc_now';
After executing the above query our output looks like
3) Using NOT Operator:
NOT operator with MySQL LIKE condition.
SELECT officer_name
FROM officers
WHERE address NOT LIKE 'Luck%';
Above example shows % wildcard with NOT Operator. In the output we can see that the addresses which are NOT LIKE ‘Luck%
MySQL IN Condition
The MySQL IN condition is used to reduce the use of multiple OR conditions in a SELECT, INSERT, UPDATE and DELETE statement.
Syntax:
expression IN (value1, value2, .... value_n);
Example
SELECT *
FROM officers
WHERE officer_name IN ('Ajeet', 'Vimal', 'Deepika');
After executing the above query our output looks like
MySQL IS NULL Condition
expression IS NULL
Example:
SELECT *
FROM officers
WHERE officer_name IS NULL;
After executing the above query our output looks like
MySQL BETWEEN Condition
expression BETWEEN value1 AND value2;
(i) MySQL BETWEEN condition with numeric value:
SELECT *
FROM officers
WHERE officer_id BETWEEN 1 AND 3;
After executing the above query our output looks like
(ii) MySQL BETWEEN condition with date:
Example: In the following example we can see only data between specific dates are shown.
SELECT *
FROM employees
WHERE working_date BETWEEN CAST ('2015-01-24' AS DATE) AND CAST ('2015-01-25' AS DATE);
After executing the above query our output looks like