Fixing MySQL "Incorrect string value": utf8 vs utf8mb4 Done Right

Posted by Kyle Hankinson August 14, 2026

A user leaves a comment ending in a thumbs-up emoji, and your application logs this:

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x91\x8D' for column 'body' at row 1

The table looked reasonable when it was created years ago:

CREATE TABLE comments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  body TEXT
) CHARACTER SET utf8;

The column claims to be UTF-8, the application sends valid UTF-8, and MySQL still rejects it. Walking through why, and fixing it at every layer, is the difference between a five-minute patch that resurfaces next month and actually being done with this error.

Reading the bytes in the message

MySQL shows you exactly what it choked on. F0 9F 91 8D is the UTF-8 encoding of U+1F44D, the thumbs-up emoji, and the significant part is that it is four bytes long. MySQL's historical utf8 character set stores at most three bytes per character. It is not real UTF-8; it is a subset now officially named utf8mb3, and current servers display it that way. On MySQL 8.4, SHOW CREATE TABLE for the table above reports DEFAULT CHARSET=utf8mb3.

Three bytes cover the Basic Multilingual Plane, which is why the column worked for years of English, European, and most CJK text. Everything beyond it needs four bytes: emoji, many less common CJK ideographs, mathematical symbols, and other supplementary-plane characters. The moment one arrives, error 1366.

The real UTF-8 type is utf8mb4, available since MySQL 5.5 and the server default since MySQL 8.0 (with collation utf8mb4_0900_ai_ci). MariaDB made utf8mb4 its default in 10.6. New databases are fine; this error lives in tables and connections created before those defaults, or created with an explicit utf8.

The layers that must agree

Fixing only the column is the classic half-fix, because character set is negotiated in more than one place:

1. The column (or its table/database default) controls what can be stored.
2. The connection controls how MySQL interprets the bytes your client sends.
3. The client library or driver decides what charset it requests, often via a charset option or SET NAMES.

The failure modes differ by layer, and one is much worse than the error. With the column already fixed but the connection charset wrong (here, a client connected with latin1 sending an emoji), MySQL raised no error at all in testing on 8.4. It stored this:

That is mojibake, silently committed. The UTF-8 bytes were reinterpreted as latin1 characters and then re-encoded, and no amount of later charset fixing will un-mangle rows stored this way. If you are diagnosing this error, look at what is already in the table before altering anything; browsing the data in SQLPro for MySQL makes it immediately obvious whether past writes were rejected cleanly (error 1366) or corrupted quietly (mojibake like the above), which determines whether you also have data repair ahead of you.

To see the connection side, check:

SHOW VARIABLES LIKE 'character_set_c%';

You want character_set_client and character_set_connection reporting utf8mb4. Set your driver's charset option to utf8mb4 (not utf8), or issue SET NAMES utf8mb4 at connection setup. The full negotiation rules are in the manual's connection character sets chapter.

Converting the tables

The straightforward, verified fix:

ALTER TABLE comments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Caution: on large tables this rebuilds the table and can lock writes for the duration, so run it in a maintenance window and take a backup first. Also note one documented surprise: CONVERT TO may widen types to guarantee the converted data fits. In testing, the TEXT column came back as MEDIUMTEXT. If you need exact types preserved, alter columns individually with ALTER TABLE ... MODIFY body TEXT CHARACTER SET utf8mb4 instead of converting the whole table. On MySQL 8.x, pick utf8mb4_0900_ai_ci as the collation; on MariaDB, utf8mb4_general_ci or the modern utf8mb4_uca1400_ai_ci (its 11.4 default).

After conversion, the emoji insert succeeds. But two side effects are worth knowing before you convert a whole schema.

Side effect 1: index length limits (error 1071)

utf8mb4 makes every character potentially four bytes, and index keys are sized in bytes. On old InnoDB row formats a single-column index maxes out at 767 bytes, so a VARCHAR(255) utf8mb4 unique key (255 x 4 = 1020 bytes) fails. Reproduced on MySQL 8.4 by forcing the old row format:

