Selecting NULL and NOT NULL values in MySQL

Question

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.

Any other possible values other then null and not null? I think you just need to remove check for disposition — Vinay Pandey
– Vinay Pandey, Commented Oct 31, 2016 at 21:24
Also "AND ri.disposition is NULL AND NOT NULL" is saying it must equal both NULL and NOT NULL which is not possible. — Adrianopolis
– Adrianopolis, Commented Oct 31, 2016 at 21:26
Regardless of the contradiction the syntax should be AND ri.disposition is NULL AND ri.dispositionNOT NULL in this case the column has to be repeated in every condition — ScaisEdge
– ScaisEdge, Commented Oct 31, 2016 at 21:26
When you do a LEFT JOIN, conditions on the second table should normally be in the ON clause, not WHERE. Otherwise, it will filter out any rows in the first table that have no match, because r.storenum will be NULL in those rows. So it becomes effectively the same as INNER JOIN. — Barmar
– Barmar, Commented Oct 31, 2016 at 21:49
It's not clear what you're trying to do here. There nothing in the first query that checks ri.disposition, so it should return both null and non-null values. — Barmar
– Barmar, Commented Oct 31, 2016 at 21:50

Rahul · Accepted Answer · 2016-10-31 21:26:18Z

2

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

answered Oct 31, 2016 at 21:26

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

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

Collectives™ on Stack Overflow

Selecting NULL and NOT NULL values in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related