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?

Is your table set to default value NULL? Like: pnpnum varchar(25) DEFAULT NULL — Mr.Web
– Mr.Web, Commented Jul 8, 2013 at 20:31
I believe you should use LEFT JOIN instead of a regular JOIN — Sumurai8
– Sumurai8, Commented Jul 8, 2013 at 20:32

Karo · Accepted Answer · 2013-07-08 20:43:00Z

4

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.

edited Jul 8, 2013 at 20:43

answered Jul 8, 2013 at 20:33

Karo

7444 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kubiej21 Over a year ago

That's exactly what I was looking for. I definitely would appreciate an explanation. SQL novice here..... Thanks :)

Sumurai8 Over a year ago

"If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table." (Docs) // With a normal join, a row must exist in both tables or the row will be dropped, while with a left join, the row only needs to exist in the left table/collection of rows.

Collectives™ on Stack Overflow

Include null values in SQL query

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related