sqlmysqljoinself-join

How can I query no two-column period overlaps?


I'm studying Employees Sample Database which has the following table.

create table dept_emp
(
    emp_no    int     not null,
    dept_no   char(4) not null,
    from_date date    not null,
    to_date   date    not null,
    primary key (emp_no, dept_no),
    constraint dept_emp_ibfk_1
        foreign key (emp_no) references employees (emp_no)
            on delete cascade,
    constraint dept_emp_ibfk_2
        foreign key (dept_no) references departments (dept_no)
            on delete cascade
);

create index dept_no
    on dept_emp (dept_no)

And two views are defined which imply that from_date and to_date are not overlaps.

create definer = root@localhost view current_dept_emp as
select `employees`.`l`.`emp_no`    AS `emp_no`,
       `d`.`dept_no`               AS `dept_no`,
       `employees`.`l`.`from_date` AS `from_date`,
       `employees`.`l`.`to_date`   AS `to_date`
from (`employees`.`dept_emp` `d` join `employees`.`dept_emp_latest_date` `l`
      on (((`d`.`emp_no` = `employees`.`l`.`emp_no`) and (`d`.`from_date` = `employees`.`l`.`from_date`) and
           (`employees`.`l`.`to_date` = `d`.`to_date`))));

create definer = root@localhost view dept_emp_latest_date as
select `employees`.`dept_emp`.`emp_no`         AS `emp_no`,
       max(`employees`.`dept_emp`.`from_date`) AS `from_date`,
       max(`employees`.`dept_emp`.`to_date`)   AS `to_date`
from `employees`.`dept_emp`
group by `employees`.`dept_emp`.`emp_no`;

Here comes a query and a result.

select *
from dept_emp
where emp_no = 499018
;
emp_no dept_no  from_date    to_date
------------------------------------
499018    d004 1996-03-25 9999-01-01
499018    d009 1994-08-28 1996-03-25

How can I query the dept_emp table so that I can confirm, at least in database, and implicitly, that


Solution

  • To find overlaps, use the lag window function:

    select emp_no, from_date, to_date, previous_to_date
    from (
        select emp_no, from_date, to_date, lag(to_date) over (partition by emp_no order by from_date) previous_to_date
        from dept_emp
    ) emps
    where from_date < previous_to_date
    

    (or <= instead of < if the previous to_date being equal to the from_date is considered an overlap)