mysql> CREATE TABLE old_style (
    ->   email VARCHAR(255),
    ->   UNIQUE KEY (email)
    -> ) CHARACTER SET utf8mb4 ROW_FORMAT=COMPACT;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

The same table without ROW_FORMAT=COMPACT creates fine, because modern defaults (innodb_default_row_format=dynamic, standard in MySQL 5.7.9+ and MariaDB 10.2.2+) raise the limit to 3072 bytes. So this bites you only on tables carried forward with ROW_FORMAT=COMPACT or REDUNDANT, or servers with non-default settings. The old workaround of VARCHAR(191) keys is rarely needed now; rebuilding the table with a modern row format is the better fix. If you are also revisiting whether those columns should be VARCHAR at all, see VARCHAR vs TEXT vs NVARCHAR.

Side effect 2: mixed charsets and collations after a partial conversion

Convert half your tables and joins across the boundary start misbehaving. Comparing a utf8mb3 column to a utf8mb4 column did not error in testing, but the optimizer could no longer use a normal index lookup for the join (EXPLAIN showed it falling back to a hash join), which on real table sizes reads as "the query got slow after the migration". Mixing two collations of the same charset is louder. Verified on 8.4:

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT)
and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

The cure for both is consistency: pick one charset and one collation, and convert everything that joins together, in one migration.

Moving between MySQL and MariaDB

One last trap for anyone restoring a MySQL 8 dump into MariaDB: MySQL's default collation utf8mb4_0900_ai_ci does not exist in older MariaDB. Verified on MariaDB 10.6:

ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

MariaDB 10.10 and later accept the 0900 names and map them to their uca1400 equivalents (confirmed on 11.4, where the same DDL succeeds). If you are importing into an older MariaDB, rewrite the collation in the dump first; our guide to importing a SQL file into MySQL covers the mechanics of the import itself.

The end state you want is boring: utf8mb4 columns, utf8mb4 connections, one collation everywhere, on defaults that every current MySQL and MariaDB already ship. The manual's utf8mb4 chapter is the reference when a legacy system will not let you get there in one step.

Tags: MySQL

SQL NULL traps: = NULL, NOT IN, and sort order

Posted by Kyle Hankinson August 7, 2026

A query that returns zero rows with zero errors is harder to debug than one that fails loudly, and NULL is behind more of those silent empties than everything else combined. The root cause is always the same: SQL comparisons involving NULL do not evaluate to true or false but to a third value, UNKNOWN, and a WHERE clause keeps only rows where the condition is TRUE. UNKNOWN rows are dropped without comment.

That one rule produces three distinct traps. All of the MySQL, PostgreSQL, and SQLite behavior below was run against MySQL 8.4, PostgreSQL 16, and SQLite 3.50.6; the SQL Server behavior is cited from Microsoft's documentation, linked where used.

Two tables are enough to demonstrate everything:

CREATE TABLE customers (id int, name varchar(20));
INSERT INTO customers VALUES (1, 'alice'), (2, 'bob'), (3, 'carol');

CREATE TABLE orders (customer_id int);
INSERT INTO orders VALUES (1), (NULL);   -- one order has no customer

Trap 1: WHERE col = NULL matches nothing

The natural way to find the orphaned order reads fine and returns nothing:

SELECT count(*) FROM orders WHERE customer_id = NULL;   -- 0
SELECT count(*) FROM orders WHERE customer_id IS NULL;  -- 1

Identical results on MySQL, PostgreSQL, and SQLite: the = version finds zero rows even though a NULL row is sitting right there. NULL means "unknown", and asking whether an unknown value equals another unknown value can only be answered "unknown", so the comparison never becomes TRUE for any row. SQL Server follows the same logic under its default settings; Microsoft's NULL and UNKNOWN page states that comparisons between two null values, or between a null value and any other value, return unknown, and directs you to IS NULL / IS NOT NULL.

When you genuinely want NULL-tolerant equality, where NULL equals NULL and nothing else, every engine has an operator for it, they just disagree on the spelling:

MySQL's comparison operators documentation notes that <=> is equivalent to the standard IS NOT DISTINCT FROM, so all four spellings mean the same thing.

Trap 2: one NULL turns NOT IN into an empty result

