Multiple nots. Complex firstname != 'Juan' || lastname != 'Cruz' English-speak: If your firstname is not Juan or your lastname is not Cruz, therefore you are not Juan Cruz Don't use the above, it has multiple nots. Use this: Single not, simple. Translates well to English: !(firstname == 'Juan' && lastname == 'Cruz') English-speak (reading from inside out): If you are Juan Cruz, then don't. English-speak (reading from left to right): If you are not Juan Cruz, then do. Postgres tuple test: (firstname, lastname) != ('Juan', 'Cruz') Languages without tuple: firstname + ' ' + lastname != 'Juan Cruz' **Schema (PostgreSQL v13)** create table person(firstname text, lastname text); insert into person(firstname, lastname) values ('Juan', 'Cruz'), ('Juan', 'Buen'), ('Michael', 'Cruz'), ('Michael', 'Buen'); --- **Query #1** select firstname, lastname, firstname != 'Juan' or lastname != 'Cruz' as test1, not (firstname = 'Juan' and lastname = 'Cruz') as test2, (firstname, lastname) != ('Juan', 'Cruz') as test3, (firstname || ' ' || lastname) != 'Juan Cruz' as test4 from person; | firstname | lastname | test1 | test2 | test3 | test4 | | --------- | -------- | ----- | ----- | ----- | ----- | | Juan | Cruz | false | false | false | false | | Juan | Buen | true | true | true | true | | Michael | Cruz | true | true | true | true | | Michael | Buen | true | true | true | true | --- [View on DB Fiddle](https://www.db-fiddle.com/f/nxbuszjT4zgmdj4pGaZeuQ/0) ------------- Multiple nots. Complex firstname != 'Juan' && lastname != 'Cruz' Single not, simple. Translates well to English: !(firstname == 'Juan' || lastname == 'Cruz') English-speak: If your firstname is Juan or lastname is Cruz, then don't. **Schema (PostgreSQL v13)** create table person(firstname text, lastname text); insert into person(firstname, lastname) values ('Juan', 'Cruz'), ('Juan', 'Buen'), ('Michael', 'Cruz'), ('Michael', 'Buen'); --- **Query #1** select firstname, lastname, firstname != 'Juan' and lastname != 'Cruz' as test1, not (firstname = 'Juan' or lastname = 'Cruz') as test2 from person; | firstname | lastname | test1 | test2 | | --------- | -------- | ----- | ----- | | Juan | Cruz | false | false | | Juan | Buen | false | false | | Michael | Cruz | false | false | | Michael | Buen | true | true | --- [View on DB Fiddle](https://www.db-fiddle.com/f/nxbuszjT4zgmdj4pGaZeuQ/1)
Sunday, April 25, 2021
Do not be driven nuts by multiple nots
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment