Here’s a list of commonly used SQL comparison operators with brief explanations and examples:
📋 Basic Comparison Operators:
| Operator | Meaning | Example | Result |
|---|---|---|---|
= | Equal to | WHERE age = 25 | Matches rows where age is 25 |
<> | Not equal to (standard) | WHERE status <> 'active' | Matches rows where status is not 'active' |
!= | Not equal to (alternative) | WHERE id != 10 | Same as <>, matches if id is not 10 |
> | Greater than | WHERE salary > 50000 | Matches rows with salary above 50k |
< | Less than | WHERE created_at < '2024-01-01' | Matches dates before Jan 1, 2024 |
>= | Greater than or equal | WHERE age >= 18 | Matches age 18 and above |
<= | Less than or equal | WHERE age <= 65 | Matches age 65 and below |
📋 Other Common Operators:
| Operator | Meaning | Example |
|---|---|---|
BETWEEN | Within a range | WHERE price BETWEEN 100 AND 200 |
IN | Match any value in a list | WHERE country IN ('US', 'CA', 'UK') |
NOT IN | Not in a list | WHERE role NOT IN ('admin', 'staff') |
IS NULL | Value is null | WHERE deleted_at IS NULL |
IS NOT NULL | Value is not null | WHERE updated_at IS NOT NULL |
LIKE | Pattern match (case-insensitive in some DBs) | WHERE name LIKE 'J%' |
ILIKE | Case-insensitive LIKE (PostgreSQL only) | WHERE email ILIKE '%@gmail.com' |
Now we’ve our products and product_variants schema, let’s re-explore all major SQL JOINs using these two related tables.
####### Products
Column | Type | Collation | Nullable | Default
-------------+--------------------------------+-----------+----------+--------------------------------------
id | bigint | | not null | nextval('products_id_seq'::regclass)
description | text | | |
category | character varying | | |
created_at | timestamp(6) without time zone | | not null |
updated_at | timestamp(6) without time zone | | not null |
name | character varying | | not null |
rating | numeric(2,1) | | | 0.0
brand | character varying | | |
######## Product variants
Column | Type | Collation | Nullable | Default
------------------+--------------------------------+-----------+----------+----------------------------------------------
id | bigint | | not null | nextval('product_variants_id_seq'::regclass)
product_id | bigint | | not null |
sku | character varying | | not null |
mrp | numeric(10,2) | | not null |
price | numeric(10,2) | | not null |
discount_percent | numeric(5,2) | | |
size | character varying | | |
color | character varying | | |
stock_quantity | integer | | | 0
specs | jsonb | | not null | '{}'::jsonb
created_at | timestamp(6) without time zone | | not null |
updated_at | timestamp(6) without time zone | | not null |
💎 SQL JOINS with products and product_variants
These tables are related through:
product_variants.product_id → products.id
So we can use that for all join examples.
🔸 1. INNER JOIN – Show only products with variants
SELECT
p.name,
pv.sku,
pv.price
FROM products p
INNER JOIN product_variants pv ON p.id = pv.product_id;
♦️ Only returns products that have at least one variant.
🔸 2. LEFT JOIN – Show all products, with variants if available
SELECT
p.name,
pv.sku,
pv.price
FROM products p
LEFT JOIN product_variants pv ON p.id = pv.product_id;
♦️ Returns all products, even those with no variants (NULLs in variant columns).
🔸 3. RIGHT JOIN – Show all variants, with product info if available
(Less common, but useful if variants might exist without a product record)
SELECT
pv.sku,
pv.price,
p.name
FROM products p
RIGHT JOIN product_variants pv ON p.id = pv.product_id;
🔸 4. FULL OUTER JOIN – All records from both tables
SELECT
p.name AS product_name,
pv.sku AS variant_sku
FROM products p
FULL OUTER JOIN product_variants pv ON p.id = pv.product_id;
♦️ Shows all products and all variants, even when there’s no match.
🔸 5. SELF JOIN Example (for product_variants comparing similar sizes or prices)
Let’s compare variants of the same product that are different sizes.
SELECT
pv1.product_id,
pv1.size AS size_1,
pv2.size AS size_2,
pv1.sku AS sku_1,
pv2.sku AS sku_2
FROM product_variants pv1
JOIN product_variants pv2
ON pv1.product_id = pv2.product_id
AND pv1.size <> pv2.size
WHERE pv1.product_id = 101; -- example product
♦️ Useful to analyze size comparisons or price differences within a product.
🧬 Complex Combined JOIN Example
Show each product with its variants, and include only discounted ones (price < MRP):
SELECT
p.name AS product_name,
pv.sku,
pv.price,
pv.mrp,
(pv.mrp - pv.price) AS discount_value
FROM products p
INNER JOIN product_variants pv ON p.id = pv.product_id
WHERE pv.price < pv.mrp
ORDER BY discount_value DESC;
📑 JOIN Summary with These Tables
| JOIN Type | Use Case |
|---|---|
INNER JOIN | Only products with variants |
LEFT JOIN | All products, even if they don’t have variants |
RIGHT JOIN | All variants, even if product is missing |
FULL OUTER JOIN | Everything — useful in data audits |
SELF JOIN | Compare or relate rows within the same table |
Let’s now look at JOIN queries with more realistic conditions using products and product_variants.
🦾 Advanced JOIN Queries with Conditions to practice
🔹 1. All products with variants in stock AND discounted
SELECT
p.name AS product_name,
pv.sku,
pv.size,
pv.color,
pv.stock_quantity,
pv.mrp,
pv.price,
(pv.mrp - pv.price) AS discount_amount
FROM products p
JOIN product_variants pv ON p.id = pv.product_id
WHERE pv.stock_quantity > 0
AND pv.price < pv.mrp
ORDER BY discount_amount DESC;
♦️ Shows available discounted variants, ordered by discount.
🔹 2. Products with high rating (4.5+) and at least one low-stock variant (< 10 items)
SELECT
p.name AS product_name,
p.rating,
pv.sku,
pv.stock_quantity
FROM products p
JOIN product_variants pv ON p.id = pv.product_id
WHERE p.rating >= 4.5
AND pv.stock_quantity < 10;
🔹 3. LEFT JOIN to find products with no variants or all variants out of stock
SELECT
p.name AS product_name,
pv.id AS variant_id,
pv.stock_quantity
FROM products p
LEFT JOIN product_variants pv
ON p.id = pv.product_id AND pv.stock_quantity > 0
WHERE pv.id IS NULL;
✅ This tells you:
- Either the product has no variants
- Or all variants are out of stock
🔹 4. Group and Count Variants per Product
SELECT
p.name AS product_name,
COUNT(pv.id) AS variant_count
FROM products p
LEFT JOIN product_variants pv ON p.id = pv.product_id
GROUP BY p.name
ORDER BY variant_count DESC;
🔹 5. Variants with price-percentage discount more than 30%
SELECT
p.name AS product_name,
pv.sku,
pv.mrp,
pv.price,
ROUND(100.0 * (pv.mrp - pv.price) / pv.mrp, 2) AS discount_percent
FROM products p
JOIN product_variants pv ON p.id = pv.product_id
WHERE pv.price < pv.mrp
AND (100.0 * (pv.mrp - pv.price) / pv.mrp) > 30;
🔹 6. Color-wise stock summary for a product category
SELECT
p.category,
pv.color,
SUM(pv.stock_quantity) AS total_stock
FROM products p
JOIN product_variants pv ON p.id = pv.product_id
WHERE p.category = 'Shoes'
GROUP BY p.category, pv.color
ORDER BY total_stock DESC;
These queries simulate real-world dashboard views: inventory tracking, product health, stock alerts, etc.
Happy SQL Query Writing! 🚀
The Rails Drop 
One thought on “Rails 8 App: Setup Test DB in PostgreSQL | Write SQL Queries | Operators | Joins”