HomeDatabase, MySQLMySQL Condition

MySQL Condition

/Categories Database, MySQL
MySQL Condition
MySQL AND Condition

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:

+————+—————+———-+
| office_id  | officer__name | address  |
+————+—————+———-+
| 1          | Rahul         | Lucknow  |
| 2          | VimaL         | Faizabad |
| 3          | Deepika       | Lucknow  |
| 4          | Ajeett        | Mau      |
+————+—————+———-+

1) Using % (percent) Wildcard:

 SELECT officer_name  
FROM officers  
WHERE address LIKE 'Luck%';

After executing the code our output something like

+—————+
| officer__name |
+————– +
| Rahul         |
| Deepika       |
+—————+

2) Using _ (Underscore) Wildcard:

SELECT officer_name  
FROM officers  
WHERE address LIKE 'Luc_now';

After executing the above query our output looks like

+—————+
| officer__name |
+————– +
| Rahul         |
| Deepika       |
+—————+

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%

+—————-+
| officer__name  |
+—————-+
| VimaL          |
| Ajeett         |
+—————-+

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

+————+—————+———-+
| office_id  | officer__name | address  |
+————+—————+———-+
| 2          | VimaL         | Faizabad |
| 3          | Deepika       | Lucknow  |
| 4          | Ajeett        | Mau      |
+————+—————+———-+

MySQL IS NULL Condition

To check if the expression has a NULL value, then we have to use MySQL IS NULL statement. IS NULL is used with SELECT, INSERT, UPDATE and DELETE statement.


Syntax:
    expression IS NULL

Example: 

SELECT *  
FROM officers  
WHERE officer_name IS NULL;

After executing the above query our output looks like

+————+—————+———-+
| office_id  | officer__name | address  |
+————+—————+———-+
| 1          | Rahul         | Lucknow  |
| 2          | VimaL         | Faizabad |
| 3          | Deepika       | Lucknow  |
| 4          | Ajeett        | Mau      |

+————+—————+———-+

In officer_name column, there is no NULL value. We are getting the empty result.

MySQL BETWEEN Condition

To retrieve values from an expression within a specific range, then we have to use The MYSQL BETWEEN condition. BETWEEN conditions is used with SELECT, INSERT, UPDATE and DELETE statement.

Syntax:
    expression BETWEEN value1 AND value2;


Here values define an inclusive range that expression is compared to.

(i) MySQL BETWEEN condition with numeric value:

Example: In the following example, we can see only three rows are returned between 1 and 3.

    SELECT *  
    FROM officers  
    WHERE officer_id BETWEEN 1 AND 3;

After executing the above query our output looks like

+————+—————+———-+
| office_id  | officer__name | address  |
+————+—————+———-+
| 1          | Rahul         | Lucknow  |
| 2          | VimaL         | Faizabad |
| 3          | Deepika       | Lucknow  |
+————+—————+———-+

(ii) MySQL BETWEEN condition with date:

Consider our table has the following records:


 
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

Post a Comment