Cfrgtkky

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 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.

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')

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 )

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

      );