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

Answer 1

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;