Now the trap with real teeth. Find the customers who have no orders:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

You would expect bob and carol. On MySQL 8.4, PostgreSQL 16, and SQLite 3.50.6 alike, this returns zero rows. Not a wrong list, an empty one, and the same three-valued logic explains it in SQL Server as well.

The subquery produces the list (1, NULL), and id NOT IN (1, NULL) expands to id <> 1 AND id <> NULL. That second comparison is UNKNOWN for every row in the table, and TRUE AND UNKNOWN is UNKNOWN, so no row ever qualifies. One stray NULL in the subquery quietly vetoes the entire result. This is the nastiest variety of NULL bug because the query works perfectly in development and then returns nothing in production the day the first NULL shows up in that column.

Two fixes, both verified to return bob and carol on all three engines. The direct one is to keep NULLs out of the list:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders
                 WHERE customer_id IS NOT NULL);

The better one is to stop using NOT IN for this job entirely:

SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.id);

NOT EXISTS asks "is there a matching row?" instead of comparing values, so NULLs in the subquery cannot poison it. It states the anti-join intent directly (the same family of shapes covered in SQL joins explained), and it is safe to use as a habit even on columns that are NOT NULL today, because columns have a way of becoming nullable later.

Trap 3: every engine sorts NULLs somewhere different

The first two traps behave identically everywhere. The third is where the engines part ways. Sort three values, one of them NULL:

CREATE TABLE s (v int);
INSERT INTO s VALUES (2), (NULL), (1);
SELECT v FROM s ORDER BY v ASC;

MySQL and SQLite treat NULL as smaller than every value, so it leads an ascending sort. PostgreSQL treats NULL as larger, so it trails. SQL Server sides with MySQL: the ORDER BY clause documentation states that NULL values are treated as the lowest possible values. The practical consequence: port a "latest items first, blanks at the bottom" query from MySQL to Postgres and the blanks migrate from bottom to top with no error and no warning.

Where the standard syntax exists, pinning the position is trivial. Verified on PostgreSQL 16 and SQLite 3.50.6:

SELECT v FROM s ORDER BY v ASC NULLS LAST;   -- 1, 2, NULL

On MySQL 8.4 that syntax is a hard error (ERROR 1064), but a boolean sort key does the same job, verified to return 1, 2, NULL:

SELECT v FROM s ORDER BY (v IS NULL), v;

v IS NULL is 0 for values and 1 for NULLs, so NULLs sink to the end. SQL Server lacks the syntax too; the equivalent trick there is ORDER BY CASE WHEN v IS NULL THEN 1 ELSE 0 END, v, using the conditional ordering pattern shown in the same ORDER BY documentation.

Chasing this class of bug across engines is considerably less painful when you can run the identical script against MySQL, PostgreSQL, SQL Server, and SQLite connections in one place and compare the grids, which is precisely the sort of side-by-side work SQLPro Studio exists for.

The habits that make all three traps a non-issue: write IS NULL rather than = NULL always, reach for NOT EXISTS rather than NOT IN under a subquery, and never let a query's correctness depend on where the engine happens to put NULLs in a sort. Declare it with NULLS LAST or a boolean sort key, and the query means the same thing everywhere.

Tags: MySQL PostgreSQL Microsoft SQL Server SQLite

Fixing MySQL Error 1055 (ONLY_FULL_GROUP_BY) Without Disabling It

Posted by Kyle Hankinson July 28, 2026

Quick answer: error 1055 means your SELECT list contains a column that is neither aggregated nor listed in GROUP BY, so MySQL cannot know which row's value you want. The fix is to change the query, not the server: add the column to GROUP BY, wrap it in an aggregate like MAX(), mark it explicitly arbitrary with ANY_VALUE(), or use a window function if you actually wanted the whole row. Disabling ONLY_FULL_GROUP_BY makes the error disappear and leaves the ambiguity in your results.

Now the longer version, with every example run on MySQL 8.4.10 and MariaDB 11.4.12.

Reproducing the error

Take a small orders table:

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  placed_at DATETIME NOT NULL
);

Ask for spending per customer, and casually include status:

SELECT customer_id, status, SUM(total)
FROM orders
GROUP BY customer_id;

