One of the things that confuse SQL users all the time is how DISTINCT and ORDER BY are related in a SQL query. The Basics Running some queries against the Sakila database, most people quickly understand: SELECT DISTINCT length FROM film This returns results in an arbitrary order, because the database can (and might apply … Continue reading How SQL DISTINCT and ORDER BY are Related
Category: sql
This category contains posts about SQL in general or some RDBMS’s SQL dialect
PostgreSQL 11’s Support for SQL Standard GROUPS and EXCLUDE Window Function Clauses
Exciting discovery when playing around with PostgreSQL 11! New SQL standard window function clauses have been supported. If you want to play with this, you can do so very easily using docker: docker pull postgres:11 docker run --name POSTGRES11 -e POSTGRES_PASSWORD=postgres -d postgres:11 docker run -it --rm --link POSTGRES11:postgres postgres psql -h postgres -U postgres … Continue reading PostgreSQL 11’s Support for SQL Standard GROUPS and EXCLUDE Window Function Clauses
Using UNPIVOT to Traverse a Configuration Table’s Rows and Columns
Imagine you have a configuration table like the following: CREATE TABLE rule ( name VARCHAR2(50) NOT NULL PRIMARY KEY, enabled NUMBER(1) DEFAULT 1 NOT NULL CHECK (enabled IN (0,1)), priority NUMBER(10) DEFAULT 0 NOT NULL, flag1 NUMBER(3) DEFAULT 0 NOT NULL, flag2 NUMBER(3) DEFAULT 0 NOT NULL, flag3 NUMBER(3) DEFAULT 0 NOT NULL, flag4 NUMBER(3) … Continue reading Using UNPIVOT to Traverse a Configuration Table’s Rows and Columns
Oracle’s OFFSET .. FETCH Can be Slower than Classic ROWNUM Filtering
One of Oracle 12c's coolest features was the introduction of the SQL standard OFFSET .. FETCH clause, as we can now write things like: SELECT * FROM film ORDER BY film_id FETCH FIRST 1 ROW ONLY This is querying the Sakila database. Most other databases had this clause (or a non-standard version of it) for … Continue reading Oracle’s OFFSET .. FETCH Can be Slower than Classic ROWNUM Filtering
How to Group By “Nothing” in SQL
The SQL standard knows a lesser known feature called GROUPING SETS. One particular side-effect of that feature is that we can group by "nothing" in SQL. E.g. when querying the Sakila database: SELECT count(*) FROM film GROUP BY () This will yield: count | ------| 1000 | What's the point, you're asking? Can't we just … Continue reading How to Group By “Nothing” in SQL
Selecting all Columns Except One in PostgreSQL
Google's BigQuery has a very interesting SQL language feature, which I've missed many times in other databases: select: SELECT [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ] [ REPLACE ( expression [ AS ] column_name [, ...] ) ] | expression [ [ AS ] alias … Continue reading Selecting all Columns Except One in PostgreSQL
How to Run a Bulk INSERT .. RETURNING Statement With Oracle and JDBC
When inserting records into SQL databases, we often want to fetch back generated IDs and possibly other trigger, sequence, or default generated values. Let's assume we have the following table: -- DB2 CREATE TABLE x ( i INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, j VARCHAR(50), k DATE DEFAULT CURRENT_DATE ); -- PostgreSQL CREATE TABLE … Continue reading How to Run a Bulk INSERT .. RETURNING Statement With Oracle and JDBC
The Performance Difference Between SQL Row-by-row Updating, Batch Updating, and Bulk Updating
Something that has been said many times, but needs constant repeating until every developer is aware of the importance of this is the performance difference between row-by-row updating and bulk updating. If you cannot guess which one will be much faster, remember that row-by-row kinda rhymes with slow-by-slow (hint hint). Disclaimer: This article will discuss … Continue reading The Performance Difference Between SQL Row-by-row Updating, Batch Updating, and Bulk Updating
When Using Bind Variables is not Enough: Dynamic IN Lists
In a previous blog post, I wrote about why you should (almost) always default to using bind variables. There are some exceptions, which I will cover in another follow-up post, but by default, bind variables are the right choice, both from a performance and from a security perspective. In this article, I will show an … Continue reading When Using Bind Variables is not Enough: Dynamic IN Lists
Why SQL Bind Variables are Important for Performance
A common problem with dynamic SQL is parsing performance in production. What makes matters worse is that many developers do not have access to production environments, so they are unaware of the problem (even if there's nothing new about this topic). What exactly is the problem? Execution plan caches Most database vendors these days ship … Continue reading Why SQL Bind Variables are Important for Performance