1

Is there a way in sql to, by default bring back values regardless of if they are true or false?

for example I have a column, 'Mandatory' which datatype is a bit.

Is there a way to bring back records where the column 'Mandatory' is either true or false or null?

something like Select * From Table Where Mandatory = .... etc

Nikk96
  • 83
  • 1
  • 8
  • 1
    Which dbms are you using? – jarlh Feb 03 '21 at 11:45
  • Here: https://stackoverflow.com/questions/10377781/return-boolean-value-on-sql-select-statement – GucciBananaKing99 Feb 03 '21 at 11:45
  • "_is either true or false or null_" - are there any other possible values? – jarlh Feb 03 '21 at 11:47
  • @jarlh, no it will always either be true false or null. I want to be able to bring true false or null all at the same time – Nikk96 Feb 03 '21 at 11:52
  • @Nikk96: Select * From Table or Select Mandatory From Table does works for you ? otherwise what is your goal ? – Malo Feb 03 '21 at 11:59
  • 1
    If the selection of dataset is made irrespective of the value of `Mandatory` field then it is better to not include the field in `where` clause. If this is not the case then please clarify your question. – fiveelements Feb 03 '21 at 12:06
  • The way you worded your question is a bit confusing but I think what you are asking is "how do I filter a query for NULL and separately how do I filter a query for all records not being NULL?" https://www.w3schools.com/sql/sql_null_values.asp should help – Bee_Riii Feb 03 '21 at 12:45

4 Answers4

1
select * from Table  
where mandatory is null or mandatory in (0,1)

If the mandatory is a bit, this code is definitely true. There is no other possibility.

Akif
  • 7,098
  • 7
  • 27
  • 53
yusuf hayırsever
  • 691
  • 7
  • 19
0

Let me assume you are using SQL Server -- because it supports bit but not boolean.

You can use:

where mandatory = 1

or

where mandatory = 'true'
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

resolved by doing this

   ISNULL(Mandatory,0) =
        CASE
        WHEN @Mandatory = 0 THEN 0
        WHEN @Mandatory = 1 THEN 1
        WHEN @Mandatory = 2 THEN ISNULL(Mandatory,0)

essentially saying that when 2 is inputted, it will bring back all values for true, false and null. works very well

Nikk96
  • 83
  • 1
  • 8
-1

The question is how to run a binary logic into trinary logic.

Binary = There are distinct, actual values: in regards to this question the values [True] or [False]

Trinary = There are distinct, actual values plus unknown values: in regards to this question the values [ True | False | NULL ]

It is not possible to include unknown values in a single WHERE condition. Instead it has to be checked separately:

WHERE mandatory IN (0, 1) -- check whether TRUE or FALSE against bit
  OR  mandatory IS NULL   -- also include UNKNOWN

This approach is better than using a function as a function in a WHERE condition can (and will) lead more often than not to performance losses.

Knut Boehnert
  • 488
  • 5
  • 10
  • Given that the column is of the bit data type and can only be NULL, 0 or 1 then there really is no reason to include it in the WHERE statement at all. – Bee_Riii Feb 03 '21 at 12:36
  • The values are a direct answer towards the question how to figure out the difference between binary (distinct values) and trinary logic (distinct values plus NULL). The concept is important when wanting to select all False and NULL values as condition and still stands. – Knut Boehnert Feb 03 '21 at 13:20