On any default MySQL from 5.7.5 onward this fails:

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and
contains nonaggregated column 'shop.orders.status' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by

The error is precise once you translate it. Each group here is one customer_id, and a customer can have many orders with different statuses. You asked for one status per group without saying which one. Older MySQL happily picked one for you; ONLY_FULL_GROUP_BY (enabled by default since MySQL 5.7.5, and in every 8.x and 9.x release) refuses to guess.

Every column in the SELECT list must be one of three things: aggregated, listed in GROUP BY, or functionally dependent on the GROUP BY columns. That last one surprises people, so let's start there.

MySQL understands primary keys

Most write-ups of this error skip functional dependency entirely, which leads to painfully over-specified GROUP BY clauses. If you group by a table's primary key, MySQL knows every other column of that table is determined by it, and allows them without aggregation. This runs cleanly on 8.4:

SELECT c.id, c.name, SUM(o.total) AS lifetime_total
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

c.name appears nowhere in the GROUP BY, and there is no error, because grouping by the primary key c.id pins down exactly one name per group. So when a legitimate query trips 1055 after an upgrade, often the cleanest rewrite is to group by the key instead of by a non-unique column. The details are in the MySQL manual's GROUP BY handling page.

The three rewrites for genuinely ambiguous queries

When the column really is ambiguous, pick the rewrite that matches what you meant.

You wanted separate groups. If different statuses should be separate rows, say so:

SELECT customer_id, status, SUM(total) AS subtotal
FROM orders
GROUP BY customer_id, status;

You wanted a specific value. If you meant "the latest" or "the largest", use an aggregate:

SELECT customer_id, MAX(placed_at) AS last_order_at, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id;

Any value will do. ANY_VALUE() (available since MySQL 5.7) tells the server, explicitly and greppably, that you accept an arbitrary pick:

SELECT customer_id, ANY_VALUE(status) AS some_status, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id;

This is exactly the old pre-5.7 behavior, but declared in the query where reviewers can see it, instead of hidden in server configuration.

There is a fourth case that produces more 1055 errors than any other: you did not want an aggregate at all, you wanted the whole row that holds the group's maximum. MAX(placed_at) alongside status will not give you the status of the latest order. For that, rank the rows:

SELECT id, customer_id, status, total, placed_at
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC) AS rn
  FROM orders o
) ranked
WHERE rn = 1;

Window functions require MySQL 8.0 or MariaDB 10.2. We cover the pattern in depth in selecting only rows with the max value on a column, and the window function toolbox itself in SQL window functions: SUM, AVG, LAG and LEAD.

What disabling the check actually costs

Here is the part the "just remove ONLY_FULL_GROUP_BY" answers never show. MariaDB does not enable this mode by default in any version, so the ambiguous query from the top runs there without complaint. On MariaDB 11.4 it returned:

Looks plausible. But in the test data, customer 1's most recent order is pending and customer 2 has a cancelled order; the server simply returned whichever status it encountered first. No error, no warning, just a column of confidently wrong-looking-right values that will shift with storage order, indexes, and optimizer changes. That MariaDB difference is also why an application "works on MariaDB but breaks on MySQL 8": the query was ambiguous on both, and only MySQL said so.

If you maintain legacy code you truly cannot rewrite today, scope the override to your session rather than the server:

SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

Caution: prefer this session form over SET GLOBAL or an option-file change. A global change alters result semantics for every application on the server and silently converts future 1055 errors into indeterminate data. The sql_mode documentation covers scoping rules if you do need broader changes.

Verifying your rewrite

After rewriting, check the numbers, not just the absence of an error: a careless GROUP BY customer_id, status where you meant per-customer totals will happily return different sums. Running the original (under a session override) and the rewrite side by side is the fastest sanity check. In SQLPro for MySQL you can execute both statements in one editor tab and compare the result sets as separate grids, then export either to CSV or JSON if you want to diff them properly.

The one-line takeaway: 1055 is MySQL telling you your query has more than one correct answer. Pick one, write it down in SQL, and the error and the ambiguity disappear together.

Tags: MySQL

