AND and OR operators in SQL

Last Updated: 22nd June, 2023

Overview

The AND and OR operators are used to combine two or more conditions in a SELECT, INSERT, UPDATE, or DELETE statement in SQL. 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. These operators filter out records that do not satisfy the given criteria.

AND Operator:

The AND operator is a logical operator that combines two or more conditions in a SQL statement.

Syntax:

SELECT * FROM table_name WHERE condition1 AND condition2 and ...conditionN;

table_name: name of the table
condition1,2,..N : first condition, second condition and so on

This statement is used to retrieve specific records from a table. The table_name specifies which table to access, and the conditions are used to specify which records to retrieve from the table. The statement will return all records that match all of the conditions provided.

Example:

Employees

employeeID	firstName	salary	department
1	John	60000	sales
2	Jane	40000	finance
3	Bob	80000	sales
4	Sarah	45000	marketing

SELECT * FROM Employees WHERE salary > 50000 AND department = 'sales'

Explanation: The above example returns all employees with a salary greater than 50000 and are in the sales department.

OR Operator:

The OR operator is a logical operator in SQL that combines two or more expressions to return true if any of the conditions are true.

Syntax:

SELECT * FROM table_name WHERE condition1 OR condition2 OR... conditionN;

table_name: name of the table
condition1,2,..N : first condition, second condition and so on

This code retrieves records from a table with a certain set of conditions. It will select all data from the table specified by table_name and then filter the results based on the specified conditions (condition1, condition2, etc). The query will return all records that match any of the specified conditions.

Example:

EmployeeID	First Name	Last Name
1	John	Smith
2	Jane	Smith
3	John	Doe
4	Jack	Smith

SELECT * FROM employees
WHERE last_name = 'Smith' OR first_name = 'John';

Explanation: This example uses the OR operator to select all employees whose last name is Smith OR whose first name is John. The database will return any records that match the WHERE clause's conditions.

Combining AND and OR:

Syntax

SELECT column_name(s)
FROM table_name
WHERE condition1 AND/OR condition2;

This syntax is utilized to choose certain columns from a table in a database based on numerous conditions. The conditions can be combined utilizing either the AND or the OR operator. The AND operator is utilized to indicate that all conditions must be met, whereas the OR operator is utilized to indicate that at slightest one of the conditions must be met

Example:

CustomerID	Name	City	Country
1	John	Berlin	Germany
2	Jane	Berlin	Germany
3	Jack	Berlin	Germany

SELECT *
FROM Customers
WHERE City = 'Berlin' AND Country = 'Germany';

This example uses both the AND and OR operators to select records from the "Customers" table where the "City" is equal to "Berlin" and the "Country" is equal to "Germany".

Conclusion

The AND and OR operators can be utilized in SQL to combine numerous Boolean expressions to create a single expression. The AND operator returns genuine in the event that all of its operands are genuine, whereas the OR operator returns genuine on the off chance that at least one of its operands is genuine. Both operators can be utilized to form complex queries to contract down comes about and discover the precise information that's required.

Key takeaways

AND operator combines two or more conditions to narrow down the comes about of a query.
OR operator is utilized to combine two or more conditions to broaden the comes about of a query.
AND operator takes precedence over the OR operator in SQL.
When combining numerous AND and OR operators in a query, the query should be composed clearly and consistently, utilizing parentheses to separate conditions.
AND operator returns records that fulfill all indicated conditions, whereas the OR operator returns records that satisfy at least one specified condition.

Quiz

Which operator is used to combine two or more conditions in SQL?
1. AND
2. OR
3. NOT
4. XOR

Answer: A. AND

Which operator is used to retrieve records that meet any of the conditions specified?
1. AND
2. OR
3. NOT
4. XOR

Answer: B. OR

Which operator is used to combine two conditions and retrieve records that satisfy either of the conditions?
1. AND
2. OR
3. NOT
4. XOR

Answer: B. OR

Which operator is used to retrieve records that meet both of the conditions specified?
1. AND
2. OR
3. NOT
4. XOR

Answer: A. AND

Module 7: Filtering in SQL