Interesting concept which reminds of the operations available in pandas.

I disagree though with the statement of SQL needing 20 lines. The given query feels verbose and has lots of redundant conditions. Not saying that it is short but a better analogy could look like this:

SELECT DISTINCT an.name, t.title

FROM keyword k

JOIN movie_keyword mk ON mk.keyword_id = k.id

JOIN title t ON t.id = mk.movie_id

JOIN movie_companies mc ON mc.movie_id = t.id

JOIN company_name cn ON cn.id = mc.company_id

JOIN cast_info ci ON ci.movie_id = t.id

JOIN aka_name an ON an.person_id = ci.person_id

WHERE k.keyword = 'character-name-in-title' AND cn.country_code = '[us]';

▲

amluto a day ago | parent [-]

I would go one step farther: the SQL is awkward and long because the SQL language not at all optimized for data that is normalized all the way to binary relations.

And if you’re trying to benchmark one of these binary relationship query tools against DuckDB, keep in mind that DuckDB is heavily optimized for wide tables and is really not heavily optimized for point queries.

(Also, I, personally, would be a bit unhappy with a DBMS that cannot express, as part of the schema, that a movie has at most one or exactly one title.)

▲

bluenose69 20 hours ago | parent [-]

Re the title possibilities: some films have multiple titles. "The F Word", a Canadian film, was called "What if" in the USA ... I guess to avoid scaring away people who thought it was porn. (It's not. It is funny and sweet. And realistic, in a quite-unrealistic way.)

	▲	amluto 11 hours ago \| parent [-]
		Sure. But IMO one should define, as part of the schema, whether one can handle this.