Saturday, August 24, 2024
Latest:

Eduguru

Tech Blog and Discussion

mysql 

MySQL Conditions

Krishna

MySQL AND Condition

The MySQL AND condition is used with SELECT, INSERT, UPDATE or DELETE statements to test two or more conditions in an individual query.

Syntax:

  1. WHERE condition1
  2. AND condition2
  3. …
  4. AND condition_n;

Parameter explanation:

condition1, condition2, … condition_n: Specifies all conditions that must be fulfilled for the records to be selected.

MySQL AND Example

The following example specifies how to use the AND condition in MySQL with SELECT statement.

Consider a table “cus_tbl”, having the following data:

103

Execute the following query:

  1. SELECT *
  2. FROM cus_tbl
  3. WHERE cus_firstname = ‘Ajeet’
  4. AND cus_id > 3;

104

MySQL OR Condition

The MySQL OR condition specifies that if you take two or more conditions then one of the conditions must be fulfilled to get the records as result.

Syntax:

  1. WHERE condition1
  2. OR condition2
  3. …
  4. OR condition_n;

Parameter explanation

condition1, condition2, … condition_n: Specifies all conditions that must be fulfilled for the records to be selected.

MySQL OR Example

The following example specifies how to use the OR condition in MySQL with SELECT statement.

Consider a table “cus_tbl”, having the following data:

105

Execute the following query:

  1. SELECT *
  2. FROM cus_tbl
  3. WHERE cus_firstname = ‘Ajeet’
  4. OR cus_id > 100;

Output:

106

MySQL AND & OR condition

In MySQL, you can use AND & OR condition both together with the SELECT, INSERT, UPDATE and DELETE statement. While combine these conditions, you must be aware where to use round brackets so that the database know the order to evaluate each condition.

Syntax:

  1. WHERE condition1
  2. AND condition2
  3. …
  4. OR condition_n;

Parameter

condition1, condition2, … condition_n: It specifies the conditions that are evaluated to determine if the records will be selected.

MySQL AND OR Example

Consider a table “students”, having the following data.

107

Execute the following query:

  1. SELECT *
  2. FROM students
  3. WHERE (course_name = ‘Java’ AND student_name = ‘Aryan’)
  4. OR (student_id < 2);

Output:

108

MySQL LIKE condition

In MySQL, LIKE condition is used to perform pattern matching to find the correct result. It is used in SELECT, INSERT, UPDATE and DELETE statement with the combination of WHERE clause.

Syntax:

  1. expression LIKE pattern [ ESCAPE ‘escape_character’ ]

Parameters

expression: It specifies a column or field.

pattern: It is a character expression that contains pattern matching.

escape_character: It is optional. It allows you to test for literal instances of a wildcard character such as % or _. If you do not provide the escape_character, MySQL assumes that “\” is the escape_character.

MySQL LIKE Examples

1) Using % (percent) Wildcard:

Consider a table “officers” having the following data.

109

Execute the following query:

  1.  SELECT officer_name
  2. FROM officers
  3. WHERE address LIKE ‘Luck%’;

Output:

110

2) Using _ (Underscore) Wildcard:

We are using the same table “officers” in this example too.

Execute the following query:

  2. SELECT officer_name
  3. FROM officers
  4. WHERE address LIKE ‘Luc_now’;

Output:

111

3) Using NOT Operator:

You can also use NOT operator with MySQL LIKE condition. This example shows the use of % wildcard with the NOT Operator.

Consider a table “officers” having the following data.

MySQL LIKE Condition 4

Execute the following query:

  1. SELECT officer_name
  2. FROM officers
  3. WHERE address NOT LIKE ‘Luck%’;

Output:

MySQL LIKE Condition 5

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:

  1. expression IN (value1, value2, …. value_n);

Parameters

expression: It specifies a value to test.

value1, value2, … or value_n: These are the values to test against expression. If any of these values matches expression, then the IN condition will evaluate to true. This is a quick method to test if any one of the values matches expression.

MySQL IN Example

Consider a table “officers”, having the following data.

MySQL IN Condition 1

Execute the following query:

  1. SELECT *
  2. FROM officers
  3. WHERE officer_name IN (‘Ajeet’, ‘Vimal’, ‘Deepika’);

