BareFeetWare <list....@tandb.com.au> wrote: >>> SELECT RAISE(FAIL,'album has songs cannot be deleted') >>> WHERE (SELECT count(album_fk) FROM songs WHERE (album_fk = OLD.id) > 0); >> >> Or simply >> >> where old.id in (select album_fk from songs) > > Or most efficiently: > > where exists (select 1 from songs where old.id = album_fk)
I believe the two are equivalent. SQLite essentially rewrites IN to EXISTS - it certainly doesn't generate the complete resultset from the nested select and then go searching inside that. I noticed anecdotally that SQLite may optimize complex statements involving IN better than those involving EXISTS, but I don't have formal evidence. > If the columns are indexed, the advantage is negligible, but it's good SQL > practice to avoid "count" and "in" when you only care > if there is any existence of a match. I'm not sure I understand. What, in your opinion, does IN clause do, if not indicate an existence of a match? > As far as I'm aware, "count" and "in" do a full table scan, whereas "exists" > only scans up > to the first match. Well, count, obviously, but why would IN do that? What makes you believe that? -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
