CODEWITHSUNDEEP
mysqldatabase
kinkajou
kinkajou

Reputation: 3728

How to check null values in select all query?

I have a situation in which I need to select all columns from a table and the query is something like:

select     sr.*,
           cs.subjectid,
           cs.priority,
           cs.subjectname   
from       sometable sr,   
           otherTable cs   
where      sr.col1='E2011FT0'   
  and      sr.col2='5'     
  and      sr.col3=  '66018'  
  and      cs.col1=sr.col1
order by   cs.col2;

How do I check for null values in the sr.* columns and replace them with a different value? There are a lot of columns in the sometable table and I'm trying to avoid listing them all explicitly.

Upvotes: 4

Views: 5369

Answers (2)

paxdiablo
paxdiablo

Reputation: 881563

I don't believe what you're after is possible in either standard SQL or MySQL (well, not directly on the table, but see below for a possible solution).

There's no option to perform a common column manipulation on sr.*, you will have to do the columns individually, something like:

select sr.column1,
       sr.column2,
       coalesce (sr.column3, 0),
       sr.column4,
       cs.subjectid ...

One possibility, though it's a bit kludgy, is to provide a view over the actual table where each column of the view is similarly named but defined as a coalesce on the equivalent table column. By that I mean something like:

create view sometableview (column1, column2, column3, column4)
    as select column1, column2, coalesce (column3, 0), column4
        from sometable;

select srv.*,
       cs.subjectid, ...
:
from sometableview srv,   
     otherTable cs
where ...

That won't make it any faster but it'll keep your query simpler, which seems to be what you're after. I'm not entirely certain why that's a requirement since queries tend to be set up once and changed rarely so it's unusual to worry about their length. But I'm going to assume you have a good reason for the requirement until informed otherwise :-)

Upvotes: 1

Rishabh
Rishabh

Reputation: 3852

Use SELECT with the COUNT function to count all rows of a given column, including the null values, use the ISNULL function. The ISNULL function can replace the null value with a valid value. With the IsNULL function, NULL is replaced with 0.

CREATE TABLE tabcount (
  pkey int IDENTITY NOT NULL CONSTRAINT pk_tabcount PRIMARY KEY,
  col1 int NULL)
GO

INSERT tabcount (col1) VALUES (10)
GO
INSERT tabcount (col1) VALUES (15)
GO
INSERT tabcount (col1) VALUES (20)
GO
INSERT tabcount (col1) VALUES (NULL)
GO

SELECT AVG(col1) A1,                                 (1)
       AVG(ISNULL(col1,0)) A2,                       (2)
       COUNT(col1) C1,                               (3)
       COUNT(ISNULL(col1,0)) C2,                     (4)
       COUNT(*) C3                                   (5)
FROM tabcount
GO






A1          A2          C1          C2          C3
----------- ----------- ----------- ----------- ---
15          11          3           4           4

--Null value is eliminated by an aggregate or other SET operation.

(1) - NULL values are eliminated. (2) - With the IsNULL function, NULL is replaced with 0. (3) - NULL values are eliminated. (4) - With the IsNULL function, NULL is replaced with 0. (5) - COUNT(*) calculates all rows, even those with NULLs.

Upvotes: 0

Related Questions