title: PostgreSQL - Merge/Join Tables With Different Effective Dates
body: <p>I have two dimensional tables in an SCD Type 2 format that I would like to merge. The second table contains additional rows relevant to the eventual structure of the first table.</p>
  
  <p>First table (foo):</p>
  
  <pre><code>| employee_id | location_id | team_id | date_effective | date_expired |
  |-------------|-------------|---------|----------------|--------------|
  | 40          | 1           | 6       | 20180101       | 20190331     |
  | 40          | 2           | 6       | 20190331       | 99991231     |
  </code></pre>
  
  <p>Second table (bar):</p>
  
  <pre><code>| team_id | manager_id | date_effective | date_expired |
  |---------|------------|----------------|--------------|
  | 6       | 15         | 20180301       | 20180630     |
  | 6       | 27         | 20180630       | 99991231     |
  </code></pre>
  
  <p>Desired output after merging:</p>
  
  <pre><code>| employee_id | location_id | team_id | manager_id | date_effective | date_expired |
  |-------------|-------------|---------|------------|----------------|--------------|
  | 40          | 1           | 6       | NULL       | 20180101       | 20180301     |
  | 40          | 1           | 6       | 15         | 20180301       | 20180630     |
  | 40          | 1           | 6       | 27         | 20180630       | 20190331     |
  | 40          | 2           | 6       | 27         | 20190331       | 99991231     |
  </code></pre>
  
  <p>I know how to join the two tables by dates, but not how to efficiently generate the extra row(s) necessary in the output. Here is my current code:</p>
  
  <pre><code>with foo as (
  select * 
  from 
  (values (40,1,6,20180101,20190331),(40,2,6,20190331,99991231))t(employee_id, location_id, team_id, date_effective, date_expired)
  )
  
  ,bar as (
  select * 
  from 
  (values (6,15,20180301,20180630),(6,27,20180630,99991231))t(team_id, manager_id, date_effective, date_expired)
  )
  
  select * 
  from foo f
  left join bar b on f.team_id = b.team_id 
      and ((f.date_effective between b.date_effective and b.date_expired)
      or (b.date_effective &gt;= f.date_effective and b.date_effective &lt; f.date_expired))
  </code></pre>
  
  <p>I know I could achieve the results by expanding each table to individual days and do some window functions, but I was wondering if there was a more efficient way.</p>
  
  <p>Thanks in advance!</p>