Two Kinds of SQL Query Builders

The SQL language, while designed for human users, is primarily written by computers for querying databases. Due to its complex grammar, most programs use specialized libraries to generate SQL queries. FunSQL is one such library that supports all SQL querying capabilities with a data-oriented interface, differentiating it from other query building libraries.

Many query builders share a similar interface, but FunSQL stands out by being data-oriented. This means that the order of pipeline nodes affects the output of the query. For example, rearranging the order of nodes can change the result from the 100 oldest male patients to males among the 100 oldest patients. However, not all query builders share this behavior. Active Record and Laravel generate SQL queries by incrementally assembling the SQL syntax tree, which does not change based on the order of pipeline nodes.

The difference between data-oriented query builders like FunSQL and others lies in how they process information. FunSQL interprets pipeline nodes as processing units that transform data, while other libraries generate SQL queries directly. To convert a pipeline into SQL, FunSQL breaks it into smaller chunks that comply with SQL's rigid clause order, converts each chunk into a SQL query, and nests them together.

This method allows for greater flexibility in the order of pipeline nodes, leading to more concise and interpretable SQL queries compared to the rigid SQL-92 standard.