Select going wrong-CodePudding

I need to generate this output from this db table

Expected output: Neil

-- Example case create statement:

CREATE TABLE poll 
(
    id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    answer CHAR(1)
);

INSERT INTO poll (id, name, answer) VALUES (1, 'Neil', NULL);
INSERT INTO poll (id, name, answer) VALUES (2, 'Nina', 'Y');
INSERT INTO poll (id, name, answer) VALUES (3, 'Walt', 'N');

I tried to obtain the results using this SQL query, but this does not work:

SELECT * 
FROM poll 
WHERE (name LIKE 'N%') 
  AND (answer <> 'N' OR answer <> 'Y');

What did I do wrong?

Thanks for the support!

CodePudding user response：

Comparing NULL against any string literal will always return false. So, you should also include a null check in your logic:

SELECT *
FROM poll
WHERE name LIKE 'N%' AND
      (answer NOT IN ('N', 'Y') OR answer IS NULL);

CodePudding user response：

If you just want to query the records that start with the letter "N" and where the Answer is Null then why not simply use:

select * from poll where (name like 'N%') AND (answer IS NULL);

CodePudding user response：

You can use functions isnull (SQL Server), ifnull (MySQL/SQLite) to check if values are null.

select * from poll 
where name like 'N%' and (ifnull(answer, '') = '' or answer not in ('N', 'Y'));

select * from poll 
where name like 'N%' and (isnull(answer, '') = '' or answer not in ('N', 'Y'));