2

I am using SQL Server 2014 and I have a simple T-SQL query (shown below) with its corresponding output.

use mydatabase

select *

from Table1

where ID in (101, 102, 103)

Output is as follows (meaning ID 102 does not exist in Table1):

ID   Age
101   46
103   50

I want the output to be as follows:

ID    Age
101   46
102    0
103   50

When there is no match for an ID in the look-up table, the output omits those IDs. How do I change my T-SQL query to ensure that unmatched IDs are also output but with zeroes.

user3115933
  • 4,303
  • 15
  • 54
  • 94
  • 2
    is there any other table where you have all your id's stored ? – Jayasurya Satheesh Dec 11 '17 at 09:59
  • IDs are primary keys in different tables in the database but I do not have a table where all IDS are kept. – user3115933 Dec 11 '17 at 10:03
  • So, if you don't have a table of ID's how do you know what ID's are actual IDs, and are "missing" from `Table1`. – Thom A Dec 11 '17 at 10:04
  • @Larnu To simplify, I have a list of IDs (which I already know exist in another table, let's call it Table0) that I need to check against Table1. Since ID 102 is not in the output, it means it does not exist in Table1. – user3115933 Dec 11 '17 at 10:09
  • You first need to determine which numbers are missing from your sequence (see this link: https://stackoverflow.com/a/1057423/100283) and then you can join the two data stores and then simply use the Coalesce function to return a zero if there isn't a match. – Mark Kram Dec 11 '17 at 10:12

3 Answers3

0

If don't have table that has the values which you are looking, then you could do that by using join and replace the mismatch values by 0 with help of coalesce() or isnull() function

select a.id, coalesce(t.age, 0) [Age] from <table> t
right join (
       values (101), (102), (103)
) a(id) on a.id = t.id

Result :

id  Age
101 46
102 0
103 50
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
0

If you have 2 or more tables and you want to display some default values if no match is found in one table, then you can use an outer join in SQL.

For Example :

I have 2 tables TableA and TableB.

When I'm doing an inner join I will get records that have matches in both Tables. Suppose I need to select id 1,2 and 3 from both tables and even if one id does not exist in TableB I want the records as NULL, then I can use this 

select
A.Id,
A.Name,
Age = ISNULL(B.Age,0)
    from TableA a
    left join TableB b
    on A.id = b.id

Here Age will be displayed as 0 if there is no matching record for a id in tableB

Jayasurya Satheesh
  • 7,826
  • 3
  • 22
  • 39
0
SELECT T0.ID
       ISNULL(T1.Age,0) AS Age
FROM Table0 T0
     LEFT JOIN Table1 T1 ON T0.ID = T1.ID
WHERE T0.ID IN (101,102,103);
Thom A
  • 88,727
  • 11
  • 45
  • 75