1. sql
  2. /references
  3. /and-or-not

AND, OR and NOT Operators in SQL

Definition

In SQL, the AND, OR, and NOT operators are used to combine multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement. These operators are used to filter records based on multiple conditions.

Examples

Here is a brief overview of how each operator works:

AND Operator

  • AND operator: The AND operator returns true if all the conditions separated by AND are true. For example, the following statement will return rows where the color is 'red' and the price is greater than 10:
SELECT * 
FROM products 
WHERE color = 'red' AND price > 10;

OR Operator

  • OR operator: The OR operator returns true if any of the conditions separated by OR are true. For example, the following statement will return rows where the color is 'red' or the price is greater than 10:
SELECT * 
FROM products 
WHERE color = 'red' OR price > 10;

NOT Operator

  • NOT operator: The NOT operator negates the condition following it. For example, the following statement will return rows where the color is not 'red':
SELECT * 
FROM products 
WHERE NOT color = 'red';

Combined/Group Operators (AND, OR, NOT)

It's also possible to use parentheses to group conditions and control the order of precedence. For example:

SELECT * 
FROM products 
WHERE (color = 'red' OR color = 'blue') AND price > 10;

This statement will return rows where the color is either 'red' or 'blue', and the price is greater than 10.

Best Practices

  • When using the AND, OR, and NOT operators in SQL, it is important to use parentheses to clearly define the order of operations. For example, the statement SELECT * FROM table WHERE column1 = value1 AND column2 = value2 OR column3 = value3 could be interpreted in multiple ways depending on the order of operations. To ensure that it is clear that column1 and column2 must be true and column3 must be false, the statement should be written as SELECT * FROM table WHERE (column1 = value1 AND column2 = value2).

  • It is also important to use uppercase for the operators. ANSI SQL standard use uppercase for the operators (AND, OR, NOT), so it's better to use uppercase to avoid confusion.

  • Another best practice is to use the NOT operator sparingly, as negating a large number of conditions can make the query harder to understand.

  • Additionally, it is important to use indexes on the columns that are being used in the WHERE clause to improve the performance of the query

  • Finally, It's also good idea to test your query using EXPLAIN command to check the query execution plan and indexes that being used by the query, This will give you an insight on how to improve your query.

  • The AND and OR operators are used to filter records based on more than one condition.

  • The AND operator displays a record if all the conditions separated by AND are TRUE.

  • The OR operator displays a record if any of the conditions separated by OR is TRUE.

  • The NOT operator displays a record if the condition(s) is NOT TRUE.

  • You now have the ability to retrieve the exact data you want. This opens up many possibilities.

  • You can use the WHERE clause in conjunction with AND, OR, and NOT, allowing you to be very precise with your SQL queries.

  • Using uppercase letters for SQL keywords and lowercase letters for everything else. This is a common practice that helps make your queries more readable.