Using Clause

Introduction

• In PostgreSQL (and standard SQL in general), Clause is a structural component, a small block of code that forms a complete SQL statement (such as SELECT, INSERT, UPDATE, DELETE).
• Each Clause begins with a designated keyword and handles a single task in the data processing lifecycle, helping you deal with everything from basic tasks like sorting and pagination to advanced techniques like recursive programming, asynchronous processing, or performance optimization.
• You can imagine an SQL statement like a train and the Clauses are the train cars hooked together in a strict logical order to transport and process data.

Below are the important clauses supported by Postgres, divided by purpose of use:

Selection & Duplicate Elimination Clauses

• DISTINCT is a clause located right within the SELECT statement used for Output Formatting/Filtering at the row level.
  • It only operates at the final stage of the query process (after data has been filtered, aggregated and calculated) to scan through the result set and remove duplicate rows before returning them to the user.
• DISTINCT ON(col1, col2, ...): This is an extremely powerful tool unique to PostgreSQL. Unlike regular DISTINCT (which considers all columns in SELECT), DISTINCT ON allows you to eliminate duplicates based on a few specific columns, while still retrieving data from other columns.

Filtering Clauses

• WHERE: The most basic filtering clause, removing non-compliant rows right from the raw data reading step, before any calculation or grouping occurs.
• HAVING: Used to filter data after grouping.
  • It operates based on the results of aggregate functions (SUM, COUNT,...) and usually immediately follows GROUP BY.
  • It is often confused with WHERE. The difference is:
    • WHERE: This is a Row-level filter used to filter data for individual rows before grouping.
    • HAVING: This is a Group-level filter used to filter data for the entire group after the data has been aggregated by GROUP BY.
• FILTER (WHERE): A filtering clause attached directly to aggregate functions. It allows you to calculate multiple different filtering conditions within the same SELECT statement.

Set Operators Group

Used to compare and process the results of two SELECT statements.

• UNION: A Set Operator used to stack the results of one SELECT statement on top of the results of another SELECT statement, as long as those SELECT statements return the same number of columns and matching data types in order.
  • UNION: Retrieves all rows appearing in both tables, then removes duplicates. Performance is slower, because Postgres must spend overhead checking, sorting or hashing data to find and remove duplicate rows.
  • UNION ALL: Retains all rows, including identical ones. Performance is much faster because Postgres simply merges the two datasets together without further processing.
• INTERSECT: Returns the intersection of two query statements, taking only rows that appear in both results.
• EXCEPT: Returns the difference, taking rows that appear in the results of statement 1 but do not exist in the results of statement 2.

CTE (Common Table Expressions) Group

• WITH AS: Allows the definition of one or more temporary tables (existing only within the scope of that statement) to be reused multiple times, making the SQL statement clean and much easier to read compared to using complex nested Subquery statements.
• WITH RECURSIVE: An extremely powerful clause used to write recursive queries, typically used to traverse tree or hierarchical data structures.

Aggregation Control Clauses

• Also known as Grouping Clauses, this group includes GROUP BY and its advanced extensions like ROLLUP, CUBE, GROUPING SETS. The sole task of this group is to change the structure (frame of reference) of the dataset.
• It transforms data from a Row-level format into a Group-level format based on identical criteria.

Detail

Please create the tables as follows:

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    stock INT NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    order_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    total_amount NUMERIC(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL
);

CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_id INT REFERENCES products(product_id),
    quantity INT NOT NULL,
    price_per_unit NUMERIC(10, 2) NOT NULL
);

CREATE TABLE product_views (
    view_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT REFERENCES products(product_id),
    view_time TIMESTAMP NOT NULL
);

CREATE TABLE marketing_rewards (
    reward_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    reward_name VARCHAR(100),
    granted_date DATE
);

• The product_views table is used to track the products users have clicked to view, dedicated to Marketing problems.
• The marketing_rewards table is used to record the list of customers receiving gifts from minigames or appreciation events.

DISTINCT

-- Query 1
SELECT DISTINCT user_id 
FROM orders 
WHERE status = 'completed'
ORDER BY user_id;

-- Query 2
SELECT DISTINCT ON (user_id) 
    user_id, 
    product_id, 
    view_time
FROM product_views
ORDER BY user_id, view_time ASC;

• Query 1: Retrieves a list of unique user IDs who have successfully purchased items.
• Query 2: Finds the first product that each user viewed, using DISTINCT ON to display information from multiple columns which regular DISTINCT cannot achieve.

HAVING & FILTER (WHERE)

-- Query 1
SELECT 
    'Mobile' AS department,
    SUM(stock) AS total_remaining_stock
FROM products
WHERE category = 'Mobile'
HAVING SUM(stock) < 50;

-- Query 2
SELECT 
    user_id, 
    SUM(total_amount) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY user_id
HAVING SUM(total_amount) > 1000;

-- Query 3
SELECT 
    p.category,
    SUM(oi.quantity * oi.price_per_unit) FILTER (WHERE o.status = 'completed') AS net_revenue,
    SUM(oi.quantity * oi.price_per_unit) FILTER (WHERE o.status = 'cancelled') AS cancelled_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN orders o ON oi.order_id = o.order_id
GROUP BY p.category;

• Query 1: This is an example of using HAVING without GROUP BY, used to check inventory for a single category.
• Query 2: Finds customers with a total successful purchase value greater than 1,000.
• Query 3: Compiles revenue statistics for each category, separately calculating actual revenue (completed) and cancelled revenue on the same report line.

UNION, INTERSECT, EXCEPT

-- Query 1
SELECT user_id FROM orders
UNION
SELECT user_id FROM product_views;

-- Query 2
SELECT user_id, 'order' AS activity_type FROM orders
UNION ALL
SELECT user_id, 'reward' AS activity_type FROM marketing_rewards;

-- Query 3
SELECT user_id FROM orders WHERE status = 'completed'
INTERSECT
SELECT user_id FROM product_views;

-- Query 4
SELECT user_id FROM product_views
EXCEPT
SELECT user_id FROM orders;

• Query 1: Retrieves a list of users as a merged result of all users who have initiated a purchase action (with any status) OR performed a product view action (Duplicates removed).
• Query 2: Similar to Query 1 but without removing duplicates, to count the number of times users interacted with the system.
• Query 3: Finds users who have both a completed order and a product view (must satisfy both conditions).
• Query 4: Finds potential users who are those who have viewed products but have not placed any orders yet.

Happy coding!

See more articles here.