How Do I Write a MySQL Query that Includes a row that Has Null Data?

Question

Below is a description of the scenario I'm trying to solve.

I have two tables. One table called displays is shown below.

id(INT) name(VARCHAR) start_date(DATE) end_Date(DATE) person_id(INT)
5       Whales        2019-01-01       2019-05-31     7
19      Universe      2019-02-01       2019-03-31     13
25      Grizzlies     2019-03-01       2019-06-30     7 
46      Homosapiens?  2019-07-01       2019-09-31     13

The other table called people is shown below.

id(INT) name(VARCHAR) background(TEXT)
7       Ron Swanson   Nonconformist
13      Tom Haverford Hustler
71      Andy Dwyer    Goofball

I'm looking to write a SQL query that finds all the names of all displays and the names of the people associated with the displays. If a display does not have a person, the display should not be in the result. Even if a person does not have a display, the person's name should be present in the final result. The correct query should show the following results (see table below).

Ron Swanson   Whales
Tom Haverford Universe
Ron Swanson   Grizzlies
Tom Haverford Homosapiens?
Andy Dwyer             

The query I've come up with is

SELECT p.name, d.name from displays d, people p WHERE d.person_id = p.id; 

Which gets the right result minus Andy Dwyer. I'm stumped on how to include Andy Dwyer, what am I missing?

Answer 1

You need to use a LEFT JOIN from people to displays:

SELECT p.name, d.name
FROM people p
LEFT JOIN displays d ON d.person_id = p.id

Output (for your sample data)

name            name
Ron Swanson     Whales
Tom Haverford   Universe
Ron Swanson     Grizzlies
Tom Haverford   Homosapiens?
Andy Dwyer      (null)

Demo on SQLFiddle