SQL AND, OR And NOT Operators
SQL AND, OR And NOT Operators
In SQL, the AND, OR, and NOT operators are used to combine multiple conditions in the WHERE clause of a SELECT statement to filter data.
- AND Operator: The AND operator is used to retrieve rows that meet multiple conditions. It returns true if all the conditions specified are true.
Example: Retrieve all customers whose age is between 20 and 30 and whose country is ‘USA’.
SELECT * FROM customers
WHERE age BETWEEN 20 AND 30 AND country = 'USA';
In the above example, the AND operator is used to combine two conditions – age is between 20 and 30, and the country is ‘USA’. Only the rows that satisfy both the conditions are returned.
- OR Operator: The OR operator is used to retrieve rows that meet at least one of the conditions specified. It returns true if any of the conditions specified are true.
Example: Retrieve all customers whose country is either ‘USA’, ‘Canada’, or ‘Mexico’.
SELECT * FROM customers
WHERE country = 'USA' OR country = 'Canada' OR country = 'Mexico';
In the above example, the OR operator is used to combine three conditions – country is ‘USA’, ‘Canada’, or ‘Mexico’. The rows that satisfy at least one of the conditions are returned.
- NOT Operator: The NOT operator is used to retrieve rows that do not meet a specified condition. It returns true if the condition specified is false.
Example: Retrieve all customers whose age is not between 20 and 30.
SELECT * FROM customers
WHERE NOT (age BETWEEN 20 AND 30);
In the above example, the NOT operator is used to negate the condition – age is between 20 and 30. Only the rows that do not satisfy the condition are returned.
By using these operators, you can combine multiple conditions and filter data in a more precise way.