Blog

Logical Operators AND, OR, NOT

In SQL database management systems, logical operators are used to add multiple user-defined conditions to the WHERE clause to filter and preview data from a particular table. Logical operators help database users improve and display any table query by creating more complex conditions or criteria.

Basic logical operators in SQL databases.

• AND operator.
• OR operator.
• NOT operator.

So let’s take a closer look at the logical operators in SQL database management systems.

AND logical operator.

The AND logical operator in SQL database management systems is used to add multiple logical conditions, where all AND logical conditions must be in the proper and correct order for the table rows to be added to the output and displayed.

Syntax of the AND logical operator.

SELECT column_name(s) FROM table_name WHERE condition1 AND condition2;

Example – If a database user wants to display employee records from the existing employe table whose monthly salary is more than ₹40,000 and who are associated with the Marketing department, then the user can use the AND logical operator.

SELECT * FROM employe WHERE salary > 40000 AND department = ‘Marketing’;

This query will display only those employee records whose monthly salary is more than ₹40,000 and who are associated with the Marketing department.

Output of the AND logical operator.

employee_id emp_name emp_age department salary

101 Siddhi deora 21 Marketing 74000

OR logical operator.

In SQL database management systems, the OR logical operator is used to combine multiple or multiple logical conditions into one and display them. In order to display table rows in the output based on the existing table, at least one of the following logical conditions must be true.

Syntax of the OR logical operator.

SELECT column_name(s) FROM table_name WHERE condition1 OR condition2;

Example – If the database user wants to display the results of employees from the employe database table whose monthly salary is more than ₹50,000, or who are associated with the Design department, then the database user can use the OR logical operator.

SELECT * FROM employe WHERE salary > 50000 OR department = ‘Design’;

The OR operator will display the records of such employees to the user. Those whose monthly salary is more than ₹50,000, or who are employees of the Design department.

Result of the OR logical operator.

employee_id emp_name emp_age department salary

301 Bhavshi deora 37 Design 63000

NOT logical operator.

In SQL database management systems, the NOT logical operator is used to delete or remove rows from a table where a user-defined condition is true. The NOT operator avoids the subsequent condition, meaning that the condition must be false to add rows to the NOT operator.

Syntax of the NOT logical operator.

SELECT column_name(s) FROM table_name WHERE NOT condition;

Example – If a database user wants to display records of employees from the Employee table who are not in the Design department, they can use the NOT logical operator.

SELECT * FROM employe WHERE NOT department = ‘Design’;

This query will display all Employee records in the Design department except for the current Employee table row.

Result of the NOT logical operator.

employee_id emp_name emp_age department salary

101 Siddhi Deora 21 Marketing 74000

201 Harry Deora 23 Development 44000

Combining Logical Operators in SQL Databases.

Database users can combine multiple logical operators to create more complex queries. When grouping AND, OR, and NOT logical operators, it’s necessary to use multiple brackets to control the order of multiple logical operator operations.

Example – If a database user wants to display employee records from the Employee table that are either associated with the Marketing department or whose monthly salary is more than ₹60,000, but does not want to display employees from the Design department, then you can combine all three logical operators to display the desired result.

SELECT * FROM employe

WHERE (department = ‘Marketing’ OR salary > 50000) AND NOT department = ‘Design’;

This group logical operator query displays employee records that are either associated with the Marketing department or whose monthly salary is more than ₹50,000, but does not display employees from the Design department.

Result of Combining Logical Operators.

employee_id emp_name emp_age department salary

101 Siddhi Deora 21 Marketing 74000

Detail Explanation about Logical Operators.