如何用SQL查询近30天未下单的客户列表？

Alright, let's figure out how to get those customers who haven't placed any orders in the last 30 days using your two tables. Here are a few solid, efficient approaches to write the SQL query:

1. Use NOT EXISTS (Recommended for Better Performance) #

This is usually the most efficient method because most database optimizers handle NOT EXISTS really well, especially if you have indexes on laces_order.laces_user_id and laces_order.order_date. It checks if a customer has any orders in the last 30 days—if not, they're included in the result.

SELECT up.*
FROM laces_users_profile up
WHERE NOT EXISTS (
    SELECT 1  -- We don't need actual data here, just an existence check
    FROM laces_order o
    WHERE o.laces_user_id = up.laces_user_id
      AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
);

Note: Adjust the date syntax based on your database:

• MySQL: Replace with CURDATE() - INTERVAL 30 DAY
• SQL Server: Use DATEADD(day, -30, GETDATE())
• Oracle: Use SYSDATE - 30

2. Use LEFT JOIN + IS NULL #

This approach joins the customer table with recent orders, then filters out customers who have no matching order records (indicated by NULL values in the order table columns).

SELECT up.*
FROM laces_users_profile up
LEFT JOIN laces_order o
    ON up.laces_user_id = o.laces_user_id
    AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
WHERE o.laces_user_id IS NULL;

Important: Make sure the date condition is in the ON clause, not the WHERE clause—putting it in WHERE would turn this into an inner join and give incorrect results.

3. Use NOT IN with a Subquery #

This method first gets all user IDs that have placed orders in the last 30 days, then excludes those IDs from the customer table.

SELECT *
FROM laces_users_profile
WHERE laces_user_id NOT IN (
    SELECT DISTINCT laces_user_id
    FROM laces_order
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
);

Caution: If laces_order.laces_user_id ever has NULL values, NOT IN will return an empty result set. For this reason, NOT EXISTS or LEFT JOIN are safer choices.

Pro Tips #

• Add indexes on laces_order.laces_user_id and laces_order.order_date to speed up the query, especially if you're working with large datasets.
• Double-check that order_date stores the actual date/time the order was placed—using a timestamp field here is fine, as the date comparison will still work.

内容的提问来源于stack exchange，提问作者Meet Patel