Output:

MySQL IN Condition 2

Let’s see why it is preferred over OR condition:

Execute the following query:

  1. SELECT *
  2. FROM officers
  3. WHERE officer_name = ‘Ajeet’
  4. OR officer_name = ‘Vimal’
  5. OR officer_name = ‘Deepika’;

Output:

MySQL IN Condition 3

 

MySQL NOT Condition

The MySQL NOT condition is opposite of MySQL IN condition. It is used to negate a condition in a SELECT, INSERT, UPDATE or DELETE statement.

Syntax:

  1. NOT condition

Parameter

condition: It specifies the conditions that you want to negate.

MySQL NOT Operator with IN condition

Consider a table “officers”, having the following data.

MySQL NOT Condition 1

Execute the following query:

  1. SELECT *
  2. FROM officers
  3. WHERE officer_name NOT IN (‘Ajeet’,‘Vimal’,‘Deepika’);

Output:

MySQL NOT Condition 2

MySQL NOT Operator with IS NULL condition:

Execute the following query:

  1. SELECT *
  2. FROM officers
  3. WHERE officer_name IS NOT NULL;

Output:

MySQL NOT Condition 3

MySQL NOT Operator with LIKE condition:

We are taking the same table “officer” for this operation also:

Execute the following query:

  1. SELECT *
  2. FROM officers
  3. WHERE officer_name NOT LIKE ‘A%’;

Output:

MySQL NOT Condition 4

MySQL NOT Operator with BETWEEN condition:

We are taking the same table “officer” for this operation also:

Execute the following query:

  1. SELECT *
  2. FROM officers
  3. WHERE officer_id NOT BETWEEN 3 AND 5;

Output:

MySQL NOT Condition 5

MySQL IS NULL Condition

MySQL IS NULL condition is used to check if there is a NULL value in the expression. It is used with SELECT, INSERT, UPDATE and DELETE statement.

Syntax:

  1. expression IS NULL

Parameter

expression: It specifies a value to test if it is NULL value.

Consider a table “officers” having the following data.

MySQL IS NULL Condition 1

Execute the following query:

  1. SELECT *
  2. FROM officers
  3. WHERE officer_name IS NULL;

Output:

MySQL IS NULL Condition 2

MySQL IS NOT NULL Condition

MySQL IS NOT NULL condition is used to check the NOT NULL value in the expression. It is used with SELECT, INSERT, UPDATE and DELETE statements.

Syntax:

  1. expression IS NOT NULL

Parameter

expression: It specifies a value to test if it is not NULL value.

MySQL IS NOT NULL Example

Consider a table “officers” having the following data.

MySQL IS NOT NULL Condition 1

Execute the following query:

  1. SELECT *
  2. FROM officers
  3. WHERE officer_name IS NOT NULL;

Output:

MySQL IS NOT NULL Condition 2

MySQL BETWEEN Condition

The MYSQL BETWEEN condition specifies how to retrieve values from an expression within a specific range. It is used with SELECT, INSERT, UPDATE and DELETE statement.

Syntax:

  1. expression BETWEEN value1 AND value2;

Parameters

expression: It specifies a column.

value1 and value2: These values define an inclusive range that expression is compared to.

Let’s take some examples:

(i) MySQL BETWEEN condition with numeric value:

Consider a table “officers” having the following data.

MySQL BETWEEN 1

Execute the following query:

  1. SELECT *
  2. FROM officers
  3. WHERE officer_id BETWEEN 1 AND 3;

Output:

MySQL BETWEEN 2

Note: In the above example, you can see that only three rows are returned between 1 and 3.

(ii) MySQL BETWEEN condition with date:

MySQL BETWEEN condition also facilitates you to retrieve records according to date.

See this example:

Consider a table “employees”, having the following data.

MySQL BETWEEN 3

Execute the following query:

  1. SELECT *
  2. FROM employees
  3. WHERE working_date BETWEEN CAST (‘2015-01-24’ AS DATE) AND CAST (‘2015-01-25’ AS DATE);

Output:

MySQL BETWEEN 4

 

Posts to Review:

No related posts.