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.