I've been thinking about patterns to create dynamic SQL for predicates in some orderly fashion. In this case I was thinking of adding dynamic OR conditions. So I start with "0" and then add in ORs based on parameters. I noticed some things, I think some of this has been discussed earlier this week but I'll put it here for reference. Here's what I ran with some notes. It's just an experiment. Based on this if I really wanted to do this I'd want to do some additional optimizations on the generated code so that for instance "OR 0" is removed. ``` select sqlite_version(); 3.32.3 create table person( x text primary key, y text); create table place( x text primary key, y text); -- basic query, the control case, all works as expected explain query plan select * from person left outer join place on person.x = place.x where person.x = 'x'; QUERY PLAN |--SEARCH TABLE person USING INDEX sqlite_autoindex_person_1 (x=?) `--SEARCH TABLE place USING INDEX sqlite_autoindex_place_1 (x=?) -- suppose no condition was added to the left outer join and we -- were left with just the base 0 -- it would be nice if the query plan here did not include SCAN TABLE place explain query plan select * from person left outer join place on 0 where person.x = 'x'; QUERY PLAN |--SEARCH TABLE person USING INDEX sqlite_autoindex_person_1 (x=?) `--SCAN TABLE place -- note that adding 0 OR to the condition spoils the index selection -- so that would have to be removed in the dynamic sql explain query plan select * from person left outer join place on 0 or person.x = place.x where person.x = 'x'; QUERY PLAN |--SEARCH TABLE person USING INDEX sqlite_autoindex_person_1 (x=?) `--SCAN TABLE place -- the order doesn't matter, it's still spoiled explain query plan select * from person left outer join place on person.x = place.x or 0 where person.x = 'x'; QUERY PLAN |--SEARCH TABLE person USING INDEX sqlite_autoindex_person_1 (x=?) `--SCAN TABLE place ``` In case you're curious the metasyntax I was playing with looked like this: ``` create proc findstuff(place_ boolean) begin -- Hypothetically, the OR clause is conditionally added to the generated sql based -- on the place_ argument. This is a silly example, you would only do this if the -- query was much more complicated and the predicate was just a small part of it. -- The general idea is that you might want conditional predicates that are dyamically -- geneated so that you get the best chance at a good plan. select * from person left outer join place ON 0 /* start with nothing */ OR IF (place_) place.x = person.x where person.x = 'x'; end; ```