Showing entries 1 to 4
Displaying posts with tag: common table expressions (reset)
Comparison of Window Functions & CTEs in MySQL 8 vs MariaDB

Every MySQL database programmer should learn and apply the newly added MariaDB and MySQL Window Functions and Common Table Expressions(CTEs) in their daily work. Both CTEs and window functions enable easy solutions to many query challenges that in prior releases have been difficult and sometimes impossible to surmount. Mastering these features opens the door to query solutions that are more robust, execute faster, and are easier to maintain over time than prior solutions using older techniques.

In our last blog we compared User Roles in MySQL 8 vs. MariaDB.  Today, we will here compare Window Functions and Common Table Expressions in both databases.

Window Functions

While all database administrators are familiar with aggregate functions like COUNT(), SUM(), and AVG(), far less people make use of window functions in their queries.  Unlike aggregate functions, which operate on an entire table, window …

[Read more]
jOOQ 3.10 Supports Exciting MySQL 8.0 Features

In recent months, there had been some really exciting news from the MySQL team:

These two SQL standard language features are among the most powerful SQL features that are available from most other databases. I frequently include them in conference talks about SQL (see my article about 10 SQL Tricks That You Didn’t Think Were Possible), and as well in the Data Geekery SQL Masterclass. With MySQL 8.0 now supporting these …

[Read more]
MySQL Bad Idea #666

MySQL… We’ve blogged about MySQL before. Many times. We’ve shown bad ideas implemented in MySQL here:

But this beats everything. Check out this Stack Overflow question. It reads: “Why Oracle does not support ‘group by 1,2,3’?”. At first, I thought this user might have been confused because SQL allows for referencing columns by (1-based!) index in ORDER BY clauses:

SELECT first_name, last_name
FROM customers
ORDER BY 1, 2

The above is equivalent to ORDER BY first_name, last_name. The indexes 1, 2 refer to columns from the projection. This might be …

[Read more]
PostgreSQL adds windowing functions and common table expressions

As Hubert writes, PostgreSQL 8.4 has windowing functions.

Well done. I’ve been watching progress on this for a while. It greatly enhances the expressiveness of SQL.

What about common table expressions (WITH and WITH RECURSIVE)? Yes, PostgreSQL 8.4 will have common table expressions (WITH and WITH RECURSIVE) as well. That’s the “other” quantum [...]

Showing entries 1 to 4