使用 GROUP BY 以及 HAVING 要筛选要保留的顾问ID，请执行以下操作：

SELECT c_id
FROM consultant_unavailabilities
GROUP BY c_id
HANING COUNT(*) = SUM(
    case when start_date BETWEEN '2018-06-16 09:00:00' AND '2018-06-16 18:00:00' 
        AND end_date between '2018-06-16 09:00:00' AND '2018-06-16 18:00:00'
        then 0 else 1 end
    )

演示。
上面， COUNT 提供顾问的不可用记录总数，而 SUM 给出不包括特定时间的不可用记录数。当这两个计数相等时，顾问在您选择的时间段内可用。

SELECT c_id, start_date, end_date 
FROM c_u 
WHERE (start_date < '2018-06-16 09:00:00' AND end_date < '2018-06-16 09:00:00') 
OR
(start_date > '2018-06-16 18:00:00' AND end_date > '2018-06-16 18:00:00')
AND 
c_id NOT IN (SELECT c_id FROM c_u WHERE 
             (start_date BETWEEN '2018-06-16 09:00:00' AND '2018-06-16 18:00:00')
             OR
             (end_date BETWEEN '2018-06-16 09:00:00' AND '2018-06-16 18:00:00')
            )
            GROUP BY c_id;

sql fiddle示例

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(c_id INT NOT NULL
,start_date DATETIME NOT NULL PRIMARY KEY
,end_date DATETIME NULL
);

INSERT INTO my_table VALUES
(937,'2018-06-01 13:00:00','2018-06-01 20:00:00'),
(937,'2018-04-28 08:00:00','2018-04-28 18:00:00'),
(938,'2018-06-28 14:00:00','2018-06-28 16:00:00'),
(938,'2018-06-16 14:00:00','2018-06-16 17:00:00'),
(939,'2018-05-05 09:00:00','2018-05-05 12:00:00'),
(939,'2018-06-08 12:00:00','2018-06-08 16:00:00');

SELECT DISTINCT x.c_id 
           FROM my_table x
           LEFT
           JOIN
              ( SELECT c_id 
                  FROM my_table a  
                  WHERE '2018-06-16 09:00:00' <= a.end_date 
                    AND '2018-06-16 18:00:00' > a.start_date
              ) y
             ON y.c_id = x.c_id
          WHERE y.c_id IS NULL;
+------+
| c_id |
+------+
|  937 |
|  939 |
+------+

您需要使用子查询来查找在此期间忙碌的所有顾问，然后将他们排除在外：

SELECT c_id, start_date, end_date
FROM consultant_availabilities
WHERE c_id NOT IN (
    SELECT c_id
    FROM consultant_unavailabilities 
    WHERE start_date BETWEEN '2018-06-16 09:00:00' AND '2018-06-16 18:00:00' 
    OR end_date BETWEEN '2018-06-16 09:00:00' AND '2018-06-16 18:00:00')