OR Operator in SQL with Example

In SQL, the OR operator is a logical operator used to combine multiple conditions in a query. It returns true if any of the conditions are true. The OR operator allows you to specify alternative conditions to include rows in the result set. Here’s an explanation with an example:

Consider a table named “employees” with columns: id, first_name, last_name, and department. Here’s a sample data:

+----+------------+-----------+------------+
| id | first_name | last_name | department |
+----+------------+-----------+------------+
| 1  | John       | Smith     | Sales      |
| 2  | Jane       | Doe       | HR         |
| 3  | Alex       | Johnson   | IT         |
| 4  | Lisa       | Brown     | Sales      |
+----+------------+-----------+------------+

To retrieve rows from this table that meet either of two conditions, you can use the OR operator in the WHERE clause of a SQL query. Here’s an example:

SELECT * FROM employees WHERE department = 'Sales' OR department = 'HR';

Output:

+----+------------+-----------+------------+
| id | first_name | last_name | department |
+----+------------+-----------+------------+
| 1  | John       | Smith     | Sales      |
| 2  | Jane       | Doe       | HR         |
| 4  | Lisa       | Brown     | Sales      |
+----+------------+-----------+------------+

In this example, the SELECT statement retrieves rows from the “employees” table where the department is either ‘Sales’ OR ‘HR’. The OR operator allows either condition to be true for a row to be included in the result set.

The OR operator provides flexibility in defining conditions and allows you to retrieve rows based on multiple alternative conditions. It is commonly used when you want to include rows that satisfy at least one of the specified conditions.

You can combine multiple OR operators with other logical operators, such as AND, to create more complex conditions and fine-tune your query results based on your specific requirements.

SQL: Combining AND & OR With Example

In SQL, you can combine the AND and OR operators to create more complex conditions for filtering data. By using parentheses to group conditions and applying logical operators in a systematic manner, you can construct powerful queries. Here’s an explanation with an example:

Consider a table named “employees” with columns: id, first_name, last_name, and department. Here’s a sample data:

+----+------------+-----------+------------+
| id | first_name | last_name | department |
+----+------------+-----------+------------+
| 1  | John       | Smith     | Sales      |
| 2  | Jane       | Doe       | HR         |
| 3  | Alex       | Johnson   | IT         |
| 4  | Lisa       | Brown     | Sales      |
+----+------------+-----------+------------+

To retrieve rows that meet specific conditions using both the AND and OR operators, you can use parentheses to group the conditions and define the logic. Here’s an example:

SELECT * FROM employees WHERE (department = 'Sales' AND age > 25) OR (department = 'HR');

Output:

+----+------------+-----------+------------+
| id | first_name | last_name | department |
+----+------------+-----------+------------+
| 1  | John       | Smith     | Sales      |
| 2  | Jane       | Doe       | HR         |
| 4  | Lisa       | Brown     | Sales      |
+----+------------+-----------+------------+

In this example, the SELECT statement retrieves rows from the “employees” table where either of the two conditions is met:

Rows where the department is ‘Sales’ AND the age is greater than 25.
Rows where the department is ‘HR’.

By grouping the conditions within parentheses, you can control the order of evaluation and specify the logical relationship between the conditions. In this case, the rows are included in the result set if either of the two conditions is true.

By combining AND and OR operators, you can create complex conditions to filter data based on various criteria. It allows you to specify intricate conditions and retrieve the desired rows that meet your specific requirements.

SQL: IN Operator with Example

In SQL, the IN operator is used to check if a value matches any value in a specified list. It allows you to specify multiple values as a list and check if a column value matches any of the values in that list. Here’s an explanation with an example:

Consider a table named “employees” with columns: id, first_name, last_name, and department. Here’s a sample data:

+----+------------+-----------+------------+
| id | first_name | last_name | department |
+----+------------+-----------+------------+
| 1  | John       | Smith     | Sales      |
| 2  | Jane       | Doe       | HR         |
| 3  | Alex       | Johnson   | IT         |
| 4  | Lisa       | Brown     | Sales      |
+----+------------+-----------+------------+

To retrieve rows that match specific values using the IN operator, you can specify a list of values and check if a column value matches any value in that list. Here’s an example:

SELECT * FROM employees WHERE department IN ('Sales', 'HR');

Output:

+----+------------+-----------+------------+
| id | first_name | last_name | department |
+----+------------+-----------+------------+
| 1  | John       | Smith     | Sales      |
| 2  | Jane       | Doe       | HR         |
| 4  | Lisa       | Brown     | Sales      |
+----+------------+-----------+------------+

In this example, the SELECT statement retrieves rows from the “employees” table where the department matches any value in the specified list (‘Sales’ OR ‘HR’). The IN operator checks if the value of the “department” column matches any of the values in the provided list.

The IN operator is useful when you want to filter rows based on multiple specific values for a column. Instead of using multiple OR conditions, you can simply specify the values as a comma-separated list within parentheses after the IN operator.

You can also use subqueries with the IN operator to retrieve values from another table or subquery result. This allows for more dynamic filtering based on the results of another query.

The IN operator provides a concise and efficient way to match values against a list of options, making it a valuable tool for filtering data in SQL queries.

SQL: NOT IN Operator With Example

In SQL, the NOT IN operator is used to check if a value does not match any value in a specified list. It is the negation of the IN operator. The NOT IN operator allows you to specify a list of values and check if a column value does not match any of the values in that list. Here’s an explanation with an example:

Consider a table named “employees” with columns: id, first_name, last_name, and department. Here’s a sample data:

+----+------------+-----------+------------+
| id | first_name | last_name | department |
+----+------------+-----------+------------+
| 1  | John       | Smith     | Sales      |
| 2  | Jane       | Doe       | HR         |
| 3  | Alex       | Johnson   | IT         |
| 4  | Lisa       | Brown     | Sales      |
+----+------------+-----------+------------+

To retrieve rows that do not match specific values using the NOT IN operator, you can specify a list of values and check if a column value does not match any value in that list. Here’s an example:

SELECT * FROM employees WHERE department NOT IN ('Sales', 'HR');

Output:

+----+------------+-----------+------------+
| id | first_name | last_name | department |
+----+------------+-----------+------------+
| 3  | Alex       | Johnson   | IT         |
+----+------------+-----------+------------+

In this example, the SELECT statement retrieves rows from the “employees” table where the department does not match any value in the specified list (‘Sales’ OR ‘HR’). The NOT IN operator checks if the value of the “department” column does not match any of the values in the provided list.

The NOT IN operator is useful when you want to exclude rows based on specific values for a column. It allows you to easily specify the values to be excluded without the need for multiple negated OR conditions.

You can also use subqueries with the NOT IN operator to retrieve values from another table or subquery result. This provides more dynamic filtering based on the results of another query.

The NOT IN operator provides a straightforward and efficient way to exclude rows that match specific values from a result set, making it a valuable tool for data filtering in SQL queries.

amit verma

Website | + posts

Technical content writer with data scientist, artificial intelligence, programming language, database. He has a bachelor’s degree in IT and a certificate in digital marketing, Digital transformation web development android app development He has written for website like Boomi techie, tech mantra, information hub, Tech all