locked
Why must SQL 'Select' use three 'Is Value' vs one 'Not Value' RRS feed

  • Question

  • User-2075826951 posted

    I am having to code for a "new" added field (so all previous values for this field are NULL) where the Display Code (on New Entry or Update) will not accept Nulls but the SQL table definition will accept Nulls and have found that the following will produce 1879 records:

    either the old

    SELECT * FROM [TABLE] WHERE ...  AND ([DISCHARGE] <> 'Dishonorable') AND ([DECEASED] IS NULL) AND ...

    or the new

    SELECT * FROM [TABLE] WHERE ...  AND ([DISCHARGE] <> 'Dishonorable') AND (([AGR] = 'No') OR ([AGR] = 'Medical') OR ([AGR] IS NULL)) AND ([DECEASED] IS NULL) AND ...

    but

    SELECT * FROM [TABLE] WHERE ...  AND ([DISCHARGE] <> 'Dishonorable') AND ([AGR] <> 'Yes') AND ([DECEASED] IS NULL) AND ...

    produces 0 records.

    Monday, November 6, 2017 7:50 PM

Answers

  • User753101303 posted

    Or IMO for clarity just use ;

    ([AGR] <> 'Yes' OR [AGR] IS NULL)

    I'm using quite rarely nullable string columns (in particular it's easy to show dates or numeric values as being empty but showing both NULL values and empty strings in a UI to users is less obvious).

    Here I would likely use '' (empty string) for existing rows, whatever default value you want for new rows and would make this column NOT NULL..

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 6, 2017 9:25 PM

All replies

  • User475983607 posted

    If AGR is the new field with NULL as the default then  AGR <> 'Yes' results in 

    NULL <> 'Yes' =  false (or unknown)

    and the the record is not displayed.

    Use ISNULL or COALESCE

    Monday, November 6, 2017 8:14 PM
  • User753101303 posted

    Hi,

    My understanding is that you expect AGR<>'Yes' to be true if AGR is NULL ? Actually both :

    SELECT CASE WHEN NULL<>'Yes' THEN 'True' ELSE 'False' END
    SELECT CASE WHEN NULL=NULL THEN 'True' ELSE 'False' END

    are showing false. NULL means the value is unknown so  it is never equal to another value (including to another unknown value). However you can ask if "AGR is unknown" (that is null). See https://docs.microsoft.com/en-us/sql/t-sql/language-elements/null-and-unknown-transact-sql

    If it can help you can add a new NOT NULL column if you define a DEFAULT value for this column at the same time :

    ALTER TABLE MyTable ADD MyColumn VARCHAR(10) NOT NULL CONSTRAINT MyTable_MyColumn_df DEFAULT '?'

    If you want some other value for new rows, you can add the new column, UPDATE existing rows with some values and then ALTER the table again to define this new column as NOT NULL.

    Monday, November 6, 2017 8:35 PM
  • User-2075826951 posted

    E. Using ISNULL to test for NULL values in the column MinPaymentAmount and display the value 0.00 for those rows.

    SELECT ResellerName, ISNULL(MinPaymentAmount,0)

    So I coded:

    SELECT * FROM [Table] WHERE ... AND ([DISCHARGE] <> 'Dishonorable') AND ([AGR] <> ISNULL(AGR, 'Yes')) AND ([DECEASED] IS NULL) AND ...

    Results in 0 Records

    Monday, November 6, 2017 9:03 PM
  • User475983607 posted

    You created the same NULL boolean problem.  ISNULL() checks if the field is null and if it is it replaces null with the second parameter.  

    SELECT * 
    FROM [Table] WHERE 
    ([DISCHARGE] <> 'Dishonorable') 
    	AND (ISNULL(AGR, 'CanNeverBeThisValue') <> 'Yes') 
    	AND ([DECEASED] IS NULL) 

    I'm not a fan of "Not Equal To" and prefer to use a JOIN or an IN clause.  Be careful with NOT IN and NULL that can also cause unexpected results.

    Monday, November 6, 2017 9:11 PM
  • User753101303 posted

    Or IMO for clarity just use ;

    ([AGR] <> 'Yes' OR [AGR] IS NULL)

    I'm using quite rarely nullable string columns (in particular it's easy to show dates or numeric values as being empty but showing both NULL values and empty strings in a UI to users is less obvious).

    Here I would likely use '' (empty string) for existing rows, whatever default value you want for new rows and would make this column NOT NULL..

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 6, 2017 9:25 PM
  • User-2075826951 posted

    You created the same NULL boolean problem.  ISNULL() checks if the field is null and if it is it replaces null with the second parameter. 

    So the condition I want to check is 'If [AGR] is anything ('No' or 'Medical') "other than" (NOT or <>) 'Yes' (which replaces the (now) default value of Null for the field AGR)', give me records.

    Monday, November 6, 2017 9:25 PM
  • User-2075826951 posted

    PatriceSc

    Or IMO for clarity just use ;

    ([AGR] <> 'Yes' OR [AGR] IS NULL)

    Ok; so one less argument:

    SELECT * FROM [Table] WHERE ... AND ([DISCHARGE] <> 'Dishonorable') AND ([AGR] <> 'Yes' OR [AGR] IS NULL) AND ([DECEASED] IS NULL) AND...

    gets me back to my 1879 records.  My "test subject", Daffy Duck who meets all the requirements, now passes or fails correctly depending on how I code the Select statement as with my original three 'Is Value' conditions.  The statement is slightly more clear to read.

    Thank you mgebhard for the links and thoughts.

    BTW: I'm rewriting a 2006 VB program that only works on an XP, Office 2003, MS-Access machine, which code, that had faulty logic in it, I have repaired multiple times.  I have finally convinced the 'Powers That Be' to let me re-write the program as "all the fixin' in the worl' ain't gonna get it offn' XP"  but from which I must inherit more than (not sure yet how many, they're still using the old program til I finish this) 10,000 records.  Almost done with the re-rewrite.  So long and thanks for all the fish.

    Monday, November 6, 2017 9:52 PM