SQL Query Examples

SELECT 
    users.user_id, 
    users.name, 
    users.email, 
    orders.order_id, 
    orders.product_name, 
    orders.order_date
FROM 
    users
LEFT JOIN 
    orders ON users.user_id = orders.user_id
ORDER BY 
    users.user_id, orders.order_date;

SELECT DISTINCT
    users.user_id, 
    users.name, 
    users.email
FROM 
    users
INNER JOIN 
    orders ON users.user_id = orders.user_id;

SELECT 
    orders.order_id, 
    orders.product_name, 
    orders.order_date
FROM 
    orders
WHERE 
    orders.order_date >= CURDATE() - INTERVAL 30 DAY
ORDER BY 
    orders.order_date DESC;

SELECT 
    users.user_id, 
    users.name, 
    COUNT(orders.order_id) as order_count
FROM 
    users
LEFT JOIN 
    orders ON users.user_id = orders.user_id
GROUP BY 
    users.user_id, users.name
ORDER BY 
    order_count DESC;

EXPLAIN SELECT 
    users.user_id, 
    users.name, 
    users.email, 
    orders.order_id, 
    orders.product_name, 
    orders.order_date
FROM 
    users
INNER JOIN 
    orders ON users.user_id = orders.user_id
ORDER BY 
    users.user_id, orders.order_date;

CREATE INDEX idx_users_user_id ON users(user_id);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_order_date ON orders(order_date);

SELECT 
    users.user_id, 
    users.name, 
    users.email, 
    orders.order_id, 
    orders.product_name, 
    orders.order_date
FROM 
    users
INNER JOIN 
    orders ON users.user_id = orders.user_id
ORDER BY 
    users.user_id, orders.order_date;