2

I have table with 3 columns:

enter image description here

I want to get all records where type like '%' and is_connect like 'N', Including rows where type IS NULL or contains a string.

My query:

SELECT * 
FROM OFFER 
WHERE TYPE LIKE '%' 
AND IS_CONNECT LIKE 'N';

I got the rows where type is NOT NULL, but how can I get all rows, even when the type IS NULL?

oria
  • 21
  • 1
  • Possible duplicate of [Get null == null in SQL](http://stackoverflow.com/questions/191640/get-null-null-in-sql) – Alexander May 04 '16 at 14:38

4 Answers4

1

If you want to get all or the rows where there is either a value or no value, simply exclude the field from your where clause:

SELECT * FROM OFFER WHERE IS_CONNECT = 'N';
Allan
  • 17,141
  • 4
  • 52
  • 69
1

just remove the like clause if you want all the entries for type :

SELECT * FROM OFFER WHERE (TYPE LIKE '%'  or TYPE is NULL) AND IS_CONNECT = 'N';
Aritra Bhattacharya
  • 720
  • 1
  • 7
  • 18
  • 1
    This is probably a better solution than mine..I would probably go with this... Since you are really not doing anything special with a check like `TYPE LIKE '%'` – cableload May 04 '16 at 14:32
  • i can't remov like clause , i need to filter table with type and is_connect columns – oria May 04 '16 at 15:29
1

Try this. 

Where N is you first alphabet. 
SELECT * FROM OFFER 
WHERE TYPE is null 
AND IS_CONNECT LIKE 'N%';

Where N is the last alphabet in your type. 
SELECT * FROM OFFER 
WHERE TYPE is null 
AND IS_CONNECT LIKE '%N';

__ BCG14

Smittey
  • 2,475
  • 10
  • 28
  • 35
Gbenga A
  • 9
  • 1
0

You were almost there..Just use an OR condition to include null check...Also, you didnt have a 'N%' after is_connect like

SELECT * FROM OFFER WHERE (TYPE LIKE '%'  or TYPE is NULL) AND IS_CONNECT LIKE 'N%'
cableload
  • 4,215
  • 5
  • 36
  • 62