SQL Server NULL value with inner join
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 开发者_JS百科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 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;
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
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
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
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
精彩评论