<!-- --> > CREATE TABLE A(Id INT NOT NULL, Name VARCHAR(100)) > CREATE TABLE B(Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY, AId INT) > INSERT INTO A (Id, Name) VALUES (1, 'A'), (2, 'B'), (4, 'C'), (8, 'D'), (9, 'E') > INSERT INTO B (AId) VALUES (2), (3), (4), (5), (6), (7), (8), (9) > > -- When trying to pull an A.Id that doesn't exist (like 3, 5, 6, 7) it should pull the previous ID, sometimes there are large gaps > SELECT * FROM B > LEFT JOIN A ON AId = A.Id > > -- Expected: > -- Id AId Id Name > -- 1 2 2 B > -- 2 3 2 B > -- 3 4 4 C > -- 4 5 4 C > -- 5 6 4 C > -- 6 7 4 C > -- 7 8 8 D > -- 8 9 9 E > > DROP TABLE B > DROP TABLE A > GO > > <pre> > Id | AId | Id | Name > -: | --: | ---: | :--- > 1 | 2 | 2 | B > 2 | 3 | <em>null</em> | <em>null</em> > 3 | 4 | 4 | C > 4 | 5 | <em>null</em> | <em>null</em> > 5 | 6 | <em>null</em> | <em>null</em> > 6 | 7 | <em>null</em> | <em>null</em> > 7 | 8 | 8 | D > 8 | 9 | 9 | E > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=791acf0afa02164e4b8dc47ed6b95b84)*