MySQL Query in bad way

Question

I have below query to check customer subscription. This is not quite right way to do in query but I do not know how to optimize or correct it. Here it is.

SELECT sub_id FROM subscription 
WHERE start_date = CURDATE()
AND end_date > CURDATE()
AND sub_id NOT IN (SELECT DISTINCT sub_id FROM subscription
WHERE start_date < CURDATE());

The reason of sub query is to sieve out sub_id previously did at least a subscription.

I have almost zero experience in MySQL but your syntax looks like you are doing essentially the same WHERE clause twice. In your main you already eliminate any records with start_date <> CURDATE(); why do you have a sub query which - essentially - eliminates any records already eliminated in your main query? — John, Jul 24 '17 at 04:00
looking for new customer. "WHERE start_date < CURDATE());" is old customer. — CPMM, Jul 24 '17 at 04:10
i think you have duplicate condition, `where start_date = CURDATE()` already get the sub_id which started in CURDATE(), and you don't need a subquery `AND sub_id NOT IN (SELECT DISTINCT sub_id FROM subscription WHERE start_date < CURDATE());` to filter out record which is old customer — ProgrammingBaKa, Jul 24 '17 at 04:15
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Jul 24 '17 at 06:21
I think we understand what you are trying to do but your sub-query is redundant. You reference the same table in both queries (subscription) and you filter on the same column (start_date) in both queries. So, in essence your effective WHERE clause becomes WHERE start_date = CURDATE() AND start_date NOT < CURDATE(). HTH. — John, Jul 24 '17 at 19:08
"start_date NOT < CURDATE()" is not correct syntax in MySQL query. what is the correct syntax to write it? — CPMM, Jul 25 '17 at 05:23

score 1 · Answer 1 · answered Jul 24 '17 at 04:16

You don't need SELECT DISTINCT sub_id FROM subscription WHERE start_date < CURDATE() subquery - you already have condition start_date = CURDATE().

SELECT sub_id FROM subscription 
WHERE start_date = CURDATE()
    AND end_date > CURDATE()

This query will select all subscriptions that start on CURDATE() and stop some other day in the future.

MySQL Query in bad way

1 Answers1