SQL AND & OR operator
In SQL, we have three types of operators. Among them, we have Logical Operators which are used for logical operations. AND is one of the logical operators. The AND is used with the WHERE clause to make more precise conditions for fetching data from the database by combining more than one condition together. AND operator is used to set multiple conditions with the WHERE clause, alongside, SELECT, UPDATE or DELETE SQL queries.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
Sample Table
Let us use the below Employee table to perform the AND operator.
Example: Use Single AND Operator
In this example, we use a single AND to combine the resultset. The below SQL query will return records where age is greater than 34 and id >103. We use the AND operator to specify two conditions with the WHERE clause. For this query, both conditions on either side of AND operator must be TRUE.
SELECT * FROM employee
WHERE age>34 AND id>103 ;
Output:
Example: Use Multiple AND Operators
In this example, we use multiple AND to combine the resultset. The below SQL query will return records where age is greater than 30, id >103 and city must be ‘London’. We use the AND operator to specify three conditions with the WHERE clause. For this query, all the three conditions on either side of AND operator must be TRUE.
SELECT * FROM employee
WHERE age>30 AND id>103 AND city='London';
Output:
Example: Combining AND, OR Operator
In this example, we use AND operator with the OR operator. We can use AND and OR operator together in a WHERE clause. The below SQL query selects all fields from employee where age>30 and ‘city’ must be "Sweden" or "Mexico". We use parentheses to form complex expressions. It increases human readability.
SELECT * FROM employee
WHERE AGE>30 AND (CITY='Sweden' OR CITY='Mexico');
Output:
Example: Use AND with NOT Operator
In this example, we use the NOT operator to negate the resultset. The below SQL query selects all fields from employee where the city is not London and not Sweden.
SELECT * FROM employee
WHERE NOT city='London' AND NOT city='Sweden';
Output:
Conclusion
In this article, we discussed AND operator using different examples. Must read the next article of OR operator to know the differences between these two logical conditions.
In SQL, we have three types of operators. Among them, we have Logical Operators which are used for logical operations. SQL OR is one of the logical operators. OR operator is used to combine multiple conditions with the WHERE clause.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
Sample Table
Let us use the below Employee table to perform the OR operator.
Example: Use Single OR Operator
In this example, we use a single OR to combine the resultset. The below SQL query will return records where salary is less than 25000 or age is less than 20. We use the OR operator to specify two conditions with the WHERE clause. For this query, if any of the conditions on either side of the OR operator is TRUE for a value, it will generate the result.
SELECT * FROM employee
WHERE age>34 OR id>103 ;
Output:
Example: Use Multiple OR Operators
In this example, we use multiple OR to combine the resultset. The below SQL query will return records where salary is less than 25000, age is less than 20 or id is greater than 101. We use the AND operator to specify three conditions with the WHERE clause. For this query, if any of the conditions on either side of the OR operator is TRUE for a value, it will generate the result.
SELECT * FROM employee
WHERE age=35 OR id>103 OR city='London';
Output:
Example: Combining OR, AND Operator
In this example, we use the OR operator with AND operator. We can use AND and OR operator together in a WHERE clause. The below SQL query selects all fields from "employees" where age>30 and ‘city’ must be "Sweden" or "Mexico". We use parenthesis to form complex expressions. It increases human readability.
SELECT * FROM employee
WHERE age>30 AND (city='Sweden' OR city='Mexico');
Output:
Example: Use OR with NOT Operator
In this example, we use the NOT operator to negate the resultset. The below SQL query selects all fields from "employees" where the city is not "London" or not "Sweden".
SELECT * FROM employee
WHERE NOT city='London' OR NOT city='Sweden';
Output:
Note: The SQL AND condition and SQL OR condition can be combined to test for multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement.
Difference between AND and OR Operators
The only difference between AND and OR is their behavior. When we use AND to combine two or more two conditions, records satisfying all the specified conditions will be there in the result. But in the case of the OR operator, at least one condition from the conditions must be satisfied by any record to be in the resultset.
