Re: Idiomatic SQL empty set?

Thanks for posting the reply on-list, John.

I disagree that my alternative requires more parameters. I assumed you were programmatically building an SQL string, and that's why I suggested the approach. Generating software algorithmically is kind of my thing, so I took a look at your approach and saw it like this:

Let's say you're building a select statement and you want to have building blocks for the WHERE clause. The trick is to define where in the WHERE clause to generate: that is, to identify the scope of the object. Your approach is to identify it at the list level, while min is to define it at the clause level. Let's see how the two approaches work to generate this SQL statement:

SELECT * FROM FILE WHERE FIELD1 IN (A, B, C) AND FIELD2 > 30

Your approach is this:

"SELECT * FROM MYFILE WHERE FIELD1 IN " sql.list(FIELD1values) " AND FIELD2 > 30"

Reasonable. However, I'd bring the granularity up one level:

"SELECT * FROM MYFILE WHERE " sql.whereIn('FIELD1', field1List) " AND FIELD2 > 30"

The sql object does the formatting, and sql.whereIn would be smart enough to return "(1 = 0)" if the list is empty. Note that the "FIELD2 > 30" is still pretty hardcoded, but it would work. But anyway, your approach requires that you hardcode the field name in the SELECT and format the IN clause from a list, mine formats the entire clause by passing in the field name and list. Same number of data points.

In truth, though, I'd be going a little more OO. I'd have an object for the file field list and one for each compare field so that I could include it like this:

tmp =
sql.select(myFile) +
sql.where(
sql.whereClause(myFile.Field1),
sql.AND,
sql.whereClause(myFile.Field2))

The definition of the where clause is in the field objects themselves. Note that these objects aren't database definitions for the fields, they're the user definitions for the selection criteria.

The point is that whereClause would be look at the criterion for Field1 and if it's a list would use the whereIn clause, which if the list was empty would return "(1 = 0)". More importantly, it could raise a signal to the WHERE clause to tell it that there are no entries in the list, which could signal the process to short circuit that entire branch, since there are no matches.

Hmm. I like this. I should write about it. :)

From an off-list reply:

I assume you're building the entire statement. If so, simply modify the
logic so that if no values are returned, replace:

where myfield2 in (val1, val2, val3)

with

where (1 = 0)

So, instead of trying to come up with an empty list, just come up with an
always false clause.

Solves the problem quite elegantly. Probably runs faster too.

I'm not building the entire statement. Not at once, anyway.

The reason I would like specifically to have an expression that means
"collection of values", and have it work for any number of values
(including no values), and any type of values, is that I would like to
have a convenience function that converts a Python collection (list,
tuple, or set) into a string suitable for interpolating into an
existing piece of otherwise static SQL (that is, an SQL "template").

This convenience function is meant for very broad use, not just for
one program or one project or one application. It's more at the level
of standard library function.

Your suggestion could also be turned into a library function, but it
would be more specific and require more parameters, and it would lift
more out of the template and into the function. What do I mean by
that?

Let's take the example I've been using in this thread. The template
I'm envisioning looks like this:

tmp = "select myfield1 from mylib.myfile where myfield2 in ({})"

The curly braces mark the replacement or interpolation. If my
convenience function is called values_string() and my Python
collection is called mylist, then I would build the final SQL
statement like so:

sql = tmp.format(values_string(mylist))

With your suggestion, the template would have to be

tmp = "select myfield1 from mylib.myfile where {}"

The convenience function would have to, at minimum, accept a field
name and the collection of values. If we call this function
field_in_values(), then interpolation becomes

sql = tmp.format(field_in_values('myfield2', mylist))

With such a small example, either approach may seem about equally
easy, but to me your suggestion makes the template less useful
(because we'd be most of the way toward having an application-specific
function that takes lots more parameters and just builds the whole SQL
statement, dispensing with the template altogether) and makes the
convenience function both less convenient and less widely applicable.

John Y.