CODEWITHSUNDEEP
mysqldatabaseweb
Tim
Tim

Reputation: 13

Why does this DATE operator not work on my query?

Can anyone explain to me why during my attempts to select data with a date greater than a specific date it runs the query but I still see dates after 2020-09-01? Due_date is a Date and is stored YYYY-MM-DD on a MySQL DB.

Select
        *
    from
        records
    where
        job_id = 13582
        OR job_id = 13583
        OR job_id = 13698
        OR job_id = 13699
        OR job_id = 13700
        OR job_id = 13738
        OR job_id = 14133
        AND due_date NOT IN (
            Select
                due_date
            from
                records
            where
                due_date > date('2020-09-01')
        );

Upvotes: 0

Views: 30

Answers (1)

Conor
Conor

Reputation: 167

Pay careful attention to how you use ANDs and ORs. And has higher priority than OR does, a bit like how * has priority over + in arithmetic.

That means that your query is equivalent to

SELECT
  *
FROM
  records
WHERE
  job_id = 13582
  OR job_id = 13583
  OR job_id = 13698
  OR job_id = 13699
  OR job_id = 13700
  OR job_id = 13738
  OR (job_id = 14133 AND due_date NOT IN (
    Select
      due_date
    from
      records
    where
      due_date > date('2020-09-01')
    )
  );

When what you probably meant is

SELECT
  *
FROM
  records
WHERE
  (job_id = 13582
  OR job_id = 13583
  OR job_id = 13698
  OR job_id = 13699
  OR job_id = 13700
  OR job_id = 13738
  OR job_id = 14133)
  AND due_date NOT IN (
    Select
      due_date
    from
      records
    where
      due_date > date('2020-09-01')
    );

A good way to avoid this is, rather than using so many OR statements, use one IN statement:

SELECT
  *
FROM
  records
WHERE
  job_id IN (13582, 13583, 13698, 13699, 13700, 13738, 14133)
  AND due_date NOT IN (
    Select
      due_date
    from
      records
    where
      due_date > date('2020-09-01')
    );

And an even better solution would be

SELECT
  *
FROM
  records
WHERE
  job_id IN (13582, 13583, 13698, 13699, 13700, 13738, 14133)
  AND due_date <= date('2020-09-01');

Upvotes: 1

Related Questions