MySQL logical conditions

MySQL logical conditions

Introduction

MySQL handles data with clauses, operators, and conditions. The logical condition is used to compare information and returns the required output. This condition applies logic to MySQL expressions or queries.

The logical condition works on two expressions of the data. It supports Boolean conditions. The logical condition comes up with the "WHERE" clause. The logical operator works with where clause and conditions.

Logical Condition	Expression
MySQL AND condition	This condition becomes true when both conditions are true.
MySQL OR condition	This condition becomes true when at least the condition comes true.
MySQL AND OR condition	MySQL query uses "AND" with the "OR" condition in the "WHERE" clause.
MySQL NOT condition	This condition inverses the expression. Display negative output of the given query.

Syntax

The basic logical operators work between two MySQL queries. The logical condition syntax is below.

(Mysql query1) logical_condition (mysql query2);

The logical condition uses MySQL expression and logical operators with the "WHERE" clause. The basic syntax of the logical operator is below.

SELECT columns FROM table_name WHERE query1 logical_condition query2;

You can add the "AND," "OR," "NOT," "AND OR" keyword as a logical condition. You can select, update, and delete statements using the above syntax.

The Boolean data type is also a logical condition. Here, the Boolean condition uses the TINYTEXT data type. The true value shows as "1," and the false value shows "0".

MySQL AND condition

MySQL "AND" condition returns true when both queries are valid. If one query is false, then the output becomes false. This condition helps to retrieve, delete, modify data.

First Query	Second query	Output
True	True	True
True	False	False
False	True	False
False	False	False

The above table displays the working procedure of the AND condition.  You use this operator on more than two queries. Here, all queries must be valid, otherwise false.

Syntax

The AND condition requires a minimum of two queries to fulfill operation. The "AND" operator chooses more than two queries. The "AND" condition syntax is below.

 MySQL "AND" condition uses single time with two queries.
 SELECT columns [*] FROM table_name WHERE query1 AND query2;
 MySQL "AND" condition is used multiple times with various queries.
 SELECT columns [*] FROM table_name WHERE query1 AND query2 AND query3; 

Examples of the "AND" condition

1) Example: The "AND" condition uses a single time.

Execute the below query to get the required data from the table. Here, two query needs to use AND operator.

 mysql> select topic, time, level 
 from mysql_tutorial
 WHERE topic = 'mysql query' AND time = '1 hour'; 

OUTPUT

Here, operators choose topic = 'mysql query' AND time = '1 hour'. Then, you get the third row of the table. The "AND" condition fulfills both conditions then displays the output.

2) Example: The "AND" condition is used multiple times.

Execute the below query to get the required data from the table. Here, more than two queries work with multiple operators.

 mysql> select topic, time, level 
 from mysql_tutorial
 WHERE topic = 'mysql introduction' AND time = '45 minutes' AND level ='beginners'; 

OUTPUT

Here, operators choose the WHERE clause, multiple queries, and the "AND" operators. Then, you get the first row of the table. The "AND" condition fulfills both conditions then displays the output.

3) Example: The "AND" condition does not fulfill in a query.

Execute the below query to get the required output.

 mysql> select topic, time, level
 from mysql_tutorial
 WHERE index_number = 2 AND level = "intermediate"; 

OUTPUT

If both conditions are not fulfilled then, the output displays "Empty set." Here, we know about the false condition.

MySQL OR condition

MySQL "OR" condition returns true when at least one query is true. If one query is false, then the output becomes true. If both queries are false, then the output becomes false.

First Query	Second query	Output
True	True	True
True	False	True
False	True	True
False	False	False

The above table displays the working procedure of the OR condition.  You use an operator on more than two queries.

Syntax

The OR condition requires a minimum of two queries to fulfill operation. You choose more than two queries.

MySQL "OR" condition uses single time with two queries. The single OR operator syntax shows below.

SELECT columns [*] FROM table_name WHERE query1 OR query2;

MySQL "AND" condition is used multiple times with various queries. The multiple OR operator syntax shows below.

SELECT columns [*] FROM table_name WHERE query1 OR query2 OR query3;

Examples of the "OR" condition

1) Example: The "OR" condition uses a single time.

Execute the below query to know the working procedure of the "OR" operator.

mysql> select topic, time, level  from mysql_tutorial WHERE topic = 'mysql query' OR time = 'half hour';

OUTPUT

You get the two rows of the table. The first condition fulfills, and the second condition also fulfills.

2) Example: The "OR" condition is used multiple times.

Execute the below query to know the working procedure of the "OR" operator. The condition uses more than two conditions and "OR" operators.

 mysql> select topic, time, level 
 from mysql_tutorial
 WHERE topic = 'mysql query' OR time = 'half hour' OR level = 'beginners'; 

OUTPUT

You get the three rows of the table. All conditions are fulfilled using "OR" operators. Here, the query uses a two-time "OR" condition. 

3) Example: The "OR" condition is used multiple times.

