0

SQL beginner here, trying to figure out how I can grab bothNULL andNOT 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)

WithNULL andNOT 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.

askedOct 31, 2016 at 21:22
Jose's user avatar
6
  • 2
    Any other possible values other then null and not null? I think you just need to remove check for dispositionCommentedOct 31, 2016 at 21:24
  • 1
    Also "AND ri.disposition is NULL AND NOT NULL" is saying it must equal both NULL and NOT NULL which is not possible.CommentedOct 31, 2016 at 21:26
  • 2
    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 conditionCommentedOct 31, 2016 at 21:26
  • When you do aLEFT JOIN, conditions on the second table should normally be in theON clause, notWHERE. Otherwise, it will filter out any rows in the first table that have no match, becauser.storenum will beNULL in those rows. So it becomes effectively the same asINNER JOIN.CommentedOct 31, 2016 at 21:49
  • It's not clear what you're trying to do here. There nothing in the first query that checksri.disposition, so it should return both null and non-null values.CommentedOct 31, 2016 at 21:50

1 Answer1

2

I don't think you really need that condition since you are trying to get both havingNULL andNOT 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 aUNION 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 NULLUNION ALLSELECT  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
answeredOct 31, 2016 at 21:26
Rahul's user avatar
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

Sign up orlog in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

By clicking “Post Your Answer”, you agree to ourterms of service and acknowledge you have read ourprivacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.