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
– Nishal K.R

2019-09-19 12:36:48 +00:00
Commented Sep 19, 2019 at 12:36 — Nishal K.R
– Nishal K.R, Commented Sep 19, 2019 at 12:36

Néstor Carreño Gimeno · Accepted Answer · 2019-09-19 12:38:33Z

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, 2019 at 12:38

answered Sep 19, 2019 at 12:34

Néstor Carreño Gimeno

5332 silver badges14 bronze badges

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

2 Comments

Bill Karwin Over a year ago

MySQL does not support full outer join.

Nishal K.R Over a year ago

@anderlaini: MySQL doesn't support outer joins. LEFT JOIN and LEFT OUTER JOIN are the same in mysql.

R Jain · Accepted Answer · 2019-09-19 12:58:46Z

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, 2019 at 12:58

R Jain

6384 silver badges10 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related