What are the Logical Operators in MySql Database

The AND & OR & NOT operators(logical operators) are used to filter records based on more than one condition. The AND operator displays a record if both the first condition and the second condition is true. The OR operator displays a record if either the first condition or the second condition is true. The NOT operator displays the records that are false to the specified value. In the previous tutorials, we have learn that the WHERE keyword can be used to conditionally select data from a table.This are simple conditions. With And & OR & NOT we will learn how to make compound conditions. Compound conditions are simple conditions combined connected using And & OR & NOT. We can use as many simple conditions as we like into out query.

Sintax for a compound condition is :

SELECT (column name) FROM table_name
WHERE (simple condition) {[AND|OR|NOT] (simple condition)}+;
Let's create the table we will use

Script:

--first lets drop the table Test (if she exists, if not skip the drop line).
Drop table test;
create table test (id int,name varchar(20),email varchar(20),salary int ,de char(20));
--insert values into table
insert into test values (1,'Eve','[email protected]',1500,'HR');
insert into test values (2,'Jon','[email protected]',2500,'AD');
insert into test values (3,'Mike','[email protected]',3000,'AD');
insert into test values (4,'Paul','[email protected]',3200,'HR');
insert into test values (5,'Mary','[email protected]',1800,'IT');
insert into test values (6,'Jane','[email protected]',2200,'IT');
Check the table to see the data and the structure. 
SQLselect * from test ;
    ID NAME         EMAIL            SALARY  DE
 ---------- -------------------- -------------------- ---------- --  --------
     1  Eve         [email protected]        1500   HR
     2  Jon         [email protected]        2500   AD
     3  Mike       [email protected]        3000   AD
     4  Paul        [email protected]        3200   HR
     5  Mary       [email protected]        1800   IT
     6  Jane        [email protected]       2200   IT
Very good

The AND condition

If you want to select rows that must satisfy all the given conditions, you can use the logical operator, AND.

Example

-we want to see the persons that have the id =1 and name= Eve. 
SQLselect * from test where id=1 and name='Eve';
    ID  NAME            EMAIL          SALARY    DE
 ---------- --------------   ---------------      ----------   -------
    1   Eve         [email protected]      1500        HR
- in this example the AND condition must be true for both cases (for both = conditions), if one of the condition is false then no result will be shownExample of wrong or false = condition. 
SQLselect * from test where id=1 and name='Evan';
-no lines found.

Example 2

-an example with more than 2 AND conditions 
SQLselect * from test where id=1 and name='Eve' and email='[email protected]';
    ID     NAME         EMAIL        SALARY  DE
 ---------- -------------------- -------------------- ---------- ---------
     1   Eve        [email protected]     1500   HR
-we can add as many conditions as we want .

The OR condition

If you want to select rows that satisfy at least one of the given conditions, you can use the logical operator, OR.
  • -when we use OR condition is enough that one of our condition to be true so that all of them to be true.
  • -let's take the example 2 (where the AND condition returned no lines), but this time we will replace the AND condition with OR and see what happens:
SQLselect * from test where id=1 or name='Evan';
    ID  NAME      EMAIL       SALARY  DE
 ---------- --------------  --------------  -------- -------
    1   Eve      [email protected]    1500  HR
- we can see that returned a row; why is that ? because of the OR condition. AND & OR conditions are very important and they of great use in everyday work with SQL language ,for now we will stop here but in the future we will give more attention to our AND & OR conditions .

The NOT condition

If you want to find rows that do not satisfy a condition, you can use the logical operator, NOT. NOT results in the reverse of a condition. That is, if a condition is satisfied, then the row is not returned. Using our test table we will want to find the name of the persons who are not working in the AD department: 
mysqlselect * from test where not de= 'AD';
+------+------+----------------+--------+------+
| id  | name | email     | salary | de  |
+------+------+----------------+--------+------+
|  1 | Eve | [email protected] |  1500 | HR  |
|  4 | Paul | [email protected] |  3200 | HR  |
|  5 | Mary | [email protected] |  1800 | IT  |
|  6 | Jane | [email protected] |  2200 | IT  |
+------+------+----------------+--------+------+
mysqlselect * from test where de not in ('AD');
+------+------+----------------+--------+------+
| id  | name | email     | salary | de  |
+------+------+----------------+--------+------+
|  1 | Eve | [email protected] |  1500 | HR  |
|  4 | Paul | [email protected] |  3200 | HR  |
|  5 | Mary | [email protected] |  1800 | IT  |
|  6 | Jane | [email protected] |  2200 | IT  |
+------+------+----------------+--------+------+
-see that the result of the querys are the same, it will show all the users that are not in the AD department.

Copyright 2020. All Rights Reserved