Skip to main content

Unsupported SQL Features

This topic contains 2 replies, has 2 voices, and was last updated by  Cristi S. 1 year, 4 months ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
  • #1728

    Cristi S.

    Here is a list of SQL features that our models and query builder do not directly supports at this time, that we will unlikely support in the future and the reasons why. We fully encourage your feedback! Give us some constructive reasons why we should reconsider.

    • BETWEEN … AND … – You can always take the >= … AND < = ... approach. Between requires two parameters and it's harder to accommodate this Boolean predicate in our builder.
    • [NOT] EXISTS – We may generate some inline queries with EXISTS, but we found no use case where EXISTS couldn’t be successfully expressed with rather a different operator.
    • INNER, FULL and LEFT/RIGHT JOIN – We always translate our inclusive and exclusive joins into one of these SQL joins. What users may find confusing is the notion of LEFT and RIGHT in the visual builder. What if you have both shapes one below the other? Which is right and which is left?
    • GROUPING_ID – We support GROUPING and we use it to generate Grouping Flag and Grouping Fields, which both return better information than GROUPING_ID, are automatic and do not require manual handling of group columns.

    Mary G.

    Any other reasons why BETWEEN is not and will not be directly included as operator?

    I’ve seen it’s rather more convenient in SQL to express a >= 1 and a <= 10 as a BETWEEN 1 AND 10.


    Cristi S.

    It could be more convenient indeed, but when people are typing queries. In our builder, at least for now, we did not find an easy way to eventually accommodate BETWEEN. And this will introduce additional learning curve, when there is already a simple equivalent way to express it.

    According to SQL All-in-One For Dummies (2011):

    1. “The BETWEEN keyword may be confusing because it doesn’t tell you explicitly whether the clause includes the end points“. Is 10 BETWEEN 10 AND 100 true? It is. Is 10 NOT BETWEEN 10 AND 100 true? It is not. The clause include the end points, but …still unclear.

    2. “If you use BETWEEN, you must be able to guarantee that the first term in your comparison is always equal to or less than the second term.” And this is an “element of surprise” we try to avoid. People may expect 20 BETWEEN 100 and 10 to be a legal statement and return true, but in fact Microsoft Access was our only supported database to return true. All other databases returned false and did not complain at all that this was not a valid statement. If we disallow this in our application, we;ll have to explain why.

    I may write a separate article on all this, the issue is important and confusing indeed. Here it is.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.