How to define INNER JOIN in SQL

I have a problem how to define INNER JOIN in SQL. The problem is that I have a table Names with columns ID and Name. The other table has column PersonID.

Sometimes value in column PersonID is 0, and in Names table there is no row with a column value of 0.

I have created a view, and I am using an INNER JOIN. Problem is that SELECT statement in the view only works when there is value greater than zero in column PersonID.

How can I define an INNER JOIN when value is 0, so that column is NULL, and when is not zero then value should be Name from Names?

What I mean is:

CREATE OR ALTER VIEW dbo.Test
AS
    SELECT 
        a.Column1,
        a.Column2,
        ...,
        CASE
            WHEN a.PersonID IS NULL 
                THEN '' 
                ELSE b.Name
        END AS PersonName
    FROM 
        dbo.ExampleA AS a
    INNER JOIN 
        -- What to put in here that value is NULL when PersonID = 0,
        -- and when PersonID > 0 then join value should be a.PersonID = b.ID?            
        dbo.Names AS b ON ???

Thank you in advance for any help.

Answer

It sounds like you want LEFT JOIN:

SELECT e.Colum1, e.Colum2, . . . ,
       COALESCE(e.name, n.Name) AS PersonName
FROM dbo.ExampleA e LEFT JOIN
     dbo.Names n
     ON n.personid = e.personid;

Note that this also changes the table aliases to meaningful abbreviations for the tables. And uses COALESCE() instead of CASE.

Advertisement

Answer