Fixing "Authentication plugin 'caching_sha2_password' cannot be loaded"

Posted by Kyle Hankinson July 9, 2026

You point an application at a MySQL 8 server and it fails before a single query runs:

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded:
/usr/lib/mysql/plugin/caching_sha2_password.so: cannot open shared object file:
No such file or directory

That is the real output from connecting a MySQL 5.6 era client to a stock mysql:8.0 Docker container. Nothing is wrong with your password or your grants. The client library is simply too old to speak the authentication protocol the server now uses by default.

What changed, and in which version

MySQL 8.0 switched the default authentication plugin from mysql_native_password (a SHA-1 scheme dating back decades) to caching_sha2_password, which uses SHA-256 and requires either a secure connection or an RSA key exchange during the first login. Every user created on a default MySQL 8 server gets the new plugin. On a fresh mysql:8.0 container:

SELECT user, host, plugin FROM mysql.user;

The story then tightened twice. Here is the state per version, each verified against the current Docker images:

This matters because the internet's most common advice for this error, "just switch the user back to mysql_native_password", stopped working by default in 8.4. Running it on MySQL 8.4.10 produces:

mysql> CREATE USER 'legacyapp'@'%' IDENTIFIED WITH mysql_native_password BY 'LegacyPw1!';
ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded

MySQL 9.7 returns the same error, and the server will not even start with the --mysql-native-password=ON option that 8.4 still accepts (9.x aborts with unknown variable 'mysql-native-password=ON').

The three faces of the same problem

Depending on your stack, the failure wears different messages:

"Authentication plugin 'caching_sha2_password' cannot be loaded" means the client library predates MySQL 8.0 and has no implementation of the plugin at all. Old libmysqlclient builds, PHP before 7.4, and ancient GUI tools produce this.

"The server requested authentication method unknown to the client" is the same root cause phrased by PHP's mysqlnd and some other drivers.

"Public Key Retrieval is not allowed" comes from MySQL Connector/J. The driver understands the plugin but refuses, by default, to fetch the server's RSA public key over an insecure connection. Enabling TLS or adding allowPublicKeyRetrieval=true to the JDBC URL resolves it (the latter trades away protection against spoofing, so prefer TLS).

"Authentication requires secure connection" appears even with a current client if the channel is insecure and RSA exchange is off. Reproduced on MySQL 8.4 over plain TCP:

$ mysql -h 127.0.0.1 -u app -p --ssl-mode=DISABLED
ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error:
Authentication requires secure connection.

Adding --get-server-public-key makes the same login succeed. There is one more wrinkle worth knowing: the plugin caches credentials server-side after a successful full authentication, so the insecure connection above starts working once any secure login for that user has primed the cache. If your error appears only sometimes, or only after a server restart, this cache is why.

The right fix: upgrade the client side

The durable fix is a client library from this decade, not a weaker server configuration. Minimum versions for common stacks:

• PHP: 7.4 or later. The PHP manual states caching_sha2_password is fully supported by mysqlnd as of 7.4.
• Java: Connector/J 8.x, with TLS enabled or allowPublicKeyRetrieval=true.
• C / CLI tools: any libmysqlclient or mysql client from 8.0 onward.
• Python: current releases of mysqlclient and PyMySQL both support the plugin (PyMySQL needs the cryptography package for the RSA path).
• MariaDB clients: recent MariaDB Connector/C releases speak caching_sha2_password. Testing the MariaDB 11.4 client against MySQL 8.4 succeeded, though it first failed with ERROR 2026: TLS/SSL error: self-signed certificate in certificate chain because MariaDB clients now verify server certificates by default. Against a server with a self-signed certificate you must either install a trusted certificate or pass --skip-ssl-verify-server-cert.

Desktop database clients bundle their own driver, so an outdated one fails against MySQL 8 no matter what you install system-wide. SQLPro for MySQL ships current client libraries that handle caching_sha2_password directly, and its SSH tunneling gives you a secure channel to servers that do not have TLS configured, which sidesteps the RSA requirement entirely.

The fallback, and why it is now a dead end

If you cannot upgrade a legacy client immediately, you can move individual users to the old plugin on MySQL 8.0:

