PostgreSQL OR Operator

PostgreSQL is an advanced relational database system that supports both relational (SQL) and non-relational (JSON) queries. It is free and open-source. PostgreSQL, one of the most used relational database management systems, has many operators supporting manipulation and querying data in various ways.

The operators stand side by side with each other and the OR operator is the most effective one when they need to create queries that are a bit detailed. The article focused on the OR operation in PostgreSQL and covered the syntax and the most appropriate ways to use the operator.

OR Operator

PostgreSQL OR operator is a logical operator that lets you connect several conditions in the WHERE clause to get the rows that are related to at least one of the specified ones. It returns true if any of the given conditions found by the ‘OR’ operator has the truth as its result.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

Here, condition1, condition2, etc., are the conditions you want to evaluate using the OR operator. You can have as many conditions as needed, separated by OR.

Queries using OR Operator

Let’s create a table in PostgreSQL insert some data into it and then perform some queries using the OR operator.

Create a table named as employees:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    salary NUMERIC(10, 2)
);

Now, let’s insert some sample data into the employees table:

INSERT INTO employees (name, department, salary)
VALUES
    ('Minal Pandey', 'Sales', 60000),
    ('Vivek Sharma', 'Marketing', 55000),
    ('Priyanshi Sharma', 'Sales', 48000),
    ('Vardhana Sharma', 'Engineering', 70000);

Output:

You can check whether the table is successfully created or not using the below command:

SELECT * FROM employees;

Employees Table

Examples of PostgreSQL OR Operator

Example 1: Retrieve Employees in Sales Departments or with salaries higher than 55000.

Query:

The query below selects all of the “employees” table’s items if the department is ‘Sales‘ or if the salary is more than 55000.

SELECT *
FROM employees
WHERE department = 'Sales' OR  salary > 55000

Output:

You can see that query returns up every row in the “employees” table, whether the department name is “Sales” or if the salary is more than 55000.

Example 1

Example 2: Retrieve the name of the employees in the Sales Departments or marketing department or with salaries higher than 55000.

Query:

This query selects the names of employees from the “employees” table where the department is either ‘Sales‘ or ‘Marketing‘, or where the salary is greater than 55000.

SELECT name
FROM employees
WHERE department = 'Sales' OR department = 'Marketing' OR salary > 55000;

Output:

You can see that the query returned the names of employees who work in the ‘Sales‘ or ‘Marketing‘ department, or whose salary is greater than 55000.

Example 2

Example 3: Retrieve Employees in Marketing Departments or with salaries higher than 55000.

Query:

The query below selects all of “employees” table’s items if the department is ‘Marketing‘ or if the salary is more than 55000.

SELECT *
FROM employees
WHERE department = 'Marketing' OR salary > 55000;

Output:

You can see that the query returned all information about employees who work in the ‘Marketing’ department or have a salary greater than 55000.

Example 3

Best Practices

Parentheses for Clarity: When using OR operators along with other operators such as AND, it is suggested that parentheses be used to define the ordering of operations explicitly. This boosts readability and contributes to the query to do what it should do irrespective.
Optimize Performance: Although the OR operator is a wonderful tool, it can slow down the query load or compound it with the rest of the conditions (complex ones). Make sure that your project is optimized, perhaps by the addition of indexes on the columns that are used in the conditions.
Consider Alternatives: For instance, using IN operator or reorganizing the logic of the query could be the best option for fast processing and clear presentation rather than using many OR conditions.
Use with Caution in Joins: It is worth bearing in mind that OR may be replaced by Join conditions in queries. However, it is essential to understand how they may affect performance, especially with large databases. Judge whether it is required to use the OR keyword in join conditions and, if required, look into different methods of joining tables.

Conclusion

The OR operator of PostgreSQL is about constructing advanced queries by making up more than one condition to retrieve rows that meet the condition of at least one. Besides the syntax, usage, and best practices knowledge, they equip you to create fast and apt SQL queries on PostgreSQL databases. By using OR operator with other SQL constructs appropriately, you may goose up your query performance to always get and display only the required data.

Article Tags :

Databases

PostgreSQL