What is the most efficient way to do a case insensitive LEFT JOIN in Sqlite/Python?

Question

I need to join two Sqlite tables, using custom upcase translations, in a Python program. Is there an established best coding practice for building case insensitive LEFT JOIN queries in Sqlite?

The built-in upper/lower functions in Sqlite are not suitable building blocks as they don't support custom upcase translations.

I'm considering four approaches:

1. Custom function used directly in the JOIN query:

def Upcase(String):
    return String.upper() #in practice, this will be .translate() instead of .upper()

con.create_function("CF", 1, Upcase)
cur.execute("SELECT * FROM left LEFT JOIN right on CF(left.Value) = CF(right.Value)")

2. Create views using a custom function, then use the views in the JOIN query:

def Upcase(String):
    return String.upper()  #in practice, this will be .translate() instead of .upper()

con.create_function("CF", 1, Upcase)
con.execute("CREATE VIEW lv AS SELECT *, CF(left.Value) AS Upcasevalue FROM left")
con.execute("CREATE VIEW rv AS SELECT *, CF(right.Value) AS Upcasevalue FROM right")
cur.execute("SELECT * FROM lv LEFT JOIN rv ON lv.Upcasevalue = rv.Upcasevalue")

3. As above, except using temporary tables with indexes instead of views.

4. Read the dataset out of the database, upcase the column values to be joined in client code, insert the data into temporary tables, then join the temporary tables.

Is any option an overwhelmingly better choice when working with datasets on the order of 200K rows?

Answer 1

Look here, this answers the question so for you its:

cur.execute("SELECT * FROM lv LEFT JOIN rv ON lv.Upcasevalue = rv.Upcasevalue COLLATE NOCASE")

I hope I could help you