ALTER USER 'legacyapp'@'%' IDENTIFIED WITH mysql_native_password BY 'a-new-password';

Caution: this changes how that account authenticates server-wide and resets its password, so every consumer of the account must be updated at the same time. Scope it to the one legacy account rather than reconfiguring the server default, and treat it as a bridge, not a destination: the same statement fails with error 1524 on 8.4 unless the server was started with --mysql-native-password=ON, and MySQL 9 removes the plugin outright. If you are creating a dedicated account for an old application anyway, our guide to creating users and granting privileges in MySQL covers the grant side.

Docker and configuration notes

On MySQL 8.0 images, the server default can be flipped at startup, which is why so many docker-compose files contain this line:

command: --default-authentication-plugin=mysql_native_password

Verified on mysql:8.0: with that flag, new users (including root) are created with mysql_native_password. On 8.4 the variable default_authentication_plugin no longer exists. Its replacement is authentication_policy, which on a stock 8.4 server reports *,, (first factor defaults to caching_sha2_password). To allow native-password accounts on 8.4 you need both --mysql-native-password=ON to load the plugin and, optionally, an authentication_policy change to make it a default. Neither option exists on 9.x.

One last cross-compatibility note: MariaDB never adopted caching_sha2_password. Its servers still default to mysql_native_password, so MySQL clients connect to MariaDB without any of this, while older MariaDB-based clients hitting a MySQL 8 server fail exactly like other legacy clients. If a connection works against MariaDB but dies against MySQL 8 with a plugin error, this default is the difference.

The MySQL manual's page on caching SHA-2 pluggable authentication documents the secure-channel rules in full, and MariaDB's authentication changes from 10.4 page covers the unix_socket side. Upgrade the client, keep caching_sha2_password on the server, and this error stays fixed through MySQL 9 instead of coming back at the next upgrade.

Tags: MySQL

SQL Window Functions: SUM, AVG, LAG, and LEAD

Posted by Kyle Hankinson January 25, 2026

Window functions perform calculations across a set of rows related to the current row — without collapsing the result into groups like GROUP BY does. They are one of the most powerful features in modern SQL.

The OVER() Clause

Every window function uses OVER() to define which rows to include:

SELECT name, department, salary,
    SUM(salary) OVER () AS total_salary
FROM employees;

This adds a total_salary column with the sum of all salaries — without grouping. Every row still appears individually.

PARTITION BY

PARTITION BY divides rows into groups (like GROUP BY, but without collapsing):

SELECT name, department, salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Each row shows the department total and average alongside the individual salary.

ORDER BY in OVER()

Adding ORDER BY creates a running calculation:

SELECT order_date, amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

LAG and LEAD

Compare the current row to previous or next rows:

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS month_over_month
FROM monthly_revenue;

LAG(col, n) looks back n rows (default 1). LEAD(col, n) looks forward n rows.

Default Values

Avoid NULLs for the first/last row:

LAG(revenue, 1, 0) OVER (ORDER BY month)  -- returns 0 instead of NULL

FIRST_VALUE and LAST_VALUE

SELECT name, department, salary,
    FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_paid
FROM employees;

Percent of Total

SELECT name, department, salary,
    ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 1) AS pct_of_dept
FROM employees;

Moving Average

Use a frame specification to average over a sliding window:

SELECT order_date, amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS seven_day_avg
FROM daily_sales;

Frame Types

Combining Multiple Window Functions

SELECT
    name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    ROUND(100.0 * salary / SUM(salary) OVER (), 2) AS pct_of_company
FROM employees
ORDER BY department, salary DESC;

WINDOW Clause (Reusable Definitions)

Avoid repeating the same OVER clause:

SELECT
    order_date, amount,
    SUM(amount) OVER w AS running_total,
    AVG(amount) OVER w AS running_avg,
    COUNT(*) OVER w AS running_count
FROM orders
WINDOW w AS (ORDER BY order_date);

Supported in PostgreSQL, MySQL 8.0+, and SQLite 3.28+. Not supported in SQL Server or Oracle.

Database Compatibility

Tags: MySQL PostgreSQL Microsoft SQL Server