There is no difference. The problem statement was: Look up the mail address (which is constrained unique) and return the defined action (which is constrained NOT NULL). If there is no "action" (which can be only because the mail does not exist) return OK.
Unless, of course, the initial problem was misstated. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Dominique Devienne >Sent: Thursday, 17 May, 2018 02:41 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] Is this really the best way to do this? > >On Wed, May 16, 2018 at 8:33 PM Keith Medcalf <kmedc...@dessus.com> >wrote: > >> >> SELECT coalsce((select action >> from blocked >> where mail='...'), 'OK') as action; >> > >Nice one Keith. Works (see below), but I find it a bit intuitive, >since returning no row is somehow not intuitively equivalent (in my >mind at >least) >to a scalar row with a NULL value. Your query makes no distinction >between >these two cases. > >PS: Thanks Ryan, that's what I thought too, and would have been my >work-around too. > >C:\Users\ddevienne>sqlite3 >SQLite version 3.20.1 2017-08-24 16:21:36 >Enter ".help" for usage hints. >Connected to a transient in-memory database. >Use ".open FILENAME" to reopen on a persistent database. >sqlite> create table blocked (mail text primary key, action text); >sqlite> insert into blocked values ('f...@acme.com', 'DISCARD'); >sqlite> select coalesce((select action from blocked where >mail='f...@acm.com'), >'OK') as action; >OK >sqlite> select coalesce((select action from blocked where >mail='f...@acme.com'), >'OK') as action; >DISCARD >sqlite> select action from blocked where mail='f...@acm.com'; >sqlite> select action from blocked where mail='f...@acme.com'; >DISCARD >sqlite> insert into blocked values ('b...@acme.com', NULL); >sqlite> select coalesce((select action from blocked where >mail='b...@acme.com'), >'OK') as action; >OK >sqlite> select action from blocked where mail='b...@acme.com'; > >sqlite> >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
