CODEWITHSUNDEEP
sql-server
Nash
Nash

Reputation: 531

Return NULL columns if IDs don't exist in the table

I have one solution with left join for the below question, but I'm looking for more efficient query

Select * from table1 where Id in (1,2,3,4,5);

returns all the existing "Ids" in the table. Now I want all the Ids to be returned with null columns if the Id is not existing in the table.

EX: Result must contain 3 and 5 though the IDs not existing in the table

ID  Name  Designation
1   John   Employee
2   Nar    Manager
3   **NULL**   **NULL**
4   Esh    Executive.
5   **NULL**   **NULL**

Upvotes: 1

Views: 334

Answers (1)

Jason
Jason

Reputation: 1325

select x.id, y.name, y.designation
from (
select row_number() OVER(order by id) as id
from table1
) x
left join table1 y
on x.id = y.id

This ought to work.

Upvotes: 3

Related Questions