condition on all columns in where clause

Question

I have table1 with 10 columns(col1 to col10).

Now,

i want to select all the rows in all columns whus values are non null.

I know i can put condition on each column seperately in where clause like

-----------------

select * from table1

where col1 <> Null

and cl2 <> NULL and ..... and col10 <> NULL

--------------------

but i m thinking if there is other way to do this?where i wont have to write conditions on each column?

like putting condition on all column at one shot..

something like this>

----------------------

select * from table1 where table1.* <> NULL

 thnaks,

Dpkas

Answer 1

I guess you are looking for : COALESCE

http://msdn.microsoft.com/en-us/library/ms190349.aspx

---

Hope that helps ... Kunal

Answer 2

select * from table1

where col1 IS NOT NULL or col2 IS NOT NULL or....................or col10 IS NOT NULL

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 3

There is no shortcut like that(table.*)

what you can do is , 

SELECT *
FROM <<Table>> 
EXCEPT
SELECT *
FROM <<Table>> where COALESCE(Col1,Col2,Col3,Col4,....,Col10) is NULL

but the better and straight way is 

select * from table1
where col1 IS NOT NULL AND col2 IS NOT NULL AND........  col10 IS NOT NULL

Regards
Satheesh

Answer 4

There is no shortcut like that(table.*)

what you can do is , 

SELECT *
FROM <<Table>> 
EXCEPT
SELECT *
FROM <<Table>> where COALESCE(Col1,Col2,Col3,Col4,....,Col10) is NULL

but the better and straight way is 

select * from table1
where col1 IS NOT NULL AND col2 IS NOT NULL AND........  col10 IS NOT NULL

Regards
Satheesh

I agree with this post.  Although the COALESCE option looks to be a shorter method of accomplishing the same thing, the multiple IS NULL method of doing it should generate a faster execution plan making better use of indexes.

---

-- Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 5

Thanks for answers.

I know this way but as i said i want to know if i could put all columns(in one shot i.e w/o using or/and and applying conditions separately on each one of them) with common values together in where clause.

Seems there isn't one.

Thanks though.