How to search for column name in range or column is null

Question

I have an query that I am writing where i have a table containing a list of ages, this column can have NULLS. I have been trying to get a result containing users ages 18+ and if the users age is NULL i would like that result as well. Here is a simplified version of what im trying to do below, and the results.

SELECT * FROM TABLE WHERE (Table.Column >= 18 OR Table.Column IS NULL)

-Returns users of all ages and nulls

SELECT * `FROM TABLE WHERE ((Table.Column >= 18) OR (Table.Column IS NULL))

-Returns users of all ages and nulls

SELECT * FROM TABLE WHERE (Table.Column >= 18 NOT BETWEEN 1 AND 17)

-Returns only users 18+, does not return NULLS

SELECT * `FROM TABLE WHERE ((Table.Column >= 18)

-Returns only users 18+, does not return NULLS

Any insight to what may be happening would be a blessing. This is part of a 1300 line query and this is the best i am able to simplify it. I may need you to keep in mind there may be other things going on which im un-able to explain and maybe a hacky work around is in order.

To go into further details, in psuedo code the entire query is as below.

      SELECT ColumnA, ColumnB,
        CASE
        WHEN (Condition 1),
        WHEN (Condition 2)
    ELSE 'N/A' END AS [Complete],
        CASE
        WHEN (Condition 1),
        WHEN (Condition 2)
    ELSE Column END AS [Column],
    ColumnC, ColumnD
    FROM
    LEFT OUTER JOIN Table A on A.Column = B.Column
    LEFT OUTER JOIN Table C on A.Column = B.Column
    LEFT OUTER JOIN Table D on A.Column = C.Column
WHERE (
Condition1,
and Condition2,
and (Table.Column >= 18 OR Table.Column IS NULL)
)UNION
SELECT
MAX([column]) AS [column],
MAX([MyColumn] AS [My Column],
FROM (
SELECT 
column],
MyColumn,
CASE
WHEN (Condition 1),
WHEN (Condition 2)
else 'N/A' end as [Complete]
CASE
WHEN (Condition 1),
WHEN (Condition 2)
ELSE Column END AS [Column],
Column3
WHERE (Condition1)
AND Condition2
)
and (Table.Column >= 18 OR Table.Column IS NULL)
GROUP BY [ColumnName]
UNION
SELECT * FROM TABLE

The column that you named 'Column' is of type NUMBER I suppose? — Robert Kock, Oct 12 '16 at 13:47
Why don't you just use COALESCE or IS NULL depending on what dbms. SQLServer.... SELECT * FROM TABLE WHERE COALESCE(Table.Column, 18) >= 18 Will get you the 18+s and NULL. IF you want to exclude the nulls..... SELECT * FROM TABLE WHERE COALESCE(Table.Column, 17) >= 18 — AntDC, Oct 12 '16 at 13:47
Is there a way of saying WHERE Table.Column IS NOT NULL THEN Table.Column must be >= 18 ? — jon.nicholssoftware.com, Oct 12 '16 at 13:48
I am getting confused? You could use a CASE statement. WHEN Column IS NOT NULL THEN 18 ELSE 17 END But why store an age? — AntDC, Oct 12 '16 at 13:50
it was a smallint type which ive converted to varchar. I've tried using COALESCE and I get criteria that is NULL or 18+ but I am getting more results than i should, give me a moment, let me see if i can elaborate more on my query on stack — jon.nicholssoftware.com, Oct 12 '16 at 13:53
If its's a varchar, then you need to cast it to an int in your where clause. — RSSM, Oct 12 '16 at 13:57
Something else in your query is returning the extra rows. Either of the first two answers should get you what you are after. — Steve Mangiameli, Oct 12 '16 at 13:58
Why would you convert a number to a varchar and then use an inequality on it? You would get the results you are after if that was stored as a number. Otherwise you are doing a lexicographical ordering for your inequality which may give you some screwy results. — JNevill, Oct 12 '16 at 13:58
To those proposing the coalesce in the where, [please stop](http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable) — JohnHC, Oct 12 '16 at 14:01
@JohnHC that is completely RDBMS dependant. For instance, the parsing engine in Teradata will rewrite a COALESCE in a WHERE statement as an `OR` just as OP has here in option 1 making the query sargable. If you don't know how your RDBMS's parsing engine and optimizer are going to behave with your query, then I agree, avoiding coalesce is a sound tactic. — JNevill, Oct 12 '16 at 14:04
@JNevill That's a feature of that DBMS, none tagged here so assume ANSI/raw, sargability is relevant. — JohnHC, Oct 12 '16 at 14:06
I think the issue it it goes Table Union, Table Union, Table Union, Can i wrap this whole thing in some sort of statement and say where column >=18 and column is not null? — jon.nicholssoftware.com, Oct 12 '16 at 14:28

score 0 · Answer 1 · answered Oct 12 '16 at 16:13

For my case I had to put the three tables which were being union together in 3 different sub queries, from there I put my where clause

SELECT A.* FROM (
Select * FROM TABLE1
) A
WHERE (A.ColumnOne >= 18 OR ColumnOne IS NULL)
UNION
SELECT B.* FROM (
Select * FROM TABLE2
) B
WHERE (B.ColumnOne >= 18 OR ColumnOne IS NULL)
UNION
SELECT C.* FROM (
Select * FROM TABLE2
) C
WHERE (C.ColumnOne >= 18 OR ColumnOne IS NULL)

How to search for column name in range or column is null

1 Answers1