Execute the below query to know the working procedure of the "OR" operator. The condition uses more than two conditions and "OR" operators.

mysql> select topic, time, level  from mysql_tutorial WHERE topic = 'mysql' OR time = 'half hour' OR level = 'expert';

OUTPUT

Here, one condition fulfills, and the other two conditions are false. Here, the query uses a two-time "OR" condition. The table only displays time = "half hour" information. Mostly, the "OR" condition does not display false output.

MySQL AND OR condition

In this condition, you use both "AND" and "OR" logical operators.  Sometimes, you need to filter data with detail. MySQL query returns the specifically required information. The "AND" operator and "OR" operators are used in a single MySQL expression.

 Firstly, you should use AND operator as a first query. Secondly, you should use the OR operator as a second query. The query must be in a simple bracket.

Syntax

The "AND OR" condition requires a minimum of two queries to fulfill operation. You can choose more than two queries.

 MySQL "AND OR" condition uses single time with two queries.
 SELECT columns [*] FROM table_name WHERE (query1 AND query2) OR (query3 condition);
 MySQL "OR" condition is used multiple times with various queries.
 SELECT columns [*] FROM table_name WHERE (query1 AND query2) OR (query3 OR query4);
 MySQL "AND" condition is used multiple times with various queries.
 SELECT columns [*] FROM table_name WHERE (query1 AND query2 AND query3) OR (query4); 

Examples of the "AND OR" condition

1) Example: The "AND OR" condition uses a single time.

Execute the below query to know about the "AND OR" operator. If you want to apply the advanced condition, then the "AND" operator and "OR" operator combine.

mysql> select index_number, topic, time, level  from mysql_tutorial WHERE (topic = 'mysql query' AND time = '1 hour') OR (index_number < 2);

OUTPUT

The "AND OR" operator is used once and gets output above. The "AND" condition and "OR" condition fulfill the above two rows display.

2) Example: The "AND OR" query uses AND condition multiple times.

Executing the below query uses the "AND" operator multiple times.

 mysql> select topic, time, level 
 from mysql_tutorial
 WHERE (index_number = 3 AND topic = 'mysql query' AND time = '1 hour')
 OR ( level = 'begineers'); 

OUTPUT

You get the above output. Here, you use the "OR" operator one time and two times the "AND" operator. This row fulfills "AND" and "OR" conditions simultaneously.

3) Example: The "AND OR" query uses OR condition multiple times.

Execute the below query using the "OR" operator multiple times.

 mysql> select topic, time, level 
 from mysql_tutorial
 WHERE (index_number = 3 AND topic = 'mysql query')
 OR (time = 'half hour' OR level = 'begineers'); 

OUTPUT

You get the above output. Here, you use the once "AND" operator and two times "OR" operator. The first row fulfills the "OR" condition. The second row fulfills the "AND" condition.

MySQL NOT condition

Sometimes, you have to filter not required data to operate data. MySQL "NOT" condition inverse the given statement. The "NOT" condition is used with clauses or operators. This condition returns negative.

Output	"NOT" output
True	False
False	True

Syntax

The Not condition requires only one query to fulfill operation. The condition uses clauses and operators to negate MySQL expressions. MySQL "NOT" logical condition works in MySQL expressions.

 SELECT columns [*] FROM table_name WHERE NOT condition (values);
 It mostly uses "IN," "LIKE," "NULL" conditions with multiple values. 

Examples of the "NOT" condition

1) Example: The "NOT" condition with the "IN" condition.

Executing the below query uses the "NOT" operator with the "IN" operator.

 mysql>  select topic, time, level 
 from mysql_tutorial
 WHERE index_number NOT IN (1, 3, 5); 

OUTPUT

The above table displays only one row. This row does not include the "IN" operator.

2) Example: The "NOT" condition with the "LIKE" condition.

Execute the below query using the "NOT" operator with the "LIKE" operator.

mysql> select topic, time, level  from mysql_tutorial WHERE topic NOT LIKE '%query%';

OUTPUT

The above table displays only one row. This row does not include the "query" word in the "LIKE" pattern. 

3) Example: The "NOT" condition with the "NULL" condition.

Execute the below query using the "NOT" operator with "IS" and "NULL" operators.

mysql> select * from mysql_tutorial Where topic IS NOT NULL;

OUTPUT

The above output shows the entire data of the table. The table does not show the null value.

4) Example: The "NOT" condition with the "Between" condition.

Execute the below query using the "NOT" operator with "BETWEEN" and "AND" operators.

mysql> select index_number, a topic from mysql_tutorial Where index_number NOT BETWEEN 1 AND 2;

OUTPUT

The above output shows the data of the table. Here, the query applies conditions for the first and second index numbers. The NOT condition displays the third row.

Here you know about the "AND," "OR," "AND OR," and "NOT" condition and their working procedure. You get the difference between all logical conditions. MySQL needs a logical condition to search advanced queries.