I am using NOT IN and also IF in Where Clause of subquery MySQL is it right way?

Question

Working Query: My query is working and I am also getting the result I desired. I just want to know if I am doing anything wrong because I heard it is not the right way to use IF inside Where Clause but in my case, it is even in a subquery. I have made this query myself. Let me know if there is a better alternative for my query.

Looking for: I am trying to get all those employees who are not busy between 14:00 & 15:00 where 14:00 and 15:00 is a time coming from input fields(like a search).

 SELECT * 
   FROM `schedule` 
  WHERE appointment_id NOT IN (
          SELECT appointment_id 
            FROM `schedule` 
           WHERE ( IF(start_time < '14:00', '14:00', start_time) >= '14:00' 
             AND   IF(end_time > '15:00', '15:00', end_time) >= '14:00') 
             AND  (IF(start_time < '14:00', '14:00', start_time) <= '15:00' 
             AND   IF(end_time > '15:00', '15:00', end_time) <= '15:00') 
             AND  `appoint_date` = '2018-11-30')

I don't see how this query finds employees. You need something like _SELECT FROM employees WHERE NOT EXISTS (a schedule at which that employee is busy during 2 and 3PM)_ — Salman A, Nov 23 '18 at 08:38
@salman I am finding employees who are not having any appointment in the time range user searched. — Sayed Mohd Ali, Nov 23 '18 at 08:42
It's just that you say you want employees, but you're returning schedules. But I assume that schedule table has something like an employee_id. So you could join it to your table with the employees. And probably group on the employee_id (or whatever it's called). — LukStorms, Nov 23 '18 at 08:50
yes, schedule table has employee_id and I am also using join with employee table to find employee data. but this part of the query is just to get the employee id of those employees who do not have any appointment in the searched time. and also I am doing UNION of my query with another query which is simply find the employees id of the employee who do not exist in the appointment table that means they are also not having any appointment. — Sayed Mohd Ali, Nov 23 '18 at 09:00
Btw, is a schedule with a range between '13:00' and '16:00' also considered as busy between '14:00' and '15:00'? — LukStorms, Nov 23 '18 at 09:01

Tim Biegeleisen · Accepted Answer · 2018-11-23T10:17:01.087

3

I don't even see the point of the IF calls, and we may rewrite your query without them:

SELECT * 
FROM schedule 
WHERE appointment_id NOT IN (
    SELECT appointment_id 
    FROM schedule
    WHERE
        end_time     >= '14:00' AND -- this
        start_time   <= '15:00' AND -- and this tests for all possible cases of overlap
        appoint_date = '2018-11-30');

I was able to remove two of the conditions in your WHERE clause because they would always be true. Here is one of the two examples:

IF (start_time < '14:00', '14:00', start_time) >= '14:00'

This will always be true, because any time which is 14:00 or earlier gets bumped up to 14:00. So, such times would always be >= 14:00. Similarly, any time already greater than 14:00 would also pass the check. So, this entire term can be removed, as well as one other term.

edited Nov 23 '18 at 10:17

answered Nov 23 '18 at 08:23

Tim Biegeleisen

502,043
27
286
360

@Salman Thanks for the edit. I hope to also leave little green bundles of joy in your inbox at some point. – Tim Biegeleisen Nov 23 '18 at 08:34
IF is important without it I will not get the desired result the if is used because there may be some employees who will be busy a whole day and user search for 14:00 and 15:00, in that case, he will also get those employees who are busy a whole day. I don't want any employees who is busy. – Sayed Mohd Ali Nov 23 '18 at 08:38
@Sayed nah. it is bullet proof. – Salman A Nov 23 '18 at 08:39
@SalmanA It may be bullet proof but it isn't null proof. If, permaybehaps, a null time is used to signify indefinitely within the day, with both start and end nulls meaning all day, then it fails. `(end_time IS NULL OR end_time >= '14:00') AND (start_time IS NULL OR start_time <= '15:00')` would cover that case. – Arth Nov 23 '18 at 09:44
2

@SayedMohdAli This answer features the standard way to check for schedule clashes.. check the operators, it basically says does an appointment end AFTER the start time AND start BEFORE the end time? This gives the same logic as your IF magic – Arth Nov 23 '18 at 09:51
@Arth thanks for the explanation, tim biegeleisen gave answer but not enough explanation. – Sayed Mohd Ali Nov 23 '18 at 10:13
@SayedMohdAli I added an explanation of why it was possible to completely remove some of the conditions from your `WHERE` clause. – Tim Biegeleisen Nov 23 '18 at 10:17
@TimBiegeleisen beat me to it! – Arth Nov 23 '18 at 10:20
Actually the logic is explained here: https://stackoverflow.com/q/325933/87015. Most people think they need to check 6 cases for overlap whereas only 2 are enough. PS: you might want to change >= to > (not exactly sure) if end time is exclusive (i.e. 14:00-15:00 and 15:00-16:00 are considered non-overlapping). – Salman A Nov 23 '18 at 10:40
@Arth thanks for the explanation, I already got it but will rate you later I do not have points today. – Sayed Mohd Ali Nov 23 '18 at 10:48

LukStorms · Answer 2 · 2018-11-23T09:33:17.097

0

That IF can be usefull to default to a fixed time in the SELECT.
But as a criteria, using it to set a default is just overhead.

If you also don't want to include those that start before 14:00 and end after 15:00?
Then try this:

SELECT DISTINCT employee_id
FROM `schedule` s
WHERE `appoint_date` = '2018-11-30'
  AND NOT EXISTS (
        SELECT 1
        FROM `schedule` s2
        WHERE s2.`appoint_date` = '2018-11-30'
          AND s2.start_time < '15:00'
          AND s2.end_time > '14:00'
          AND s2.appointment_id = s.appointment_id
      );

edited Nov 23 '18 at 09:33

answered Nov 23 '18 at 08:25

LukStorms

28,916
5
31
45

You don't need five terms in the `WHERE` clause. Read the question again. – Tim Biegeleisen Nov 23 '18 at 08:25

Zaynul Abadin Tuhin · Answer 3 · 2018-11-23T08:40:30.527

0

I prefer not eixsts

 select * from schedule t where not exists
  (select 1 from schedule t1 where 
  t1.appointment_id=t.appointment_id and
  start_time>='14:00' and end_time<='15:00' and appoint_date` = '2018-11-30'
  from )

edited Nov 23 '18 at 08:40

answered Nov 23 '18 at 08:26

Zaynul Abadin Tuhin

31,407
5
33
63

Copy of my answer. – Tim Biegeleisen Nov 23 '18 at 08:28
@TimBiegeleisen you used exists? really – Zaynul Abadin Tuhin Nov 23 '18 at 08:29
How exactly does it work? It is wrong by the way. Fails for many cases. – Salman A Nov 23 '18 at 08:30

score 0 · Answer 4 · answered Nov 23 '18 at 08:29

0

If the query is giving the desired results and is fast enough then it's fine.
You could make improvements, like creating indexes if they do not exist (I believe appointment_id is indexed).
There is a chance that NOT EXISTS instead of NOT IN could be an improvement.
I would write the WHERE part like this:

WHERE (IF(start_time < '14:00', '14:00', start_time) BETWEEN '14:00' AND '15:00')
  AND (IF(end_time > '15:00', '15:00', end_time) BETWEEN '14:00' AND '15:00') 
  AND  (`appoint_date` = '2018-11-30')

answered Nov 23 '18 at 08:29

forpas

160,666
10
38
76

NOT EXISTS is faster than NOT IN? – Sayed Mohd Ali Nov 23 '18 at 08:51
1

@SayedMohdAli I've seen this claim in various discussions, but I have never experienced any difference, maybe because I don't have very large amount of data. If the query is fast then don't change it, the code is much more clearer with `NOT IN`. – forpas Nov 23 '18 at 08:54
appointment_id is a primary key and what do you mean by indexed? – Sayed Mohd Ali Nov 23 '18 at 09:18
If appointment_id is a primary key then it is indexed. See this for the indexes: https://www.tutorialspoint.com/sql/sql-indexes.htm – forpas Nov 23 '18 at 09:20
tim answer was right he made my logic simple without IF – Sayed Mohd Ali Nov 23 '18 at 10:15

Arth · Answer 5 · 2018-11-23T10:30:54.190

This is meant as a supplementary answer to Tim's solution

Breaking down your WHERE section (I have removed the surplus brackets)

WHERE IF(start_time < '14:00', '14:00', start_time) >= '14:00' /** Line 1 */
  AND IF(end_time > '15:00', '15:00', end_time) >= '14:00' /** Line 2 */
  AND IF(start_time < '14:00', '14:00', start_time) <= '15:00' /** Line 3 */
  AND IF(end_time > '15:00', '15:00', end_time) <= '15:00' /** Line 4 */
  AND `appoint_date` = '2018-11-30'

Looking at line 1, IF(start_time < '14:00', '14:00', start_time) will always return a time greater than or equal to '14:00' for a non-null start_time.. so this line is the same as WHERE start_time IS NOT NULL

Similarly line 4 can be rewritten AND end_time IS NOT NULL

Looking at line 2, it is fairly simple to see that this is equivalent to AND start_time <= '15:00'. Any manipulation of start_time by the IF only affects times that would produce a true result, and keeps them true

Similarly line 3 can be rewritten AND end_time >= '14:00'

Putting this all together

WHERE start_time IS NOT NULL /** Line 1 */
  AND end_time >= '14:00' /** Line 2 */
  AND start_time <= '15:00' /** Line 3 */
  AND end_time IS NOT NULL /** Line 4 */
  AND `appoint_date` = '2018-11-30'

Now, as any comparison involving a null operand returns false, line 3 effectively gives you line 1 for free

Similarly, line 2 effectively gives you line 4 for free

Removing these superfluous lines, you end up with Tim's WHERE

WHERE end_time >= '14:00' 
  AND start_time <= '15:00'
  AND appoint_date = '2018-11-30'

Addendum

A further benefit of refactoring away your IF clauses in Tim's WHERE is that this allows the engine to utilise an index on start_time, or end_time, to satisfy these conditions. This can give a significant performance benefit

I am using NOT IN and also IF in Where Clause of subquery MySQL is it right way?

5 Answers5