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

Question

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 ?

score 3 · Answer 1 · edited May 23 '17 at 12:11

3

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.

edited May 23 '17 at 12:11

Community

1
1

answered Mar 19 '13 at 16:49

Kaf

33,101
7
58
78

THAT'S how you insert multiple values! Why could you not have written this 6 years ago? – Mike Burton Mar 19 '13 at 16:56
@MikeBurton: What makes you to take it 6 years back? Oh btw, I wasn't a member of `stackoverflow.com` 6 years ago. – Kaf Mar 19 '13 at 17:03
isnt `UNION` a better solution? – Praveen Nambiar Mar 19 '13 at 17:04
@Kaf: I was trying to work on a system with a fair-sized SQL DAL back then, and this would have come in handy for many, many things. – Mike Burton Mar 19 '13 at 17:05
@PraveenNambiar: How could you use a `UNION`? You cannot use it for his requirement. – Kaf Mar 19 '13 at 17:13
@Kaf either ways he just wants to populate data which is not present so i guess even a `UNION` would work in his case. – Praveen Nambiar Mar 19 '13 at 17:15
@PraveenNambiar, `UNION` doesn't work in this case, your below solution is a `hard coded query for a single value`. – Kaf Mar 19 '13 at 17:21
@PraveenNambiar, no problem! I have added a demo above. – Kaf Mar 19 '13 at 17:28

score 0 · Answer 2 · answered Mar 19 '13 at 16:43

0

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.

answered Mar 19 '13 at 16:43

Mike Burton

3,010
24
33

score 0 · Answer 3 · answered Mar 19 '13 at 17:11

0

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

answered Mar 19 '13 at 17:11

Praveen Nambiar

4,852
1
22
31

What happen if he adds more `e_nos` to the list and (for example) 10 of them are not in `MY_TABLE`? And what about if `457` is actually in `MY_TABLE` ? – Kaf Mar 19 '13 at 17:16

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

3 Answers3