Better implimentation of this query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Better implimentation of this query

Author

Topic

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-05-01 : 13:22:18

I want to eliminate rows that have both NULLs in field1 AND field2.
We inserted 6 rows, three NULL, NULL rows should not be in my result set.
This query does this, but is there an easier way to impliment this??


CREATE TABLE #MyTable(Field1 VARCHAR(50), Field2 VARCHAR(50))

INSERT INTO #MyTable(Field1, Field2) VALUES (NULL, NULL)
INSERT INTO #MyTable(Field1, Field2) VALUES (234345, NULL)
INSERT INTO #MyTable(Field1, Field2) VALUES (NULL, 245454324654)
INSERT INTO #MyTable(Field1, Field2) VALUES (NULL, NULL)
INSERT INTO #MyTable(Field1, Field2) VALUES (564874357, 245454324654)
INSERT INTO #MyTable(Field1, Field2) VALUES (NULL, NULL)

--I want to eliminate rows that have both NULLs in field1 AND field2.
--We inserted 6 rows, three NULL, NULL rows should not be in my result set.
--This query does this, but is there an easier way to impliment this??
SELECT * FROM #MyTable
WHERE 
   (Field1 IS NOT NULL AND Field2 IS NULL)
   OR
   (Field1 IS NULL AND Field2 IS NOT NULL)
   OR 
  (Field1 IS NOT NULL AND Field2 IS NOT NULL)


DROP TABLE #MyTable

Thanks!
Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-01 : 13:28:03

SELECT * FROM #MyTable
WHERE
(Field1 IS NOT NULL OR Field2 IS NOT NULL)

or ...

SELECT * FROM #MyTable
WHERE
NOT (Field1 IS NULL AND Field2 IS NULL)

they are logically equivalent.

- Jeff

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-01 : 13:32:25

where not(field1 is null and field2 is null)

EDIT: damn, too slow ...

Jay White
{0}

Edited by - Page47 on 05/01/2003 13:33:24

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-05-01 : 13:36:03

Thanks guys, much better than what I had!!

To all of those wanting help on this site, the way I posted is generally the prefered way of getting help. Post something that we can drop into query analyzer and start working with.

Thanks again!
Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-01 : 15:00:10

4 Seconds has GOT to be a record!