Include null values in SQL query

Question

I have the following four tables in my database:

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| complex_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| complex_name | varchar(45) | NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+


+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| machine_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| complex_id   | int(11)     | NO   | MUL | NULL    |                |
| machine_name | varchar(45) | NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+


+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| devices_id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| machine_id      | int(11)      | NO   | MUL | NULL    |                |
| description     | varchar(255) | NO   |     | NULL    |                |
| location        | varchar(255) | YES  |     | NULL    |                |
| verification    | varchar(255) | YES  |     | NULL    |                |
| rack_num        | varchar(8)   | YES  |     | NULL    |                |
| section_num     | varchar(8)   | YES  |     | NULL    |                |
| color_or_number | varchar(16)  | YES  |     | NULL    |                |
| normal_position | varchar(16)  | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+


+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| pnp_id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| devices_id | int(11)     | NO   | MUL | NULL    |                |
| pnp_num    | varchar(45) | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

I am trying to get results formatted as follows (NULL values appear as blanks):

+------------+-------------+-----------------------+
| devices_id | description | pnpnum                |
+------------+-------------+-----------------------+
| 1          | ex          | 1234                  |
| 2          | ex2         | 2345                  |
| 3          | ex3         |                       |
| 4          | ex4         | 3456, 4567, 5678, 6879|
+------------+-------------+-----------------------+

Using the following SQL query,

SELECT *, GROUP_CONCAT(pnp.pnp_num separator ', ') pnpnum
FROM devices
JOIN pnp ON devices.devices_id = pnp.devices_id
WHERE devices.machine_id = 1
GROUP BY devices.devices_ID
ORDER BY devices.description;

my results are relatively close, however, I am unable to include a device if it has a null pnpnum.

+------------+-------------+-----------------------+
| devices_id | description | pnpnum                |
+------------+-------------+-----------------------+
| 1          | ex          | 1234                  |
| 2          | ex2         | 2345                  |
| 4          | ex4         | 3456, 4567, 5678, 6879|
+------------+-------------+-----------------------+

What is it that I am missing from my SQL statement that will allow me to include null values?

Answer 1

You need to use LEFT JOIN because even if there isn't a match, it will return all the results from the left table leaving the fields from the right table null.