SQL AND, OR, and NOT Operators – Tutorial with Examples

SQL offers a range of logical operators that allow you to combine multiple conditions in your query statements. These operators are AND, OR, and NOT. In this article, we will discuss each of these operators in detail, including how they work and how to use them in your SQL statements.

Sample Dataset

To understand the SQL AND, OR, and NOT operators, let’s consider the following sample data set:

id name age city
1 John 30 New York
2 Jane 25 London
3 Jim 35 Paris
4 Jack 32 Tokyo
5 Jill 28 New York

SQL AND Operator

The AND operator is used to retrieve records from a database table where multiple conditions are met. It combines two or more conditions, and returns only the rows where all conditions are true.

SELECT name, age, city
FROM users
WHERE age >= 30 AND city = 'New York';

The above SELECT statement returns the name, age, and city of all users where the age is greater than or equal to 30 and the city is New York. The result will be:

name age city
John 30 New York
Jill 28 New York

SQL OR Operator

The OR operator is used to retrieve records from a database table where at least one of the conditions is met. It combines two or more conditions, and returns the rows where at least one of the conditions is true.

SELECT name, age, city
FROM users
WHERE age >= 30 OR city = 'New York';

The above SELECT statement returns the name, age, and city of all users where the age is greater than or equal to 30 or the city is New York. The result will be:

name age city
John 30 New York
Jim 35 Paris
Jack 32 Tokyo
Jill 28 New York

SQL NOT Operator

The NOT operator is used to retrieve records from a database table where a condition is not met. It negates the condition, and returns only the rows where the condition is false.

SELECT name, age, city
FROM users
WHERE NOT city = 'New York';

The above SELECT statement returns the name, age, and city of all users where the city is not New York. The result will be:

name age city
Jane 25 London
Jim 35 Paris
Jack 32 Tokyo

Combining SQL OR, AND, and NOT Operators

You can also combine the AND and OR operators in a single query. In such cases, you can use parentheses to specify the order of evaluation. The NOT operator can also be used in conjunction with the AND and OR operators to further refine the conditions of your query.

SELECT name, age, city
FROM users
WHERE (age >= 30 AND city = 'New York') OR city = 'London';

The above SELECT statement returns the name, age, and city of all users where either the age is greater than or equal to 30 and the city is New York, or the city is London. The result will be:

name age city
John 30 New York
Jane 25 London
Jill 28 New York
SELECT name, age, city
FROM users
WHERE NOT (city = 'New York' OR city = 'London');

The above SELECT statement returns the name, age, and city of all users where the city is not New York or London. The result will be:

name age city
Jim 35 Paris
Jack 32 Tokyo

By combining the AND, OR, and NOT operators, you can create complex and sophisticated conditions to retrieve data from your database table. Understanding how these operators work and how to use them effectively can greatly enhance your ability to query and manipulate data in SQL.

In conclusion, the SQL AND, OR, and NOT operators are essential tools for querying and manipulating data in a database. They allow you to combine multiple conditions and return only the data that meets your specific criteria.

Leave a Reply

Your email address will not be published. Required fields are marked *