Jose Jose - 1 month ago 6
MySQL Question

Selecting NULL and NOT NULL values in MySQL

SQL beginner here, trying to figure out how I can grab both

NULL
and
NOT NULL
values from a table in MySQL. I've tried omitting specifying null and not null to see if that would grab both types, but no luck.

SELECT
COUNT('') as cnt
FROM returned_items ri
LEFT JOIN returns r ON ri.return_id = r.return_id
WHERE r.storenum IN (11)


With
NULL
and
NOT NULL
:

SELECT
COUNT('') as cnt
FROM returned_items ri
LEFT JOIN returns r ON ri.return_id = r.return_id
WHERE r.storenum IN (11)
AND ri.disposition is NULL AND NOT NULL


Any advice is greatly appreciated.

Answer

I don't think you really need that condition since you are trying to get both having NULL and NOT NULL ... means you are trying to get all the records and thus the condition makes no sense but you can have two different queries and perform a UNION ALL like (though not sure why you would do that)

SELECT
  COUNT(*) as cnt
  FROM returned_items ri
  LEFT JOIN returns r ON ri.return_id = r.return_id
  WHERE r.storenum = 11
  AND ri.disposition is NULL
UNION ALL
SELECT
  COUNT(*) as cnt
  FROM returned_items ri
  LEFT JOIN returns r ON ri.return_id = r.return_id
  WHERE r.storenum = 11
  AND ri.disposition is NOT NULL