CODEWITHSUNDEEP
sql
user2434
user2434

Reputation: 6399

SQL Query to return rows even if it is not present in the table

This is a specific problem .

I have an excel sheet containing data. Similar data is present in a relational database table. Some rows may be absent or some additional rows may be present. The goal is to verify the data in the excel sheet with the data in the table.

I have the following query

  Select        e_no, start_dt,end_dt
  From         MY_TABLE
  Where        e_no In 
  (20231, 457)

In this case, e_no 457 is not present in the database (and hence not returned). But I want my query to return a row even if it not present (457 , null , null). How do I do that ?

Upvotes: 0

Views: 246

Answers (3)

Kaf
Kaf

Reputation: 33809

For Sql-Server: Use a temporary table or table type variable and left join MY_TABLE with it

Sql-Server fiddle demo

Declare @Temp Table (e_no int)

Insert into @Temp
Values (20231), (457) 

Select   t.e_no, m.start_dt, m.end_dt
From     @temp t left join MY_TABLE m on t.e_no = m.e_no

If your passing values are a csv list, then use a split function to get the values inserted to @Temp.

Upvotes: 3

Praveen Nambiar
Praveen Nambiar

Reputation: 4892

You can also do it this way with a UNION

Select
     e_no, start_dt ,end_dt
From         MY_TABLE
Where e_no In (20231, 457) 

UNION

Select 457, null, null

Upvotes: 0

Mike Burton
Mike Burton

Reputation: 3020

Why not simply populate a temporary table in the database from your spreadsheet and join against that? Any other solution is probably going to be both more work and more difficult to maintain.

Upvotes: 0

Related Questions