PostgreSQL Simple Queries (Using WHERE Clause, IN Subquery, LIKE Operator, etc.)

Basic Concepts

In PostgreSQL, simple queries are basic operations used to retrieve data.

1. WHERE Clause

The WHERE clause is used for conditional filtering of data, such as:

SELECT * FROM table_name WHERE condition;

For example:

SELECT * FROM customers WHERE country='USA';

This SQL statement returns customer information located in the USA.

2. IN Clause

The IN clause is used to retrieve data from a specified set of values. For example:

SELECT * FROM table_name WHERE value IN (column, value);

For example:

SELECT * FROM customers WHERE country IN ('USA', 'Canada');

This SQL statement returns customer information located in the USA and Canada.

3. LIKE Operator

The LIKE operator is used to match data patterns. For example:

SELECT * FROM table_name WHERE field LIKE '%feature%';

For example:

SELECT * FROM customers WHERE name LIKE '%John%';

This SQL statement returns customer information containing the feature "John".

4. AND, OR, NOT Operators

The AND, OR, and NOT operators are used to combine conditions, such as:

SELECT * FROM table_name WHERE condition1 AND condition2;

For example:

SELECT * FROM customers WHERE country='USA' AND age>18;

This SQL statement returns customer information located in the USA with an age greater than 18.

Examples

5. Example of Querying Data

-- Set up a table: customers

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    country VARCHAR(255),
    age INTEGER
);

-- Add data

INSERT INTO customers (name, country, age) VALUES 
('John', 'USA', 25), 
('Jane', 'Canada', 30), 
('Bob', 'UK', 20);

-- Query data using WHERE clause

SELECT * FROM customers WHERE country='USA';

-- Query data using IN clause

SELECT * FROM customers WHERE country IN ('USA', 'Canada');

-- Query data using LIKE operator

SELECT * FROM customers WHERE name LIKE '%John%';

6. Multi-Table Joins

Multi-table joins are used to connect two or more tables to retrieve more detailed information. For example:

-- Set up two tables: customers and orders

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE
);

INSERT INTO orders (customer_id, order_date) VALUES 
(1, '2022-01-01'), 
(1, '2022-01-15'), 
(2, '2022-02-01');

-- Perform multi-table join using WHERE clause

SELECT * FROM customers JOIN orders ON customers.id=orders.customer_id;

Conclusion

Simple queries (using WHERE clauses, IN clauses, LIKE operators, etc.) are basic operations in PostgreSQL used to retrieve data. These methods help you filter and match data based on different conditions and can be used in multi-table joins to obtain more detailed information.