MySQL sort of solves this problem with a <=> operator, which I wish was the default for ORMs to use.
There are a lot of other minor nitpicks but a lot of criticisms come down to the actual RDMS not SQL itself.
SQL’s shortcomings can be grouped into these categories:
- lack of proper orthogonality — SQL is hard to compose;
- lack of compactness — SQL is a large language;
- lack of consistency — SQL is inconsistent in syntax and semantics;
- poor system cohesion — SQL does not integrate well enough with application languages and protocols.
[1] We Can Do Better Than SQL: https://www.edgedb.com/blog/we-can-do-better-than-sql/
SELECT AVG(col1) OVER (PARTITION BY col2) AS partcol1
FROM tbl
WHERE AVG(col1) OVER (PARTITION BY col2) > 10.0
SELECT AVG(col1) OVER (PARTITION BY col2) AS partcol1
FROM tbl
WHERE partcol1 > 10.0
So if I have a bunch of these and want a convenient way to work with the named columns, I have to wrap them into a common table expression:
WITH cte AS (
SELECT AVG(col1) OVER (PARTITION BY col2) AS partcol1
FROM tbl
)
SELECT partcol1
FROM cte
WHERE partcol1 > 10.0
For example, your example would have been more naturally expressed as
SELECT partcol1
FROM (tbl WITH partcol1 AS AVG(col1) OVER (PARTITION BY col2))
WHERE partcol1 > 10.0WHERE col1 > col2
is wrong, and it'll break in terrible ways
and in the face of negation + NULLs, everything falls apart[0], giving you both false positive and false negatives in your answerset, and everything will look perfectly fine.
Every column being made NOT NULL is the only sane solution.
[0] https://databasetheory.org/sites/default/files/2018-05/03_pr...
That implies that comparison and boolean operators should be ternary, which would be ugly and confusing: but an ugly and confusing that reflects reality.
This would give us three "greater than" operators:
WHERE col1 > col2
WHERE col1 ?> col2
WHERE col1 >? col2
The most important of such operators would be
WHERE col1 ?= col2
FROMCLAUSE * SELECTCLAUSE * WHERECLAUSE = SQLEXPRESSION
SELECTCLAUSE * WHERECLAUSE * FROMCLAUSE = SQLEXPRESSION
...
SELECTCLAUSE(WHERECLAUSE(relation)) = relation
WHERECLAUSE(SELECTCLAUSE(relation)) = relation
My example in the previous post has a flaw where it's not clear what:
SELECTCLAUSE * WHERECLAUSE = ????
Example:
select * from customers c inner join orders o on o.order_id = c.customer_id
Legal syntax but clearly incorrect.
[1] https://books.google.co.uk/books?id=t9ZQAAAAMAAJ&source=gbs_...
I agree, and I do wish SQL had stronger typing so the parser could warn you before your query silently runs off the rails. For example, in Oracle, I believe the following is legal, but I wish it wasn’t:
select *
from my tab
where 1 = ‘1’