SQL Server NULL value with inner join

Question

I am using C# and SQL Server.

Take a look at the following SQL:

SELECT table1.id, table1.description, table2.name, table2.surname 
FROM table1 
    INNER JOIN table2 ON table1.EmpID = table2.EmpID

It is straight forward and works fine. It retrieves the data from table1 table just fine and inner joins table1.empid to table2.name and table2.surname correctly.

Now, sometimes table1.empid is null and when it is, this SQL just ignores the "row" with the null value; which is pretty normal basing on the criteria.

What I need here is to also get the "rows" with the null values and when table1.empid is null I need to set a custom value to table2.name and table2.surname.

I have been playing with isnull() but all I did is make it even worst.

Any suggestions?

Thanks

You really need to provide some sample data with some expected outputs. For example, when table1.EmpID Is Null, what rows for table2 should appear? None? — Thomas, Apr 27 '11 at 20:51

score 10 · Accepted Answer · answered Apr 27 '11 at 20:35

10

You need to do a LEFT JOIN:

SELECT table1.id, table1.description, table2.name, table2.surname FROM table1
LEFT JOIN table2 ON table1.EmpID = table2.EmpID;

answered Apr 27 '11 at 20:35

Alan Savage

822
1
12
24

1

Close, but table1's value doesn't have to be null for the record to show up; it'll still be in the results if there are no table2 records with that empID. Also, the table2 values will always come back null, instead of allowing for a "custom value" as the OP wanted. – KeithS Apr 27 '11 at 20:39
1

The original question does not state that non null values could be returned that don't have a matching record in table2. To set a default value if the value is null you could use: SELECT table1.id, table1.description, ISNULL(table2.name, 'John'), ISNULL(table2.surname, 'Smith') FROM table1 LEFT JOIN table2 ON table1.EmpID = table2.EmpID; – Alan Savage Apr 27 '11 at 21:20

score 0 · Answer 2 · answered Apr 27 '11 at 20:36

Try using a UNION:

SELECT table1.id, table1.description, table2.name, table2.surname 
FROM table1 
INNER JOIN table2 ON table1.EmpID = table2.EmpID
UNION
SELECT table1.id, table1.description, 'Table 2 Null', 'Table 2 Null'
FROM table1
WHERE table1.empId is null

Thomas · Answer 3 · 2011-04-27T20:55:01.427

0

Select table1.id table1.description
    , Case When table1.EmpID Is Null Then 'Some Value' Else table2.name End As Table2Name
    , Case When table1.EmpID Is Null Then 'Some Value' Else table2.surname End As Table2Surname
From table1
    Left Join table2
        On table2.EmpID = table1.EmpID
Where table1.EmpID Is Null
        Or table2.EmpID Is Not Null

edited Apr 27 '11 at 20:55

answered Apr 27 '11 at 20:37

Thomas

63,911
12
95
141

This will replace nulls in existing records as well; table2.surname may legitimately be null, and the OP only stated he wanted the custom values for nonexistent records. Also, if table1.empID isn't null but there isn't a table2 with that empID, the table1 record is shown when the OP wanted them shown only if table1.empID was null. – KeithS Apr 27 '11 at 20:46
@KeithS - WRT to null surnames or names, we aren't told if they are nullable. Regardless, I reworked my solution that would account for that. – Thomas Apr 27 '11 at 20:56

score 0 · Answer 4 · answered Apr 27 '11 at 20:37

If table 1 is null and you still need the records that you cannot start with that. Start with table2 and join table1.

SELECT table1.id, table1.description, ISNULL(table1.empid, "some new value") AS name, table2.surname 
FROM table2 
    LEFT OUTER JOIN table1 ON table2.EmpID = table1.EmpID

score 0 · Answer 5 · answered Apr 27 '11 at 20:42

SELECT table1.id
       ,table1.description
       ,COALESCE(table2.name, 'DEFAULT') AS name
       ,COALESCE(table2.surname, 'DEFAULT') AS surname
FROM table1 
LEFT JOIN table2
    ON table1.EmpID = table2.EmpID

Now note, that this will also include people when the EmpID is not null but nevertheless "invalid" if they have an EmpID in table1, but it isn't found in table2, so if that's something you want to avoid, another option is this:

SELECT table1.id
       ,table1.description
       ,table2.name
       ,table2.surname
FROM table1 
INNER JOIN table2
    ON table1.EmpID = table2.EmpID

UNION ALL

SELECT table1.id
       ,table1.description
       ,'DEFAULT' AS name
       ,'DEFAULT' AS surname
FROM table1 
WHERE table1.EmpID IS NULL

SQL Server NULL value with inner join

5 Answers5