sql one-to-one relation with null values-CodePudding

I have two tables with one-to-one relations like: people has one to one relation with status table

**people table**
ID  Name    Status
1   Mick    1
2   Rohit   null
3   Virat   1
4   Viru    null
5   Gilly   2
6   Shann   null
7   Mitch   3

**status table**
ID  Status 
1   started
2   not-started
3   pending
4   waiting

I need to get the people with status "null" and "started"(from ex: Mick, Rohit, Virat, Viru, Shann).

I tried with SQL query

select p.id, p.name
from people p
inner join status s on s.id = p.status
where (s.name IS NULL OR s.name = 'started')

this is giving only names which have a relation I mean "Mick, Virat" (skips nulls).

I don't know what I am missing here. thanks in advance

CodePudding user response：

In order to keep people records where no status matches you need an OUTER join. In this case, a LEFT OUTER JOIN, which is often shorted to just LEFT JOIN:

select p.id, p.name 
from people p 
left join status s on s.id = p.status
where coalesce(s.name, 'started') = 'started'