SQL Boolean expression-CodePudding

I'm new to SQL but I thought naively thought this would be simple.

I have a SQLITE table with FIRSTNAME and LASTNAME columns. I want to exclude specific individuals, e.g. 'Fred Smith' and 'Joe Bloggs'. I used the following:

SELECT FIRSTNAME, SURNAME
FROM MyTable
WHERE (FIRSTNAME != 'Fred' AND LASTNAME != 'Smith');

but that excludes all FIRSTNAMEs = 'Fred' and all LASTNAMEs = 'Smith'. How do I achieve what I need? Secondly, how do I achieve that for multiple individuals, i.e. 'Joe Bloggs' as well as 'Fred Smith'?

CodePudding user response：

How do I achieve what I need?

SELECT FIRSTNAME,SURNAME FROM MyTable WHERE NOT (FIRSTNAME = 'Fred' AND LASTNAME = 'Smith');

or the equivalent

SELECT FIRSTNAME,SURNAME FROM MyTable WHERE (FIRSTNAME != 'Fred' OR LASTNAME != 'Smith');

How do I achieve what I need? Secondly, how do I achieve that for multiple individuals, i.e. 'Joe Bloggs' as well as 'Fred Smith'?

Simply add the relevant AND clause.

SELECT FIRSTNAME,SURNAME FROM MyTable WHERE NOT (FIRSTNAME = 'Fred' AND LASTNAME = 'Smith') AND NOT (FIRSTNAME = 'Joe' AND LASTNAME = 'Bloggs');

CodePudding user response：

Use VALUES to return all the names that you want to exclude and the EXCEPT operator to filter them out:

SELECT FIRSTNAME, LASTNAME FROM MyTable
EXCEPT
VALUES ('Fred', 'Smith'), ('Joe', 'Bloggs');

See the demo.