The logical operators AND, OR, and NOT are the most fundamental tools in our SQL toolkit.
Operators AND, OR, and NOT are incredibly useful in refining our queries to extract the precise information you need from our databases. Without further ado, let’s dive right into it.
SQL Logical Operators
SQL logical operators offer a way to combine or modify conditions in a SQL statement. The AND, OR, and NOT operators are integral components of SQL queries and are used extensively in the WHERE clause. Let’s take a closer look at each of them
1) AND operator : The AND operator allows us to combine two or more conditions in a SQL statement, and returns true if all conditions are met.
2) OR Operator : The OR operator allows you to extract data if any of the specified conditions are met.
3) NOT Operator : The NOT operator is used to exclude a particular condition in a SQL statement. Essentially, it returns true if the condition is not met.
Let’s consider the following sample data for the “Employees” table
EmployeeID FirstName LastName Age City Salary
-------------------------------------------------------------
1 John Doe 28 San Francisco 75000
2 Jane Smith 35 Boston 50000
3 Mary Johnson 40 San Francisco 85000
4 James Brown 32 Austin 60000
5 Linda Davis 30 San Francisco 65000
6 Robert Miller 24 Boston 70000
1) The AND Operator
The AND operator allows us to combine two or more conditions in a SQL statement, and returns true if all conditions are met.
Consider a database table “Employees” with fields “FirstName”, “LastName”, “Age”, “City”, and “Salary”. If you want to select employees who are from the city “San Francisco” and have a salary greater than 70,000, our SQL statement would look like this
SELECT FirstName, LastName
FROM Employees
WHERE City = 'San Francisco' AND Salary > 70000;
In this query, both conditions must be true for any row to be included in the results.
Output:
FirstName LastName
-------------------
John Doe
Mary Johnson
2) The OR Operator
Unlike the AND operator, the OR operator allows you to extract data if any of the specified conditions are met.
For example, if you want to fetch the details of employees who are either from “San Francisco” or have a salary greater than 70,000, you would use the following SQL query
SELECT FirstName, LastName
FROM Employees
WHERE City = 'San Francisco' OR Salary > 70000;
In this case, the query will return rows if either condition is true.
Output
FirstName LastName
-------------------
John Doe
Jane Smith
Mary Johnson
Linda Davis
3) The NOT Operator
The NOT operator is used to exclude a particular condition in a SQL statement. Essentially, it returns true if the condition is not met.
Let’s say you want to select all employees who are not from “San Francisco”. Our SQL statement would look like this
SELECT FirstName, LastName
FROM Employees
WHERE NOT City = 'San Francisco';
This query will return all the employees from cities other than San Francisco.
Output
FirstName LastName
-------------------
Jane Smith
James Brown
Robert Miller
4) Combining AND, OR, NOT Operators
You can use these logical operators in conjunction to create more complex queries. However, be careful about the order of operations – SQL processes NOT before AND, which is processed before OR. Parentheses can help ensure your conditions are evaluated in the way you intend.
Suppose you want to fetch the details of employees who are under the age of 30 and either live in “San Francisco” or earn more than 70,000:
SELECT FirstName, LastName
FROM Employees
WHERE Age < 35 AND (City = 'San Francisco' OR Salary > 70000);
In this query, the OR condition is evaluated first due to the parentheses, then the AND condition. So, you get the list of young employees who either live in San Francisco or have high salaries.
Output
FirstName LastName
-------------------
John Doe
Jane Smith
Linda Davis
Robert Miller
Conclusion
The AND, OR, and NOT operators are powerful tools that can make your SQL queries more flexible and precise. While they seem simple at first glance, understanding their functionality at a deeper level allows you to create complex queries with relative ease.
