4 ms·
How does it handle subqueries? Especially correlated ones, where the subquery depends on the value provided by the outer query?
by orthoxerox 5y ago
How does it handle subqueries? Especially correlated ones, where the subquery depends on the value provided by the outer query?
- kbelder 5y agoThis is what I'm most interested in. This is where SQL becomes very awkward and repetitive. select customer, ( select sum(revenue) from orders where customer=accounts.customer) as revenue, ( select count(transactionid) from orders where customer=accounts.customer) as orders from accounts; That's a very simple example, but it can get VERY wordy and complex, and in my dreams I'd be able to write something like: getrevenue(cust) is select sum(revenue) from orders where customer=cust getorders(cust) is select count(transactionid) from orders where customer=cust select customer, getrevenue(customer), getorders(customer) from accounts; In a way, it's just dynamic sql without hacking strings together in unmaintainable ways.
- unfocussed_mike 5y agoMy SQL life is seemingly dominated by correlated subqueries and self-joins where the join checks another row does _not_ exist (the row R with the largest column X is the one where there is no row R1 with a larger X1). I don't believe SQL queries like these can be improved by a metalanguage. They _can_ be meaningfully improved by indentation, whitespace, careful naming and detailed comments.