PostgreSQL OR Operator

PostgreSQL OR Operator

The PostgreSQL OR operator is used to combine multiple conditions in a query. This operator is essential for filtering query results where at least one of the specified conditions must be true.

---

Syntax

SELECT columns
FROM table_name
WHERE condition1 OR condition2;

The OR operator has the following components:

• columns: The columns to be retrieved from the table.
• table_name: The table from which to retrieve the data.
• condition1: The first condition to be met.
• condition2: The second condition to be met.

---

Example PostgreSQL OR Queries

Let's look at some examples of PostgreSQL OR operator queries:

1. Basic OR Example

SELECT customer_id, customer_name
FROM customers
WHERE customer_id = 1 OR customer_id = 3;

This query retrieves the customer_id and customer_name from the customers table where the customer_id is either 1 or 3.

2. OR with Multiple Conditions

SELECT product_id, product_name
FROM products
WHERE price < 30.00 OR price > 90.00 OR in_stock = false;

This query retrieves the product_id and product_name from the products table where the price is less than 30.00, greater than 90.00, or the product is not in stock.

3. OR with Date Comparison

SELECT order_id, order_date
FROM orders
WHERE order_date < '2024-01-01' OR order_date > '2024-12-31';

This query retrieves the order_id and order_date from the orders table where the order_date is before '2024-01-01' or after '2024-12-31'.

4. OR with String Comparison

SELECT customer_id, customer_name
FROM customers
WHERE customer_name = 'John Doe' OR customer_name = 'Jane Smith';

This query retrieves the customer_id and customer_name from the customers table where the customer_name is either 'John Doe' or 'Jane Smith'.

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the OR operator to filter data.

Step 1: Creating a Table

This step involves creating a new table named products to store product data.

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    price NUMERIC(10, 2),
    in_stock BOOLEAN
);

In this example, we create a table named products with columns for product_id, product_name, price, and in_stock.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the products table.

INSERT INTO products (product_name, price, in_stock)
VALUES ('Product A', 30.00, true),
       ('Product B', 60.00, false),
       ('Product C', 90.00, true);

Here, we insert data into the products table.

Step 3: Using the OR Operator

This step involves using the OR operator to filter data from the products table.

-- Basic OR
SELECT product_id, product_name
FROM products
WHERE price = 30.00 OR price = 90.00;

-- OR with Multiple Conditions
SELECT product_id, product_name
FROM products
WHERE price < 30.00 OR price > 90.00 OR in_stock = false;

-- OR with Date Comparison
SELECT order_id, order_date
FROM orders
WHERE order_date < '2024-01-01' OR order_date > '2024-12-31';

-- OR with String Comparison
SELECT customer_id, customer_name
FROM customers
WHERE customer_name = 'John Doe' OR customer_name = 'Jane Smith';

These queries demonstrate how to use the OR operator to filter data from the products table, including basic disjunctions, multiple conditions, date comparisons, and string comparisons.

Conclusion

The PostgreSQL OR operator is a fundamental tool for combining multiple conditions and filtering query results where at least one of the specified conditions must be true. Understanding how to use the OR operator and its syntax is essential for effective data retrieval and manipulation in PostgreSQL databases.