why is inner join causing query to show only results with not null column?

Question

I'm running the query below and it is working well.

SELECT
    stock_parts.title,
    COUNT(*) as qtyAvailable,
    SUM(shipments.item_cost) as totalValue,
    ### stock_alerts.minimum
FROM
    stock_items
INNER JOIN stock_parts ON stock_items.stock_part_id = stock_parts.id
INNER JOIN shipments ON shipments.id = stock_items.shipment_id
### INNER JOIN stock_alerts ON stock_alerts.stock_part_id = stock_items.stock_part_id
WHERE
    stock_items.status IS NULL
    AND stock_items.current_stock_id = 1
GROUP BY stock_parts.id

So if I uncomment the two commented lines (is a join to get info from another table), the query results are filtered showing only rows where stock_alerts.minimum is not null.

Why is this happening? I need all results event that column is NULL.

try `LEFT JOIN` instead of `INNER JOIN` – Nishal K.R Sep 19 '19 at 12:36 — Nishal K.R, Sep 19 '19 at 12:36

Néstor Carreño Gimeno · Answer 1 · 2019-09-19T12:38:33.987

2

What INNER JOIN does is output the row results where none of the tables have null values on that cell. In other words (from W3 Schools):

The INNER JOIN keyword selects records that have matching values in both tables

Try using FULL OUTER JOINinstead to get all the records even when one of the tables has a NULL value on it. See the explanation on W3 Schools FULL OUTER JOIN.

EDIT:

I didn't realize one of your tags is MySQL. Here you have a post that explains how to emulate a FULL OUTER JOIN in MySQL

edited Sep 19 '19 at 12:38

answered Sep 19 '19 at 12:34

Néstor Carreño Gimeno

523
2
12

1

MySQL does not support full outer join. – Bill Karwin Sep 19 '19 at 12:36
@anderlaini: MySQL doesn't support outer joins. `LEFT JOIN` and `LEFT OUTER JOIN` are the same in `mysql`. – Nishal K.R Sep 19 '19 at 12:42

score 0 · Answer 2 · answered Sep 19 '19 at 12:58

0

Inner JOIN will ignore NULLS when you do an equal to comparison, I would suggest using IS DISTINCT FROM OPERATOR (<=>)

stock_alerts.stock_part_id <=> stock_items.stock_part_id

answered Sep 19 '19 at 12:58

R Jain

486
3
9

why is inner join causing query to show only results with not null column?

2 Answers2