Geek Answers Handbook

SQL query theory question - single-charge queries and multiple-statement queries

When I write SQL queries, I often find that "there is no way to do this with a single query." When this happens, I often move on to stored procedures or multitasking table functions that use temporary tables (of one type or another) and eventually simply merge the results and return the result table.

I am wondering if anyone knows, simply as a matter of theory, whether it is possible to write ANY query that returns a single result set as a single query (rather than multiple statements). Obviously, I ignore relevant points such as code readability and maintainability, perhaps even query performance / efficiency. This is more about theory - it can be done ... and don’t worry, I certainly don’t plan to start pushing myself to write a query with one expression when multitasking is better for my purpose in all cases, but it can make me think twice or a little longer about whether there is a viable way to get the result from a single query.

I think a few parameters are fine - I am thinking of a relational database (e.g. MS SQL) with tables that follow common best practices (e.g. all tables have a primary key, etc.).

Note. To win the “Accepted Answer” on this, you need to provide the final proof (link to web material or something similar.)

+6
sql theory relational-algebra
source share
5 answers

At least with the latest version of Oracle it is absolutely possible. It has a "model article" that makes sql turing complete. ( http://blog.schauderhaft.de/2009/06/18/building-a-turing-engine-in-oracle-sql-using-the-model-clause/ ). Of course, all this with the usual restriction that we really do not have unlimited time and memory.

For a normal sql dialect without these abdominations, I do not think this is possible.

A task that I do not see how to implement in "normal sql" would be: Suppose a table with a single column of type integer

For each row, 'take the value in the current row and make sure that many rows are returned, extract that value, go on to see that many rows are returned, and continue until you get the same value twice and return it as result.'

+2
source share

I think this is possible. I worked with very complex queries, very long queries, and often this can be done with a single query. But most of the time it is more complicated, but if you do it with a single request, make sure that you carefully comment on your request.

I have never come across something that could not be done in any request. But sometimes this is best done in a few queries.

+3
source share

I cannot prove it, but I believe that the answer is a cautious yes - provided that the design of your database is completed correctly. Usually forcing multiple operators to write for a specific result is a sign that your circuit might require some improvements.

+2
source share

I would say yes, but I can’t prove it. However, my main thought process:

  • Any choice should be based on a set of operations

  • Your assumption is that you are dealing with mathematically correct sets (i.e. normalized correctly)

  • Set theory should guarantee this

Other thoughts:

  • Several SELECT statements often load temporary tables / variable tables. They can be obtained or shared in the CTE.

  • Any RBAR processing (for good or bad) will now be processed using CROSS / OUTER APPLY on views

  • UDFs will be classified as "cheating" in this context, I feel, because it allows you to put SELECT in another module, and not in your only

  • Writing is not allowed in your "before" DML sequence; this changes state from SELECT to SELECT

  • Have you seen any code in our store?

Edit glossary

Edit: APPLY: cheating?

SELECT * FROM MyTable1 t1 CROSS APPLY ( SELECT * FROM MyTable2 t2 WHERE t1.something = t2.something ) t2
+2
source share

In theory, yes, if you use functions or the excruciating maze of EXTERNAL APPLICATIONS or subqueries; however, for readability and performance, we always ended up temp tables and stored procedures with multiple statements.

As noted above, this is usually a sign that your data structure is starting to smell; not so bad, but maybe it's time for denormalization for performance reasons (happens to the best of us), or maybe put a denormalized query layer in front of your normalized "real" data.

+1
source share

More articles:


All Articles