MySQL: Find Extra Days From List Of Date Ranges I have a following rows

start        end
2015-02-12   2015-02-16
2015-02-17   2015-02-18
2015-02-20   2015-02-20


Now I want to calculate number of days for a date range which are not in these ranges. So for example I have date range like this 

2015-02-11 - 2015-02-19


so the result for this input should be 2 days. How? for a clear explanation 

start        end 
2015-02-12   2015-02-16   // 1 day which is 2015-02-11 
2015-02-17   2015-02-18   // 1 day which is 2015-02-19 range
2015-02-20   2015-02-20  // 0 day because this row starting date is greater than input limit.


I want MySQL query for this.
You could use a Dates table to help with this.

This table will hold a single row-per-day from 2015-01-01 into the future:

CREATE TABLE Days
(
  Day date NOT NULL
);

INSERT INTO Days (Day)
SELECT ADDDATE('2015-01-01', INTERVAL (n1.Num + (n2.Num * 10) + (n3.Num * 100) + (n4.Num * 1000)) DAY)
FROM (
  SELECT 0 AS Num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
  SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) n1
CROSS JOIN (
  SELECT 0 AS Num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
  SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) n2
CROSS JOIN (
  SELECT 0 AS Num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
  SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) n3
CROSS JOIN (
  SELECT 0 AS Num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
  SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) n4;


This is the list of ranges you have defined in your question:

CREATE TABLE RangeList
(
  FromDate date NOT NULL
  , ToDate date NOT NULL
);

INSERT INTO RangeList (FromDate, ToDate)
VALUES ('2015-02-12', '2015-02-16')
  , ('2015-02-17', '2015-02-18')
  , ('2015-02-20', '2015-02-20');


This is the range of days you're interested in checking:

CREATE TABLE DaysNotInRange
(
  FromDate date NOT NULL
  , ToDate date NOT NULL
);

INSERT INTO DaysNotInRange (FromDate, ToDate)
VALUES ('2015-02-11', '2015-02-19');


This will show you the missing days:

SELECT Days.Day
FROM Days
    INNER JOIN DaysNotInRange ON Days.Day &gt;= DaysNotInRange.FromDate 
        AND Days.Day &lt;= DaysNotInRange.ToDate
WHERE Days.Day NOT IN (
  SELECT Days.Day
  FROM Days
      INNER JOIN RangeList ON Days.Day &gt;= RangeList.FromDate 
        AND Days.Day &lt;= RangeList.ToDate
  )
ORDER BY Day;


The results:

╔════════════╗
║    Day     ║
╠════════════╣
║ 2015-02-11 ║
║ 2015-02-19 ║
╚════════════╝