Temporal "As-Of" JOIN — Salary on a Specific Date
Join employees to salary-history intervals containing the as-of date.
- Joins
- Date analysis
- NULL handling
- Sorting
Exercise brief
Understand the request
Payroll audit lead An audit must reconstruct each employee’s salary as it stood on January 1, 2023.
Find what each employee was earning AS OF 2023-01-01: the salary_history row whose effective_from <= '2023-01-01' AND (effective_to IS NULL OR effective_to >= '2023-01-01'). INNER JOIN — employees with no qualifying history row are dropped. Return employee_id, first_name, last_name, salary, effective_from, effective_to — ordered by employee_id.
Return
- Return employee_id, first_name, last_name, salary, effective_from, effective_to in this exact left-to-right order.
Constraints
- Put the employee key and both effective-date predicates in ON.
- Retain only employees with a qualifying history interval.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
employee_idINTEGERfirst_nameVARCHAR(50)last_nameVARCHAR(50)
salary_history
employee_idINTEGERsalaryINTEGEReffective_fromDATEeffective_toDATE
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
'Active at moment T': effective_from <= T AND (effective_to IS NULL OR effective_to >= T).
Hint 2
Put the temporal predicate IN THE ON clause — it is part of the join, not a post-join filter.
Hint 3
If two rows ever overlap for the same entity, this query returns BOTH — that means your history table has a data-quality bug.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT e.employee_id, e.first_name, e.last_name, sh.salary, sh.effective_from, sh.effective_to FROM employees e INNER JOIN salary_history sh ON sh.employee_id = e.employee_id AND sh.effective_from <= '2023-01-01' AND (sh.effective_to IS NULL OR sh.effective_to >= '2023-01-01') ORDER BY e.employee_id;Why this works
Temporal/'as-of' joins are essential in finance, audit, and any reporting that has to be reproducible at a past moment. Modern engines have specialized syntax: SQL Server's `FOR SYSTEM_TIME AS OF`, PostgreSQL with range types, etc. The classic `from <= T AND (to IS NULL OR to >= T)` predicate works on every engine.
Success check
Each employee with coverage on the as-of date appears with the correct historical salary row.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| employee_id | first_name | last_name | salary | effective_from | effective_to |
|---|---|---|---|---|---|
| 100 | John | Smith | 120000 | 2023-01-01 | NULL |
| 101 | Alice | Johnson | 75000 | 2021-03-20 | 2023-06-30 |
| 102 | Bob | Wilson | 80000 | 2021-06-10 | NULL |
| 103 | Carol | Davis | 60000 | 2023-01-01 | NULL |
| 104 | David | Brown | 70000 | 2022-02-14 | NULL |
| 105 | Emma | Taylor | 95000 | 2020-11-30 | NULL |
| 106 | Frank | Green | 65000 | 2021-08-25 | NULL |
| 107 | Grace | White | 90000 | 2019-05-12 | NULL |
| 108 | Henry | Clark | 55000 | 2022-07-18 | NULL |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
Self Joins & Hierarchical Queries
Query organization charts, trees, and parent-child relationships.
SQL Subqueries
Practice scalar, derived-table, correlated, EXISTS, NULL-safe anti-subquery, quantified, and row-subquery patterns.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
Open the interactive workspace and practice across SQL topics.