Modern SQL: With – Organize Complex Queries • Hacker News Board

Re: Modern SQL: With – Organize Complex Queries

#11

Post by ycom13__ » Tue, Nov 24, 2015, 1:06 PM UTC

Updatable CTEs are great, for example if you want to set a column to 1 where the date is the latest day for each value in a different column, you would do something like this

  ;WITH cte AS(SELECT   
        ROW_NUMBER() OVER(PARTITION BY SomeVal   
                  ORDER BY SomeDate DESC) AS row,  
                * FROM SomeTable)

  UPDATE cte  
  SET id =  CASE WHEN row = 1 THEN 1 ELSE 0 END

Here is the DDL and DML in case you want to play around with this

  CREATE TABLE SomeTable(id int,SomeVal char(1),SomeDate date)
  INSERT SomeTable 
  SELECT null,'A','20110101'
  UNION ALL
  SELECT null,'A','20100101'
  UNION ALL
  SELECT null,'A','20090101'
  UNION ALL
  SELECT null,'B','20110101'
  UNION ALL
  SELECT null,'B','20100101'
  UNION ALL
  SELECT null,'C','20110101'
  UNION ALL
  SELECT null,'C','20100101'
  UNION ALL
  SELECT null,'C','20090101'

Re: Modern SQL: With – Organize Complex Queries

#12

Post by reacweb » Tue, Nov 24, 2015, 1:12 PM UTC

Very interesting site, but does anyone know why Oracle and mysql have a so poor support of the standard ? It looks as if postgres was the only one trying to comply.

Re: Modern SQL: With – Organize Complex Queries

#13

Post by danso » Tue, Nov 24, 2015, 1:12 PM UTC

Wow, never knew this. I've been fine with just doing nested subqueries but this is definitely easier to read. Great to learn a helpful piece of everyday syntax before I've had my coffee.

Re: Modern SQL: With – Organize Complex Queries

#14

Post by dorfsmay » Tue, Nov 24, 2015, 1:21 PM UTC

reacweb wrote: post #12

Very interesting site, but does anyone know why Oracle and mysql have a so poor support of the standard ? It looks as if postgres was the only one trying to comply.

Ans DB2, typically DB2 is ahead of the game with newer standard SQL.

Re: Modern SQL: With – Organize Complex Queries

#15

Post by igrekel » Tue, Nov 24, 2015, 1:26 PM UTC

reacweb wrote: post #12

Very interesting site, but does anyone know why Oracle and mysql have a so poor support of the standard ? It looks as if postgres was the only one trying to comply.

I don't know for MySQL but I'd guess in the case of Oracle is that they have equivalents in their own dialect of SQL.

Re: Modern SQL: With – Organize Complex Queries

#16

Post by olavgg » Tue, Nov 24, 2015, 1:27 PM UTC

Markus Winand is a great SQL expert. I'm really grateful for everything that he shares with us. He writes well, explains us the differences between the different SQL engines. And his books are fantastic references.

I highly recommend his "No to offset" tutorial http://use-the-index-luke.com/no-offset Should be common knowledge, but I still see offset being used way to often, even in core ORM frameworks :-(

Re: Modern SQL: With – Organize Complex Queries

#17

Post by frik » Tue, Nov 24, 2015, 1:30 PM UTC

reacweb wrote: post #12

Very interesting site, but does anyone know why Oracle and mysql have a so poor support of the standard ? It looks as if postgres was the only one trying to comply.

Have you read the SQL99 or even 2003 spec? No database supports everything. It's a matter which features one cherrypicks. If you want to use lots of XML and ORMs inside your database or use Java as stored procedures than SQL 2003 spec is great. The enterprise grade database software Oracle, DB2, MSSQL and Postgres are all somewhat comparable.

MySQL with e.g. its InnoDB storage engine is very fast and web scale. Whould a materialized view or other advanced features be useful in MySQL? Sure. But it's a tradeoff. And many devs are willing to go with MySQL for websites.

And SQLite is different than the rest too. And then there are a bunch of NoSQL databases for various usecases.

Re: Modern SQL: With – Organize Complex Queries

#18

Post by wulczer » Tue, Nov 24, 2015, 1:48 PM UTC

dorfsmay wrote: post #14

reacweb wrote: post #12

Very interesting site, but does anyone know why Oracle and mysql have a so poor support of the standard ? It looks as if postgres was the only one trying to comply.

Ans DB2, typically DB2 is ahead of the game with newer standard SQL.

But that's because IBM has a strong presence in the SQL standard committee and can nudge it to standarise (post-factum) things they already implemented in DB2.

Re: Modern SQL: With – Organize Complex Queries

#19

Post by colanderman » Tue, Nov 24, 2015, 2:11 PM UTC

If your query is complex enough to warrant a (non-recursive) CTE, it's probably complex enough to warrant breaking that CTE out into a separate view (or function, if it performs a write). Now you can independently test and reuse that view, which you can't do with a CTE.

(Bonus if you're in Postgres: Postgres optimizes across views, but not across CTEs. Go figure.)

Re: Modern SQL: With – Organize Complex Queries

#20

Post by garyclarke27 » Tue, Nov 24, 2015, 2:11 PM UTC

theallan wrote: post #3

What I really want from SQL is the ability to do a single select with one or more parts of each returned record being an array (defined by a one-to-many join) and others simple scalars. It looks like Postgres can do it [1] with its JSON capabilities, and to be honest its being a little while since I've dug into it, so things might have changed (I'm actually not even sure I'm phrasing the question correctly), but the…

Recursive CTE can be very fast in Postgres. I can create a closure table of 2 Million rows for indirect and direct relations i.e. All paths from 15000 source direct relations in about 12 seconds on my MBPro 1Tb ssd 16 Ram. The makes it practical to just recreate all paths as a materialzed view refresh instead of worring about complicated incremental graph logic such as moving branches. With such a closure view - any graph traversal query is lightning fast - no need for neo4j !