SQL Logical Operators
Sometimes there is required to match multiple conditions/relational expressions in where clause. To handle such type of situation such like of many high level languages SQL also provide logical operators. These operators are AND, OR and NOT.
In all below examples the employee table is used, which is defined in SQL select statement explanation.
SQL AND operator
AND operator results TRUE if both provided relational expressions are TRUE. If any of these is FALSE then result will also FALSE.
SELECT *| list_of_columns FROM table_name WHERE condition_1 AND condition_2 ...... AND condition_n;
If we want to fetch record of employee whose blood group is A+ and name is Jack then the SQL query will be as below.
SELECT * FROM employee WHERE `name` = "Jack" AND `blood_group` = "A+";
SQL OR Operator
OR operator behave reciprocal to the AND operator. It means it will return FALSE if both provided conditions are FALSE, otherwise return TRUE if any one results im TRUE.
SELECT *| list_of_columns FROM table_name WHERE condition_1 OR condition_2 ...... AND condition_n;
Similar to above said example of AND operator, we can chanege it with OR operator. Then the result of query will include both results having either blood group A+ or O- or may any other.
SELECT * FROM employee WHERE `name` = "Jack" OR `blood_group` = "A+";
SQL NOT operator
NOT results TRUE if the result is FALSE and similarly returns FALSE if result of expression is TRUE. It always invert the result of condition in where clause.
SELECT *| list_of_columns FROM table_name WHERE NOT condition;
If we want to display all records of employee excluding the name as Jack then NOT operator can be used.
SELECT * FROM employee WHERE NOT `name` = "Jack";
SQL Multiple Logical Operators
Sometimes there is need to use combination of all these logical operators in a single query. For this purpose we can group our relational expressions/conditions using small parentheses. Then SQL will evaluate results according to precedence of operators.
SELECT * FROM employee WHERE NOT `name` = "Jack" AND `blood_group` = "O+";