SQL AND, OR and NOT Operators

SQL is a powerful language used for managing and retrieving data in relational databases. One of the most important operations in SQL is filtering data based on conditions. To perform these operations, SQL provides several logical operators such as AND, OR, and NOT. We'll discuss what these operators are and how to use them in SQL.

AND operator

The AND operator is used to combine two or more conditions in a query. The AND operator returns only the rows that meet both conditions. For example, if you want to retrieve all employees who have an age of 30 and are from the USA, you would use the following query:

1SELECT * FROM employees
2WHERE age = 30 AND country = 'USA';

OR operator

The OR operator is used to combine two or more conditions in a query, but it returns the rows that meet at least one condition. For example, if you want to retrieve all employees who are either 30 years old or from the USA, you would use the following query:

1SELECT * FROM employees
2WHERE age = 30 OR country = 'USA';

NOT operator

The NOT operator is used to negate a condition. It returns the rows that do not meet the condition. For example, if you want to retrieve all employees who are not 30 years old, you would use the following query:

1SELECT * FROM employees
2WHERE NOT age = 30;

Using AND, OR and NOT operators combined

Consider a database of employees, with the following columns: id, name, age, gender, and department.

Suppose you want to retrieve the names of all employees who are either female and under 30 years old, or male and over 40 years old, but not those who work in the HR department. The SQL query for this would be:

1SELECT name
2FROM employees
3WHERE (gender = 'Female' AND age < 30) OR (gender = 'Male' AND age > 40)
4AND NOT department = 'HR';

The query uses parentheses to group conditions and the AND and OR operators to combine conditions. The NOT operator is used to exclude the employees who work in the HR department. The result of the query would be a list of names of employees who meet the specified conditions.