CODEWITHSUNDEEP
mysqlnull
Jose
Jose

Reputation: 5210

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.

Upvotes: 0

Views: 66

Answers (1)

Rahul
Rahul

Reputation: 77926

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

Upvotes: 2

Related Questions