Finding Products Not Ordered by Any Customer

This query will identify products that have never been ordered by any customer. Let me explain this in detail with dummy data, the query, and expected output.

Database Structure

We'll need at least two tables for this analysis:

1. products - contains product information
2. orders - contains order details including which products were ordered

Sample Tables with Dummy Data

-- Create products table
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2)
);

-- Create orders table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    order_date DATE,
    quantity INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Insert dummy data into products
INSERT INTO products VALUES 
(1, 'Laptop', 999.99),
(2, 'Smartphone', 699.99),
(3, 'Headphones', 149.99),
(4, 'Smartwatch', 249.99),
(5, 'Tablet', 399.99),
(6, 'Bluetooth Speaker', 79.99),
(7, 'Camera', 499.99);

-- Insert dummy data into orders
INSERT INTO orders VALUES
(101, 1, 1, '2023-01-15', 1),
(102, 2, 2, '2023-01-20', 2),
(103, 3, 1, '2023-02-05', 1),
(104, 1, 3, '2023-02-10', 1),
(105, 4, 2, '2023-03-01', 1),
(106, 5, 5, '2023-03-15', 1),
(107, 2, 4, '2023-04-01', 1);

The Query

There are several ways to find products not ordered by any customer. Here are three common approaches:

Method 1: Using LEFT JOIN with NULL check

SELECT p.product_id, p.product_name, p.price
FROM products p
LEFT JOIN orders o ON p.product_id = o.product_id
WHERE o.product_id IS NULL;

Method 2: Using NOT IN

SELECT product_id, product_name, price
FROM products
WHERE product_id NOT IN (SELECT DISTINCT product_id FROM orders);

Method 3: Using NOT EXISTS

SELECT product_id, product_name, price
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.product_id = p.product_id);

Expected Output

Based on our dummy data:

• Products ordered: Laptop (ID 1), Smartphone (ID 2), Headphones (ID 3), Smartwatch (ID 4), Tablet (ID 5)
• Products not ordered: Bluetooth Speaker (ID 6), Camera (ID 7)

The output should be:

product_id | product_name      | price
-----------+-------------------+--------
6         | Bluetooth Speaker |  79.99
7         | Camera            | 499.99

Explanation

1. LEFT JOIN approach:

   • We join all products with their corresponding orders (if any)
   • Products without matching orders will have NULL values in the order columns
   • The WHERE clause filters for these NULL values

2. NOT IN approach:

   • The subquery gets all product_ids that appear in orders
   • The main query selects products whose IDs are not in that list

3. NOT EXISTS approach:

   • For each product, checks if there's no matching record in orders
   • More efficient than NOT IN for large datasets as it stops at first match

All three methods will produce the same result, but performance may vary based on your database size and structure. The LEFT JOIN method is often the most efficient for this type of query.