DEV Community: Slava Rozhnev

Why `SUM() OVER (ORDER BY ...)` Sometimes Feels Wrong: A Practical Guide to SQL Window Frames

Slava Rozhnev — Wed, 11 Mar 2026 19:06:35 +0000

Window functions in SQL can make you feel productive very quickly. You learn PARTITION BY, add ORDER BY, use ROW_NUMBER(), RANK(), and running totals, and it feels like you already have the mental model.

That was exactly my mistake.

For a while, I thought I understood window functions well enough because my queries were working and the results looked plausible. The confusion only started later, when I began getting results that were syntactically correct but did not match what I expected logically.

A classic example looks like this:

SUM(amount) OVER (ORDER BY amount)

You expect a normal running total. Instead, the result suddenly jumps by multiple rows at once. No SQL error. No broken query. The database is doing exactly what you asked.

The missing piece is usually the same: the window frame.

The frame determines which rows around the current row are actually included in the calculation. Until that part clicks, window functions are easy to copy from memory but hard to control precisely.

For me, understanding frames was the point where window functions stopped feeling like a bag of handy tricks and started feeling like a consistent system.

When I want to test behavior like this quickly, I usually run the queries in a live environment. For quick experiments I use sqlize.online, and for more structured SQL practice and lessons I publish material on sqltest.online.

In this article, I want to walk through:

what a window frame actually is;
the difference between ROWS, RANGE, and GROUPS;
how boundaries like UNBOUNDED PRECEDING, CURRENT ROW, and n FOLLOWING work;
why the default frame can surprise you;
how to write running totals and moving averages without hidden assumptions.

What a window frame is

When we write a window function, we usually see something like this:

SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY payment_date
)

When I first started using queries like this, I mentally translated them as: “the function is calculated over the whole customer_id partition in payment_date order.”

That is not quite right.

PARTITION BY defines which partition the window works inside.

ORDER BY defines the order of rows inside that partition.

The window frame defines which subset of rows from that partition is used for the current row’s calculation.

The full shape looks like this:

function_name() OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    [ROWS | RANGE | GROUPS BETWEEN ... AND ...]
)

So the mental model is really three layers:

Choose the partition.
Define the ordering inside that partition.
For each row, define the frame: where it starts and where it ends.

Why this matters

The same SUM() can mean completely different things depending on the frame:

a running total from the start of the partition to the current row;
a 3-row sliding window;
an average across the current row and future rows;
a full-partition aggregate without collapsing rows.

From the outside, these queries can look very similar. Their behavior is not similar at all. That is why window functions can seem simple right up until you hit the first result that looks weird in production.

Frame boundaries

A frame is usually defined with BETWEEN ... AND ....

The available boundaries are:

UNBOUNDED PRECEDING — start from the first row in the partition;
n PRECEDING — go n rows or logical steps backward;
CURRENT ROW — the current row;
n FOLLOWING — go n rows or logical steps forward;
UNBOUNDED FOLLOWING — continue to the last row in the partition.

For example:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

That is the classic running-total frame: from the start of the partition up to the current row.

`ROWS`, `RANGE`, and `GROUPS`: the real difference

This is where things usually become interesting.

`ROWS`

ROWS works with physical rows.

If you write:

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

that always means exactly three rows:

the current row;
the two previous rows.

It does not matter whether those rows have the same ORDER BY value or not. The count is based on row positions.

This is the most predictable mode. In most practical analytics work, especially when I need a fixed-width sliding window, ROWS is usually where I start.

`RANGE`

RANGE works with a logical value range, not physical rows.

If multiple rows share the same ORDER BY value, they can enter the frame together as one logical group.

That is why RANGE often surprises people.

The most important detail is this: if you specify ORDER BY but do not define a frame explicitly, many databases use this default:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

That means the calculation includes not only all rows before the current row, but also all rows that share the same ordering value as the current row.

If your ORDER BY column contains duplicates, the result can jump more than you expect.

`GROUPS`

GROUPS works with peer groups of equal ORDER BY values.

If ROWS counts rows and RANGE thinks in logical value ranges, GROUPS counts groups of equal values.

For example:

GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW

means: take the current peer group and the previous peer group as whole units.

This is useful when your mental model is based on equal-value groups rather than individual rows. PostgreSQL supports it. Support in MySQL and MariaDB is more limited depending on version.

Example 1: running total

Let’s start with the most common case.

SELECT
    customer_id,
    payment_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

What happens here:

rows are partitioned by customer_id;
rows inside the partition are ordered by payment_date;
for each row, the sum runs from the first row in the partition up to the current row.

This is the explicit, correct running-total pattern.

In my own queries, I almost always write this frame explicitly, even if the database would happen to return the expected result without it. Writing the frame makes the behavior obvious and protects you from subtle surprises later.

Example 2: moving average

Now let’s use a fixed-width window:

SELECT
    customer_id,
    payment_date,
    amount,
    ROUND(
        AVG(amount) OVER (
            PARTITION BY customer_id
            ORDER BY payment_date
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS moving_avg_3
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

For each row, the frame includes:

the current row;
the two previous rows.

So the maximum frame width is three rows.

This is a typical case where ROWS is the right choice and RANGE is not. The goal is a fixed number of rows, not a logical expansion around equal values.

Example 3: looking forward

Window functions can look ahead as well:

SELECT
    customer_id,
    payment_date,
    amount,
    ROUND(
        AVG(amount) OVER (
            PARTITION BY customer_id
            ORDER BY payment_date
            ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
        ),
        2
    ) AS forward_avg
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

This kind of frame is useful for things like:

smoothing;
local trend analysis;
short forward-looking comparisons.

Near the end of the partition, the frame naturally shrinks because there are no future rows left.

Example 4: full-partition aggregate without `GROUP BY`

Sometimes you want to keep row-level detail and still show a partition-level aggregate next to each row:

SELECT
    customer_id,
    payment_date,
    amount,
    ROUND(
        AVG(amount) OVER (
            PARTITION BY customer_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ),
        2
    ) AS customer_avg
FROM payment
WHERE customer_id IN (1, 2)
ORDER BY customer_id, payment_date;

This behaves a bit like GROUP BY customer_id, except rows are not collapsed. You still see every row, with the partition average attached to each one.

That pattern is useful when you want to compare a row against its wider context:

deviation from average;
share of total;
comparison with a partition maximum or minimum.

The main trap: `ROWS` and `RANGE` can produce different running totals

Suppose you have multiple rows with the same amount.

Compare these two expressions:

SELECT
    customer_id,
    amount,
    SUM(amount) OVER (
        ORDER BY amount
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_rows,
    SUM(amount) OVER (
        ORDER BY amount
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_range
FROM payment
WHERE customer_id IN (1, 2, 3)
ORDER BY amount;

If amount = 11.99 appears multiple times, the behavior changes:

ROWS counts one physical row at a time;
RANGE includes all rows with the same amount together.

That is why a running total based on RANGE can jump several rows at once when there are duplicates in the ordering column.

This is one of the most common sources of confusion I see with window functions. The query is valid. The database is right. The expectation was wrong.

When I use `ROWS` vs `RANGE`

My rule of thumb is simple.

Use ROWS when you want:

row-by-row running totals;
moving averages based on a fixed number of rows;
predictable incremental behavior;
analytics where each physical row matters separately.

Use RANGE when you want:

calculations based on logical value ranges;
tied ORDER BY values to be treated together;
behavior tied to the ordering value itself rather than row count.

Use GROUPS when the right mental model is “peer groups as units.”

If I am not sure, I almost always start with ROWS. It is the most predictable option.

Named windows

When the same window definition is reused several times in one query, things get noisy fast. That is where the WINDOW clause helps:

SELECT
    customer_id,
    payment_date,
    amount,
    SUM(amount)   OVER w AS running_total,
    AVG(amount)   OVER w AS running_avg,
    COUNT(amount) OVER w AS payment_count
FROM payment
WHERE customer_id = 1
WINDOW w AS (
    PARTITION BY customer_id
    ORDER BY payment_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
ORDER BY payment_date;

Why I like this approach:

less duplication;
lower chance of mistakes;
easier maintenance;
the logic of the window lives in one place.

If a query contains several window functions, named windows usually make it much easier to read.

A practical reporting pattern: daily sales

One of the most useful patterns for reporting and dashboards looks like this:

SELECT
    DATE(payment_date) AS payment_day,
    SUM(amount) AS daily_total,
    SUM(SUM(amount)) OVER (
        ORDER BY DATE(payment_date)
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_total,
    ROUND(
        AVG(SUM(amount)) OVER (
            ORDER BY DATE(payment_date)
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS rolling_7day_avg
FROM payment
GROUP BY DATE(payment_date)
ORDER BY payment_day;

This gives you two very useful metrics immediately:

cumulative_total for the running total;
rolling_7day_avg for a 7-day moving average.

Notice the SUM(SUM(amount)) OVER (...) pattern: first we aggregate by day, then we apply a window function over the grouped result.

I like this example because it shows the practical value of frames very quickly. In one query, you get accumulation, smoothing, and a solid base for a chart or dashboard.

Where this topic kept breaking for me

If I reduce my own mistakes here to a short list, they usually came from three things:

mentally substituting “the whole partition” where only the current frame was actually used;
forgetting that RANGE does not behave like ROWS when ORDER BY values are duplicated;
relying too long on defaults instead of writing the frame explicitly.

Once I started asking one extra question for every window query, most of the confusion went away:

Which exact rows should be included in the calculation for the current row?

That question alone removes a lot of the magic.

What is worth remembering

A window frame is not about the partition itself and not about ordering by itself. It is specifically about the boundaries of rows included in the current calculation.

In short:

PARTITION BY splits data into partitions;
ORDER BY defines order inside a partition;
the frame defines which rows around the current row are included in the calculation.

The three most useful patterns to remember are:

running total:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

full partition:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

3-row sliding window:

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

And the most important trap is this:

if you have ORDER BY but no explicit frame, many databases will use RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Which means duplicate ordering values can change the result in ways that are easy to miss.

Final thought

Window functions only became intuitive for me once I stopped thinking in terms of just partitions and ordering and started thinking in terms of frames.

If you want to write analytical SQL with confidence, it is worth building one habit:

do not rely on the default frame;
write it explicitly;
choose between ROWS and RANGE on purpose;
remember that ties in ORDER BY change frame behavior.

If I had to leave one practical takeaway from this entire article, it would be this:

When you write a window function, do not think only about PARTITION BY and ORDER BY. Ask one more question: which exact rows should participate in the calculation for the current row?

Once that answer is explicit, window queries become much more reliable and much easier to reason about.

If you want to experiment with ROWS, RANGE, and GROUPS directly, sqlize.online is the easiest place to test queries quickly. If you want a more structured way to study SQL through lessons and practice, I publish that work on sqltest.online.

Make Your Technical Tutorials Interactive with SQLize Embed

Slava Rozhnev — Tue, 20 Jan 2026 13:19:48 +0000

If you've ever written a SQL tutorial or database documentation, you know the struggle. You provide a beautiful code snippet, but for your readers to actually see it in action, they have to:

Copy the code.
Open their local terminal or a heavy IDE.
Set up a database schema.
Run the code.

Most readers won't do it. They just keep scrolling.

Today, I'm excited to introduce SQLize Embed—a lightweight, responsive, and powerful way to embed live SQL sandboxes directly into your blog, documentation, or educational site.

What is SQLize Embed?

SQLize Embed is a client-side library that transforms static <div> elements into fully functional SQL editors. Powered by the SQLize.online engine, it allows users to write and execute SQL against real database instances without leaving your page.

Key Features

20+ Database Engines: Supports everything from the classics like MySQL 8.0/9.3, PostgreSQL (14-18), and SQLite 3, to enterprise giants like MS SQL Server (2017-2025) and Oracle 23ai.
Ready-to-Use Datasets: Want to demo a JOIN? Use preloaded databases like Sakila (MySQL/MariaDB), OpenFlights, or AdventureWorks (MS SQL).
Modern Editor Experience: Powered by the Ace Editor, providing syntax highlighting, auto-indentation, and a professional coding feel.
Responsive & Lightweight: Works seamlessly on mobile and desktop.
Read-Only Mode: Perfect for strictly showing examples that you want users to run but not modify.

Getting Started in 30 Seconds

Adding a live SQL sandbox to your site is as easy as adding a YouTube video.

1. Include the Script

Add this script tag to your site's <head> or before the closing </body> tag:

<script src="https://sqlize.online/js/sqlize-embed.js"></script>

2. Add Your Sandbox

Create a div with the data-sqlize-editor attribute. Specify your preferred database version and initial code:

<div data-sqlize-editor data-sql-version="mysql80" code-rows="10">
-- Create a sample table
CREATE TABLE dev_to_fans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100)
);

-- Insert data
INSERT INTO dev_to_fans (username) VALUES ('awesome_dev'), ('sql_ninja');

-- Run it!
SELECT * FROM dev_to_fans;
</div>

Advanced Configuration

You can customize the appearance and behavior of the sandbox using simple HTML attributes:

Attribute	Description	Default
`data-sql-version`	The DB engine (e.g., `psql17`, `mssql2025`, `clickhouse`)	`mysql80`
`code-rows`	The height of the editor in lines	`12`
`result-rows`	The height of the result area	`12`
`data-read-only`	Set to `true` to disable editing	`false`

Use Cases

Interactive Learning: Build a "SQL 101" course where users solve challenges directly in the browser.
Documentation: Stop using screenshots of tables. Let users run DESCRIBE table themselves.
Technical Blogs: Show off complex PostgreSQL window functions or the new MariaDB Vector types with live examples.

Try the Live Demo

Check out the live documentation and various examples here:
👉 SQLize Embed Documentation

👉 SQLize Embed Showcase

Join the Community!

We are constantly adding support for new database versions (we already have MS SQL Server 2025!). If you have a specific database or dataset you'd like to see, let us know in the comments!

Happy coding! 💻

sql #database #webdev #tutorial #productivity #programming

Debunking the Myth: Is JOIN Always Faster Than Correlated Subqueries?

Slava Rozhnev — Tue, 11 Nov 2025 19:18:20 +0000

Hey there, fellow developers! If you've ever dabbled in SQL, you've probably heard the golden rule: "Never use correlated subqueries in SELECT—they're a recipe for N+1 disasters!" Instead, we're told to always opt for JOINs because they're set-based, efficient, and lightning-fast.

But is this rule set in stone? I decided to put it to the test across four popular database systems: MySQL 8.0, Oracle 23c, PostgreSQL 16, and SQLite 3.45. Spoiler alert: The results were eye-opening. Sometimes, the "bad" correlated subquery outperformed the "good" JOIN. Let's dive in and see why.

The Test Setup: Customers and Orders
To keep things fair, I used a simple schema with two tables:

customers: A small table with 25 rows of customer data.
orders: A larger table with 1,000 rows of orders, linked via a foreign key.
The goal? Count the number of orders per customer, including those with zero orders.

Here's the schema (using MySQL syntax for reference):

-- Customers table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- Orders table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATETIME,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);

Data was populated with random values to simulate real-world scenarios.

The Two Queries: JOIN vs. Correlated Subquery
I compared two approaches to achieve the same result.

The "Good" Way – JOIN + GROUP BY This is the set-based, relational approach everyone loves:

SELECT 
    c.customer_id, 
    COUNT(o.order_id) AS orders_count
FROM 
    customers c
LEFT JOIN 
    orders o ON c.customer_id = o.customer_id
GROUP BY 
    c.customer_id;

Pros: Handles all customers, even those without orders.
Theory: One optimized operation to join and aggregate.

The "Bad" Way – Correlated Subquery This is the row-by-row method we're warned against:

SELECT 
    c.customer_id, 
    (SELECT COUNT(o.order_id) 
     FROM orders o 
     WHERE o.customer_id = c.customer_id) AS orders_count
FROM 
    customers c;

Pros: Also includes customers with zero orders.
Theory: Executes a subquery for each customer—classic N+1 problem.

Testing Across Databases: The Results
I ran both queries on online SQL testers (links provided below) and analyzed execution times and plans using EXPLAIN. Here's what happened.

MySQL 8.0: Subquery Wins!
Execution Times: Subquery ~14 ms vs. JOIN ~16 ms.
Why? The subquery triggered a Nested Loop plan with fast index lookups (25 quick searches). JOIN used Hash Join + Aggregate, which was overkill for small data.
Key Insight: With an index on orders.customer_id, the subquery wasn't N+1—it was efficient Nested Loops.
Test Link: MySQL Tester

Oracle 23c: Subquery Dominates!
Execution Times: Subquery ~2.4 ms vs. JOIN ~15 ms.
Why? Similar to MySQL—Nested Loop for subquery vs. Hash Join for JOIN. The subquery avoided heavy aggregation overhead.
Key Insight: Indexes are crucial; without them, Oracle falls back to full scans.
Test Link: Oracle Tester

PostgreSQL 16: JOIN Takes the Lead
Execution Times: JOIN ~0.6 ms vs. Subquery ~1.9 ms.
Why? PostgreSQL's optimizer rewrote the subquery into a JOIN-like plan, but the explicit JOIN was slightly faster. Subquery showed 25 sub-plan executions (mild N+1).
Key Insight: PostgreSQL is smart—indexes level the playing field.
Test Link: PostgreSQL Tester

SQLite 3.45: A Tie!
Execution Times: Both ~1 ms.
Why? Plans were nearly identical: SCAN on customers + SEARCH on orders via index. No N+1 effect.
Key Insight: SQLite's simplicity made both queries efficient; choose based on readability.
Test Link: SQLite Tester

Key Takeaways: No Silver Bullet
The "JOIN is always faster" myth crumbles because performance depends on:

Database Optimizer: PostgreSQL rewrites queries; MySQL/Oracle follow your syntax more literally.
Data Size: Small outer tables (like our 25 customers) favor Nested Loops; large ones benefit from Hash Joins.
Indexes: Without an index on orders.customer_id, subqueries tank. With it, they shine.
Bottom Line: Don't blindly follow rules. Always run EXPLAIN (or EXPLAIN ANALYZE) to see the actual execution plan. Test with your data!

What are your experiences with JOINs vs. subqueries? Drop a comment below!

This article is based on real testing and analysis. Links to testers are provided for you to verify the results.

SQL Tricks: Generate Calendar Table

Slava Rozhnev — Sat, 19 Jul 2025 19:34:49 +0000

Creating a "calendar table" or "date dimension" is a common task in SQL, especially for reporting, data warehousing, or when you need to perform calculations based on dates that might not exist in your actual data (e.g., finding days with no sales). While a full-fledged calendar table usually contains many attributes (day of week, week number, quarter, holiday flags, etc.), sometimes you just need a simple list of dates for a specific period, like the current month.

In this post, we'll explore how to dynamically generate a table containing all dates for the current month across different popular RDBMS dialects: MySQL, PostgreSQL, MS SQL Server, and Oracle. This approach avoids hardcoding dates and ensures your script always works for the current period.

Why generate a calendar table?
Before we dive into the code, let's briefly touch upon why this is useful:

Filling Gaps: If your transaction data only records days with activity, a calendar table can help you identify days with no activity (e.g., zero sales).
Time-Series Analysis: Essential for analyses that require a continuous timeline, even when data is sparse.
Simplifying Joins: You can join your data to a calendar table to easily group by specific date parts or filter by continuous date ranges.
Reporting: Providing a complete date range for reports, even if some dates have no associated data.

Let's look at the solutions for each database system.

MySQL

MySQL offers a few ways to generate series. We'll use a common table expression (CTE) combined with a recursive CTE or a numbers table approach to generate our dates.

WITH RECURSIVE dates AS (
    SELECT
        DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY) AS dt -- First day of current month
    UNION ALL
    SELECT
        DATE_ADD(dt, INTERVAL 1 DAY)
    FROM
        dates
    WHERE
        dt < LAST_DAY(CURDATE()) -- Last day of current month
)
SELECT
    dt AS calendar_date
FROM
    dates;

Explanation:

WITH RECURSIVE dates AS (...): Defines a recursive CTE named dates.
Anchor Member: SELECT DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY): This calculates the first day of the current month. CURDATE() gets the current date, DAYOFMONTH(CURDATE()) gets the day of the month (e.g., 15 for July 15th), and we subtract (day - 1) days to get to the 1st of the month.
Recursive Member: SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM dates WHERE dt < LAST_DAY(CURDATE()): This part adds one day to the previous date (dt) until it reaches the last day of the current month, which is obtained using LAST_DAY(CURDATE()).

Try this solution with SQLize.online

PostgreSQL

PostgreSQL has a very convenient generate_series() function which is perfect for this task.

SELECT
    GENERATE_SERIES(
        DATE_TRUNC('month', CURRENT_DATE), -- First day of current month
        (DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month' - INTERVAL '1 day')::date, -- Last day of current month
        '1 day'
    )::date AS calendar_date;

Explanation:

GENERATE_SERIES(start, stop, step): Generates a series of values.
DATE_TRUNC('month', CURRENT_DATE): Truncates the current date to the beginning of the month, giving us the first day.
(DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month' - INTERVAL '1 day'):📅 This calculates the last day of the current month. We go to the beginning of the next month (+ INTERVAL '1 month') and then subtract one day (- INTERVAL '1 day') to get the last day of the current month.
'1 day': Specifies that each step in the series should be one day.
:📅 Casts the resulting timestamp to a date type for a cleaner output.

Test tris code on SQLize.online

MS SQL Server

SQL Server offers multiple ways to generate sequences. Since SQL Server 2022, the GENERATE_SERIES function provides a straightforward method, similar to PostgreSQL. For earlier versions, or if you prefer, recursive CTEs are also a good option.

Using GENERATE_SERIES (SQL Server 2022+)

SELECT
    DATEADD(day, value, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) AS calendar_date
FROM
    GENERATE_SERIES(0, DAY(EOMONTH(GETDATE())) - 1);

Explanation:

GENERATE_SERIES(start, stop, step): This function generates a sequence of numbers from start to stop with increments of step. By default, step is 1 if omitted.
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0): This calculates the first day of the current month. This will be our base date.
DAY(EOMONTH(GETDATE())) - 1: EOMONTH(GETDATE()) returns the last day of the current month. DAY() extracts the day number (e.g., 31 for July 31st). Subtracting 1 gives us the maximum number to generate for our series (from 0 to days_in_month - 1). For example, if a month has 31 days, we want to generate numbers from 0 to 30.
DATEADD(day, value, ...): For each value generated by GENERATE_SERIES (which are 0, 1, 2, ... up to days_in_month - 1), we add that number of days to our first day of current month base date. This effectively generates each date of the month.

Try this SQL online

Using Recursive CTE (Older SQL Server Versions or Alternative)

WITH Dates AS (
    SELECT
        DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS dt -- First day of current month
    UNION ALL
    SELECT
        DATEADD(day, 1, dt)
    FROM
        Dates
    WHERE
        DATEPART(month, DATEADD(day, 1, dt)) = DATEPART(month, GETDATE())
)
SELECT
    dt AS calendar_date
FROM
    Dates
OPTION (MAXRECURSION 366); -- Set max recursion limit, 366 covers leap years

Explanation:

WITH Dates AS (...): Defines a recursive CTE named Dates.
Anchor Member: SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS dt: This is a common SQL Server idiom to get the first day of the current month.
DATEDIFF(month, 0, GETDATE()): Calculates the number of month boundaries crossed between 0 (which SQL Server treats as January 1, 1900) and GETDATE().
DATEADD(month, ..., 0): Adds that number of months to 0, effectively landing on the first day of the current month.
Recursive Member: SELECT DATEADD(day, 1, dt) FROM Dates WHERE DATEPART(month, DATEADD(day, 1, dt)) = DATEPART(month, GETDATE()): Adds one day to dt as long as the month of the next date is still the current month.
OPTION (MAXRECURSION 366): Important for recursive CTEs in SQL Server. It sets the maximum number of times the recursive part can execute. 366 is a safe number to cover all possible days in a year (including leap years).

Try legacy SQL Server code

Oracle

Oracle provides a powerful CONNECT BY LEVEL clause, often used for generating sequences.

SELECT
    TRUNC(SYSDATE, 'MM') + LEVEL - 1 AS calendar_date
FROM
    dual
CONNECT BY
    TRUNC(SYSDATE, 'MM') + LEVEL - 1 <= LAST_DAY(SYSDATE);

Explanation:

TRUNC(SYSDATE, 'MM'): Truncates the current date (SYSDATE) to the first day of the current month.
LEVEL: A pseudo-column in hierarchical queries that returns the current level in the hierarchy (starting from 1).
TRUNC(SYSDATE, 'MM') + LEVEL - 1: Generates successive dates starting from the first day of the month.
When LEVEL is 1, it's first_day_of_month + 1 - 1 = first_day_of_month.
When LEVEL is 2, it's first_day_of_month + 2 - 1 = first_day_of_month + 1 day.
FROM dual: dual is a dummy table in Oracle, often used for selecting pseudo-columns or evaluating expressions.
CONNECT BY TRUNC(SYSDATE, 'MM') + LEVEL - 1 <= LAST_DAY(SYSDATE): This clause acts as the loop condition. It continues generating rows as long as the generated date is less than or equal to the last day of the current month (LAST_DAY(SYSDATE)).

Oracle SQL playground online

Conclusion

As you can see, while the syntax differs across RDBMS, the core concept of generating a series of dates remains similar. With the addition of GENERATE_SERIES in SQL Server 2022, modern SQL versions are increasingly standardizing on easier ways to achieve this. Understanding these techniques is crucial for effective data manipulation and reporting in SQL. Choose the method that best suits your specific database environment and version.

I hope this was helpful! Feel free to leave a comment if you have any questions or alternative approaches.

SQLtest.online - the site where you can test your SQL skills

Slava Rozhnev — Sat, 17 Feb 2024 12:00:35 +0000

Hello friends! I'll tell you about my new project - SQLTest.online. This is a platform for those who are learning SQL and want to test their skills by solving practical problems.

The using of the site is very simple - select a problem from the list on the left and try to solve it! So far there are more then 220 questions on MySQL, several questions on Firebird. For tests, public databases Sakila (MySQL), Bookings (PostgreSQL) and Employee (Firebird) are used. In the future, I plan to expand both the list of available databases and the number of questions for each of them.

The table structure of the database relevant for each task is displayed on the right side of the screen and helps you in solving.

If you are at a loss with a solution, you can use the hint or run a query and see the result. Validation of a solution is performed in two stages: a basic check using regular expressions and then checking the result against the correct solution.

If you are interested in the project and decide to pass all the tests, you can log in to the site. Login is currently available via Google, Yandex and GitHub. When you log in to the site, no personal information is collected, only data about your progress in solving problems. However, even without registration, you get access to all functions of the site without restrictions!

So, Happy testing with SQLTest.online

If you find problems on the site, write about them below in the comments to the article.

Generate Date Series in popular Databases

Slava Rozhnev — Wed, 23 Aug 2023 20:43:15 +0000

In this post I want to answer to frequently asked question: How I can generate date series between to particular dates?

Generating a date series between two particular dates can be done using different methods depending on the relational database management system (RDBMS) you are using. I'll provide examples for a few popular RDBMS systems: MySQL, PostgreSQL, and Microsoft SQL Server.

Please note that the syntax might slightly differ based on the specific version of the RDBMS you're using, so you should consult the documentation for your specific version if you encounter any issues.

MySQL

Legacy MySQL (5.7.*)
The old MySQL doesn't have built-in functions to generate a date series, so you might need to use a temporary table or a numbers table. Here's an example using a numbers table approach:

CREATE TEMPORARY TABLE Numbers (n INT);
-- Insert numbers up to the desired range
INSERT INTO Numbers VALUES (0), (1), (2), ...;  

SELECT 
    DATE_ADD('start_date', INTERVAL n DAY) AS generated_date
FROM Numbers
WHERE 
    DATE_ADD('start_date', INTERVAL n DAY) <= 'end_date';

Just replace 'start_date' and 'end_date' with your desired start and end dates and try it on SQLize.online.

In Modern MySQL 8.0.*, you can use a Common Table Expression (CTE) to generate a date series between two particular dates. Here's how you can do it:

SET @start_date = '2022-01-01';
SET @end_date = '2022-01-31';

WITH RECURSIVE DateSeries AS (
    SELECT @start_date AS generated_date
    UNION ALL
    SELECT DATE_ADD(generated_date, INTERVAL 1 DAY)
    FROM DateSeries
    WHERE generated_date < @end_date
)
SELECT generated_date
FROM DateSeries;

Explanation:

The WITH RECURSIVE clause defines the CTE named DateSeries.
In the initial SELECT statement within the CTE, we set the anchor value to the start date.
In the recursive SELECT statement, we use the DATE_ADD function to increment the date by one day for each iteration.
The WHERE clause in the recursive SELECT statement ensures that the recursion continues until the generated date is less than the end date.
Finally, the outer SELECT statement selects all the generated dates from the CTE.

Remember that recursive queries can be resource-intensive, so use them cautiously and only when necessary. Try the query here

PostgreSQL

PostgreSQL has the generate_series function that makes this task easy:

SELECT generate_series('2022-01-01'::date, '2022-01-31'::date, '1 day') AS generated_date;

Replace 'start_date' and 'end_date' with your desired start and end dates.

Microsoft SQL Server

SQL Server also has a similar approach using the sys.dates system table and the DATEADD function:

DECLARE @start_date DATE = '2022-01-01'
DECLARE @end_date DATE = '2022-01-31'

SELECT TOP 
    (DATEDIFF(day, @start_date, @end_date) + 1)
    generated_date = DATEADD(day, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @start_date)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;

Since SQL Server 2022 where implemented GENERATE_SERIES function you can use it for generate dates series too in next way:

SELECT 
    DATEADD(day, value, '2022-01-01') AS Date
FROM GENERATE_SERIES(0, DATEDIFF(day, '2022-01-01', '2022-01-31'))

Oracle

SELECT DATE '2022-01-01' + LEVEL - 1 AS generate_series
FROM dual
CONNECT BY LEVEL <= DATE '2022-01-31' - DATE '2022-01-01' + 1

Another cool method:

SELECT TRUNC (DATE '2023-01-01' + ROWNUM) dt
  FROM DUAL CONNECT BY ROWNUM < 31

If you know more methods to get date series in different RDBMS, please post in comments

Exploring PostgreSQL's EXCLUDE Operator: Advanced Data Constraints

Slava Rozhnev — Tue, 30 May 2023 22:05:45 +0000

Introduction

In the process of designing a database, as described in my previous article, I decided to utilize the EXCLUDE constraint to maintain data integrity. While contemplating this, I realized that the EXCLUDE operator deserves a dedicated article.

Introduction to the EXCLUDE Operator

PostgreSQL is renowned for its numerous powerful features, and one of them is the EXCLUDE operator. This operator allows you to create advanced constraints on sets of values within table columns. In this article, I want to delve into the EXCLUDE operator, provide examples of its usage, and help you understand how to leverage it to build flexible and efficient databases.

Similar to the UNIQUE constraint in PostgreSQL, the EXCLUDE operator is used to define constraints on sets of values within table columns. However, unlike UNIQUE, it enables you to specify rules that determine which values cannot coexist within a particular column or set of columns. The EXCLUDE operator is often used with GiST or SP-GiST index types to ensure query efficiency, although it can also be used with a regular B-Tree index.

Examples of Usage

A common example of utilizing EXCLUDE is applying a constraint on overlapping time intervals, such as movie screenings in a cinema.

CREATE TABLE events (
    id serial primary key,
    event_time tstzrange,
    constraint no_screening_time_overlap exclude using gist (
        event_time WITH &&
    )
);

INSERT INTO events (event_time) VALUES ('["2023-01-01 19:00:00", "2023-01-01 20:45:00"]');

In the above example, we create a table named "events" and insert a record with a time interval. You can check the SQL on SQLize.online. Afterwards, you can try inserting another row with an interval that overlaps with an existing one in the table. Most likely, it will result in an error. If you manage to succeed, let me know in the comments!

Similar to UNIQUE, the EXCLUDE constraint can be applied to a group of columns. For instance, you can use the "event_start" and "event_end" columns of type timestamp and restrict time overlaps. Here's an example:

CREATE TABLE events (
    event_id serial primary key,
    event_name VARCHAR(100) NOT NULL,
    event_start TIMESTAMPTZ NOT NULL,
    event_end TIMESTAMPTZ NOT NULL,
    EXCLUDE USING GIST (event_start WITH &&, event_end WITH &&)
);

Constraints on numeric ranges can also be imposed using EXCLUDE. Take a look at this example:

CREATE TABLE ranges (
    range_id serial primary key,
    start_value INTEGER NOT NULL,
    end_value INTEGER NOT NULL,
    EXCLUDE USING GIST (int4range(start_value, end_value, '[]') WITH &&)
);

In this example, the "ranges" table is created, which contains numeric ranges. The EXCLUDE operator with a GiST index specifies that the numeric ranges in the "start_value" and "end_value" columns cannot overlap.

Another significant application is constraining the intersection of geometric figures:

CREATE TABLE polygons (
    polygon_id serial primary key,
    polygon_data geometry(Polygon) NOT NULL,
    EXCLUDE USING GIST (polygon_data WITH &&)
);

Here, the "polygons" table is created, which stores information about polygons. The EXCLUDE operator with a GiST index ensures that geometric objects in the "polygon_data" column cannot intersect or be contained within each other.

In all the above examples, we utilized the EXCLUDE constraint based on a GiST index. However, for completeness, let's provide an example using an R-Tree:

CREATE TABLE users (
    user_id serial primary key,
    email VARCHAR(255) NOT NULL,
    EXCLUDE USING btree (lower(email) WITH =)
);

In this example, we nearly replicated the functionality of the UNIQUE constraint with a slight modification. Our uniqueness is now case-insensitive.

Conclusion

The EXCLUDE operator in PostgreSQL offers the ability to create advanced constraints on sets of values within table columns. It allows you to define rules that restrict combinations of values that cannot coexist. This is particularly useful for ensuring data integrity and performing complex checks at the database level.

In this article, we explored several examples of using the EXCLUDE operator, including constraints on overlapping time intervals, prohibition of intersecting geometric objects, and constraints on non-overlapping numeric ranges. The EXCLUDE operator is a powerful tool that can be employed to build flexible and efficient databases in PostgreSQL.

In your projects, utilize the EXCLUDE operator to create sophisticated constraints and ensure data integrity at the database level. This will help you maintain the structure and reliability of your database while optimizing its usage.

If you found this article helpful, you can show your support to the author.

MySQL 8.0: Invisible Columns

Slava Rozhnev — Tue, 16 May 2023 12:03:39 +0000

The Oracle company ported INVISIBLE COLUMN feature to it's little brother MySQL. Since version 8.0.23 the invisible column feature available for MySQL users.

Let's learn how it works!

You can create invisible column as regular when you create a table using INVISIBLE keyword:

CREATE TABLE test_table (
  a INT,
  b DATE INVISIBLE
) ENGINE = InnoDB;

or add new column to an exists table by ALTER TABLE command:

ALTER TABLE test_table ADD COLUMN c INT INVISIBLE;

When a column exists you can change its visibility using CHANGE, MODIFY or ALTER COLUMN command as you can see below:

ALTER TABLE test_table CHANGE COLUMN b b DATE VISIBLE;
ALTER TABLE test_table MODIFY COLUMN b DATE INVISIBLE;
ALTER TABLE test_table ALTER COLUMN c SET VISIBLE;

When you fetch table structure you will see all columns, but part of them will be marked by INVISIBLE flag:

SHOW COLUMNS FROM test_table;
SHOW CREATE TABLE test_table;

So you see, the invisible column manipulation pretty simple. Now look how the visibility affects DML.

First, when you try to select all columns using SELECT *, the invisible columns are not appears. So if you need to see they, you must to know the column name and select it by its name. Look below SQL code examples:

TABLE test_table; SELECT * FROM test_table; -- column hidden

SELECT a, b, c FROM test_table; -- column visible

Same way, when you need to insert data to invisible column you must call it by name:

INSERT INTO test_table VALUES (1, now(), 33); -- Error
INSERT INTO test_table () VALUES (1, now(), 33); -- Error too

INSERT INTO test_table VALUES (1, 22); -- NULL inserted
INSERT INTO test_table (a, b, c) VALUES (1, now(), 33); -- all values

Where you can use this feature? First - security - you can use invisible column for hide some column from DB users with low permissions. Second - when you use generated column you can prevent insert data to such column doing it invisible.
Look next example:

CREATE TABLE test_table (
  a INT,
  b INT
) ENGINE = InnoDB;

INSERT INTO test_table VALUES (1, 2), (2, 3), (3, 4);

ALTER TABLE test_table ADD COLUMN a_b_sum INT AS (a + b);

SELECT * FROM test_table;

In above example we added generated column to our test_table but this change can break our insert query. What we can do in this case? Right - use invisible column.

ALTER TABLE test_table ALTER COLUMN a_b_sum SET INVISIBLE;

INSERT INTO test_table VALUES (5, 7), (2, 9), (2, 3);

SELECT a, b, a_b_sum FROM test_table;

Here you can run SQL queries online and test this feature.

So, this is all about this feature. If you think about more use-cases please share it in comments.

The power of MERGE in PostgreSQL 15

Slava Rozhnev — Mon, 01 May 2023 14:22:32 +0000

The MERGE statement is a powerful tool that can be used to perform conditional INSERT, UPDATE, or DELETE operations on rows in a table. According Wikipedia MERGE operation was officially introduced in the SQL:2003 standard, and expanded in the SQL:2008 standard.

MERGE is similar to the UPSERT statement in other SQL dialects, but it offers a number of advantages, including:

It is more efficient, as it only performs the necessary operations for each row.
It is more atomic, as it either succeeds or fails as a whole.
It is more flexible, as it allows for more complex conditions to be specified.

Before PostgreSQL 15 merge was possible using INSERT INTO ... ON CONFLICT [ conflict_target ] conflict_action but now we can use all advantages of MERGE

The syntax for the MERGE statement is as follows:

MERGE INTO target_table AS t
USING source_table AS s
ON <condition>
WHEN MATCHED THEN
  <update_statement>
WHEN NOT MATCHED THEN
  <insert_statement>

The target_table is the table that will be updated or inserted into. The source_table is the table that contains the data that will be used to update or insert rows in the target_table. The condition is a Boolean expression that is used to determine whether a row in the source_table should be updated or inserted. The update_statement is a SQL statement that is used to update rows in the target_table. The insert_statement is a SQL statement that is used to insert rows into the target_table.

Here is an example of a MERGE statement that update table customers using leads table. Both tables have email field, so we need to add only leads that email not exists in customers table

MERGE INTO customers AS c
USING leads AS l
ON c.email = l.email
WHEN NOT MATCHED THEN
  insert (name, email, created_at)
  VALUES (l.name, l.email, DEFAULT);

This statement will first perform a join between the customers table and the leads table. The join will only return rows in the leads table where the email address is not exits in customers. For each row that is returned, the insert query will be performed.

This query equivalent next UPSERT query used before:

insert into customers (name, email)
select name, email from leads
on conflict (email) do nothing;

but if we look results we already see the MERGE advantage. It not increment id field in case when record already exists.

Just run both SQL queries and compare results

Let try more complicated task. Now we add leads with new email and update name for exist

MERGE INTO customers AS c
USING leads AS l
ON c.email = l.email
WHEN NOT MATCHED THEN
  INSERT (name, email, created_at)
  VALUES (l.name, l.email, DEFAULT)
WHEN MATCHED THEN UPDATE
    SET name = l.name
;

Here you can run SQL

And in last scenario we will add leads with absent emails, update name where customer creation date less then matched leads record and delete other:

MERGE INTO customers AS c
USING leads AS l
ON c.email = l.email
WHEN NOT MATCHED THEN
  INSERT (name, email, created_at)
  VALUES (l.email, l.name, DEFAULT)
WHEN MATCHED AND c.created_at < l.created_at THEN UPDATE
    SET name = l.name
WHEN MATCHED THEN
    DELETE
;

In article conclusion we can say:
The MERGE statement is a powerful tool that can be used to perform conditional INSERT, UPDATE, or DELETE operations on rows in a table. It is more efficient, more atomic, and more flexible than the UPSERT statement in other SQL dialects.

The old MySQL bug (detective story)

Slava Rozhnev — Mon, 24 Apr 2023 11:47:12 +0000

In this article I want to describe about one bug that I found in MySQL and aware you from mistakes what you can do.

Some guy from my Telegram chat asked me: "How can I view all foreign key references to a specific table?" I gave him a quick answer: "Look it up in information_schema", but after that I decided to check my answer.

Just created simple table test and table ref1 with field referenced to id field:

create table test (
 id int primary key,
 first_name varchar(20), 
 last_name varchar(30)
);

create table ref1 (
 id int primary key,
 test_id int references test(id)
);

Looks simple because I like this short syntax. After that I looked into information_schema table REFERENTIAL_CONSTRAINTS

SELECT * FROM `information_schema`.`REFERENTIAL_CONSTRAINTS` WHERE REFERENCED_TABLE_NAME = 'test';

and checked again. After that I sowed records in REFERENTIAL_CONSTRAINTS. How is it possible? I was disappointed and decided to save my mind by next test case:

I created one more referenced table ref2 with canonical syntax:

create table ref2 (
 id int primary key,
 test_id int,
 foreign key (test_id) references test(id)
);

and did check again. After that I sow record in REFERENTIAL_CONSTRAINTS. How it possible? I was disappointed and decided to save my mind by next test case:

-- add values to test case
insert into test values (1), (2);

-- add ref1 row referenced to first row in test table
insert into ref1(id, test_id) values (1, 1);

-- add ref2 row referenced to second row in test table
insert into ref2(id, test_id) values (1, 2);

Now, I think, I will try to delete first row from test and will get error (I still think it some feature, not bug) but MySQL drop this row without any warning

delete from test where id = 1;

but when I try to delete second one that referenced by second table I did not succeed

delete from test where id = 2;

So it is not a feature, it is a real BUG! MySQL allows creating tables using short syntax without warnings but constraints are not created and do not guard your data consistently OMG!

I looked in Google and found the bug has been open since 2004 https://bugs.mysql.com/bug.php?id=4919 and it is still not fixed. Hey guys, what are you doing? Hey guys what you during?

I decided to run this test case over other databases and found all databases that I can test on SQLize.online allows short syntax and all of them (MariaDB, PostgreSQL, SQL Server, Oracle) created foreign key constraints in both of cases except MySQL and SQLite.

Everything you wanted to know about Auto Increment but were afraid to ask

Slava Rozhnev — Mon, 03 Apr 2023 19:02:46 +0000

Auto-increment is a feature in databases that automatically generates a unique number for each new row added to a table. This number is usually used as a primary key to uniquely identify each row in the table. The database system automatically assigns the next available number to each new row, saving the user from having to manually manage the primary key values. This simplifies the process of adding new rows and ensures that each row has a unique identifier.

Below we can see how to create table with auto-increment column in different RDBMS

SQLite:

CREATE TABLE example_table (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   name VARCHAR(50),
   age INT
);

In this example, the id column is defined as an integer data type with the AUTOINCREMENT keyword. It is also set as the primary key of the table.

MySQL and MariaDB:

CREATE TABLE example_table (
   id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(50),
   age INT
);

In this example, the id column is defined as an auto increment column with the AUTO_INCREMENT keyword. It is also set as the primary key of the table.

SQL Server:

CREATE TABLE example_table (
   id INT IDENTITY(1,1) PRIMARY KEY,
   name VARCHAR(50),
   age INT
);

In this example, the id column is defined as an identity column with the IDENTITY keyword. It is also set as the primary key of the table. The numbers 1,1 specify the starting value and increment value of an auto-incrementing column.
For example, we can start numbering with 100 and add 10 (100, 110, 120, 130...) each next row by replacing IDENTITY(1,1) with IDENTITY(100, 10). Moreover, negative numbers can be used as a sequence start and step. Try it yourself here

Oracle:

CREATE SEQUENCE example_table_seq;

CREATE TABLE example_table (
   id NUMBER DEFAULT example_table_seq.nextval PRIMARY KEY,
   name VARCHAR2(50),
   age NUMBER
);

In this example, a sequence is created first with the CREATE SEQUENCE statement. Then the id column is defined as a number data type with a default value of the next value of the sequence. It is also set as the primary key of the table. The sequence example_table_seq started with 1 and step 1. When we need to customize it we can use INCREMENT BY and START WITH parameters:

CREATE SEQUENCE example_table_seq 
    INCREMENT BY 10
    START WITH 10;

PostgreSQL have several options for define auto-increment column. The most popular is SERIAL keyword

CREATE TABLE example_table (
   id SERIAL PRIMARY KEY,
   name VARCHAR(50),
   age INT
);

In this example, the id column is defined as a serial data type, which is equivalent to an auto increment column in other DBMS. It is also set as the primary key of the table.

Also, you can use both IDENTITY and SEQUENCE syntax in PostgreSQL to create an auto-increment column. Here are the examples of creating an auto-increment column using IDENTITY and SEQUENCE syntax in PostgreSQL:

CREATE TABLE example_table (
   id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   name VARCHAR(50),
   age INT
);

CREATE SEQUENCE example_table_seq;

CREATE TABLE example_table (
   id INT DEFAULT nextval('example_table_seq') PRIMARY KEY,
   name VARCHAR(50),
   age INT
);

To insert a new row into table with auto-increment column, we can use the INSERT INTO statement, specifying for all columns except auto-increment:

INSERT INTO example_table (name, age) VALUES ('John Smith', 30);

Most of SQL dialects allows to insert several rows in single query.

INSERT INTO example_table (name, age)
VALUES
  ('Jane Doe', 25),
  ('Bob Johnson', 40),
  ('Alice Brown', 35);

SQLite, MySQL & MariaDB allows to insert arbitrary value into auto-increment column and update the sequence.

INSERT INTO example_table (id, name, age) VALUES (11, 'John Smith', 30);

INSERT INTO example_table (name, age)
VALUES
  ('Jane Doe', 25),
  ('Bob Johnson', 40),
  ('Alice Brown', 35);

SELECT * FROM example_table;

In above example we inserting 'John Smith' with id = 11 and next records inserts uses next sequence values 12, 13, 14

+====+=============+=====+
| id | name        | age |
+====+=============+=====+
| 11 | John Smith  | 30  |
| 12 | Jane Doe    | 25  |
| 13 | Bob Johnson | 40  |
| 14 | Alice Brown | 35  |
+----+-------------+-----+

MS SQL Server does not allow such voluntarism by default. So if we need to insert value in identity column we must to set IDENTITY_INSERT on our table:

SET IDENTITY_INSERT example_table ON
INSERT INTO example_table (id, name, age) VALUES (11, 'John Smith', 30)
SET IDENTITY_INSERT example_table OFF;

The above query inserts record with id = 11 and move up identity counter for next records.

What about Oracle? It allows to insert values in id column, without sequence be affected, so we can insert row with arbitrary id, but this can cause to collision like in next example:

CREATE SEQUENCE example_table_seq;

CREATE TABLE example_table (
   id NUMBER DEFAULT example_table_seq.nextval PRIMARY KEY,
   name VARCHAR2(50),
   age NUMBER
);
-- insert row with id = 3
INSERT INTO example_table (id, name, age) VALUES (3, 'John Smith', 30);

-- next to queries will add records with id 1 and 2
INSERT INTO example_table (name, age) VALUES ('Jane Doe', 25); -- 1
INSERT INTO example_table (name, age) VALUES ('Bob Johns', 40); -- 2

-- below query cause to error OCIStmtExecute: ORA-00001: unique constraint (0c7690dacb6b.SYS_C009760) violated 
INSERT INTO example_table (name, age) VALUES ('Alice Brown', 35); 

SELECT * FROM example_table;

PostgreSQL with SERIAL or SEQUENCE syntax for auto-increment column show us same behavior like Oracle (row inserted, sequence not updated, error on duplicate). But when we use IDENTITY syntax insert row with arbitrary id is not allowed. This restriction can be forced by OVERRIDING SYSTEM VALUE like in next example, but again it is not update the sequence and collision may cause.

CREATE TABLE example_table (
   id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   name VARCHAR(50),
   age INT
);

INSERT INTO example_table (id, name, age) OVERRIDING SYSTEM VALUE VALUES (3, 'John Smith', 30);

INSERT INTO example_table (name, age) VALUES ('Jane Doe', 25);
INSERT INTO example_table (name, age) VALUES ('Bob Johnson', 40);
INSERT INTO example_table (name, age) VALUES ('Alice Brown', 35);

SELECT * FROM example_table;

Lets move on! How to change an auto-increment value?

In SQLite you can reset the auto-increment value for a table by using the sqlite_sequence table.
The sqlite_sequence table is an internal table used by SQLite to keep track of the next auto-increment value for each table in a database. Each row in the sqlite_sequence table represents a table in the database, and the seq column stores the next auto-increment value for that table. So when we need to change it just try next:

UPDATE sqlite_sequence SET seq = 100 WHERE name = 'example_table';

INSERT INTO example_table (name, age) VALUES ('John Gold', 30);

The above code will move auto-increment value to 100 and next inserted row will get id = 101;

We can delete auto-increment with next query:

DELETE FROM sqlite_sequence WHERE name = 'example_table';

or set it less than maximal value in auto-incremented column but after next row will be inserted the sqlite_sequence will be fixed with right value. Just look next code snippet:

CREATE TABLE example_table (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   name VARCHAR(50),
   age INT
);

INSERT INTO example_table (name, age)
VALUES
  ('Jane Doe', 25),
  ('Bob Johnson', 40),
  ('Alice Brown', 35);

SELECT * FROM sqlite_sequence WHERE name = 'example_table';

after table create and filled we see

+===============+=====+
| name          | seq |
+===============+=====+
| example_table | 3   |
+---------------+-----+

We can drop it and make sure that value disappeared

DELETE FROM sqlite_sequence WHERE name = 'example_table';

SELECT * FROM sqlite_sequence WHERE name = 'example_table';

but after next record inserted value of sequence will be restored as well. Try it here

MySQL & MariaDB also allows to change auto-increment using ALTER TABLE query but it can not be less than maximal value in auto-incremented column:

ALTER TABLE example_table AUTO_INCREMENT = 100;

In Oracle, it is possible to alter a sequence using the ALTER SEQUENCE statement. This statement allows you to change the characteristics of a sequence, such as its minimum and maximum values, its increment, and its starting value. If you need to make more significant changes to a sequence, such as changing its data type or dropping it entirely, then you may need to drop and re-create the sequence.

DROP SEQUENCE example_table_seq;
CREATE SEQUENCE example_table_seq INCREMENT BY 1 START WITH 100;

Note that in Oracle, there is no conflict checking between the new sequence value and existing records. Therefore, it is important to exercise caution and use this feature responsibly to avoid unintended consequences.

In PostgreSQL, it doesn't matter how your auto-increment column is created (SERIAL, GENERATED ALWAYS AS IDENTITY, or using CREATE SEQUENCE) because you can use the ALTER SEQUENCE statement to modify the sequence. However, this DBMS does not guarantee the absence of conflicts after the sequence is modified, so it is important to exercise caution.

ALTER SEQUENCE example_table_id_seq RESTART WITH 100;

In conclusion:

Auto-increment is a feature in databases that generates a unique number for each new row added to a table, saving the user from manually managing the primary key values. This feature is supported by most RDBMS and can be easily implemented using different syntax in each database. SQLite, MySQL, and MariaDB all support auto-increment columns, as well as PostgreSQL and Oracle, which also allow for more advanced options like sequences and serial data types. Although each database has its own syntax for implementing and mange auto-increment. Overall, the auto-increment feature greatly simplifies the process of adding new rows and ensures that each row has a unique identifier, which is essential for many database operations.

If you want to practice creating tables with auto-increment columns and executing SQL statements, you can try using an online SQL editor like SQLize.online, which allows you to create tables, insert data, and run SQL queries on a live database.

Simple query to remove duplicates from table

Slava Rozhnev — Tue, 28 Mar 2023 17:13:09 +0000

In this small post I want to share the simple way to remove duplicates from table. The query works in MySQL, MariaDB and PostgreSQL databases. If you interested in such query for other RDBMS, please write me in comments.
Let's start. Assume we have simple table with two columns: id - is primary key and v simple integer value:

create table t (
    id int primary key,
    v int
);

insert into t(id, v) values 
(1, 1),(2, 1),(3, 2),(4, 2),(5, 1),(6, 1),(7, 2),(8, 3),(9, 2),(10, 4),(11, 3);

The code above create the table and insert couple of values. As you can see id have unique values, v have several duplicates:

+====+===+
| id | v |
+====+===+
| 1  | 1 |
| 2  | 1 |
| 3  | 2 |
| 4  | 2 |
| 5  | 1 |
| 6  | 1 |
| 7  | 2 |
| 8  | 3 |
| 9  | 2 |
| 10 | 4 |
| 11 | 3 |
+----+---+

Our mission is remove rows this duplicates in column v and rest unique values with minimal id value.
How we can find the duplicates? We can use simple LEFT JOIN on field v with additional condition for prevent joins rows itself:

select * 
from t
left join t t1 on t.v = t1.v and t.id > t1.id;

The query give us next result:

+====+===+========+========+
| id | v | id     | v      |
+====+===+========+========+
| 1  | 1 | (null) | (null) |
| 2  | 1 | 1      | 1      |
| 3  | 2 | (null) | (null) |
| 4  | 2 | 3      | 2      |
| 5  | 1 | 1      | 1      |
| 5  | 1 | 2      | 1      |
| 6  | 1 | 1      | 1      |
| 6  | 1 | 2      | 1      |
| 6  | 1 | 5      | 1      |
| 7  | 2 | 3      | 2      |
| 7  | 2 | 4      | 2      |
| 8  | 3 | (null) | (null) |
| 9  | 2 | 3      | 2      |
| 9  | 2 | 4      | 2      |
| 9  | 2 | 7      | 2      |
| 10 | 4 | (null) | (null) |
| 11 | 3 | 8      | 3      |
+----+---+--------+--------+

We can see the unique rows with minimal id have (null) values in last columns. So we need to remove rest. We can done this in next simple query:

delete t.*
from t
left join t t1 on t.v = t1.v and t.id > t1.id
where t1.id is not null;

Just run in on SQLize.online and see the result

P.S. After this publication my colleague @Akina suggested a shorter version:

delete t.*
from t
join t t1 on t.v = t1.v and t.id > t1.id;

DEV Community: Slava Rozhnev

Why `SUM() OVER (ORDER BY ...)` Sometimes Feels Wrong: A Practical Guide to SQL Window Frames

What a window frame is

Why this matters

Frame boundaries

ROWS, RANGE, and GROUPS: the real difference

ROWS

RANGE

GROUPS

Example 1: running total

Example 2: moving average

Example 3: looking forward

Example 4: full-partition aggregate without GROUP BY

The main trap: ROWS and RANGE can produce different running totals

When I use ROWS vs RANGE

Named windows

A practical reporting pattern: daily sales

Where this topic kept breaking for me

What is worth remembering

Final thought

Make Your Technical Tutorials Interactive with SQLize Embed

What is SQLize Embed?

Key Features

Getting Started in 30 Seconds

1. Include the Script

2. Add Your Sandbox

Advanced Configuration

Use Cases

Try the Live Demo

👉 SQLize Embed Showcase

Join the Community!

sql #database #webdev #tutorial #productivity #programming

Debunking the Myth: Is JOIN Always Faster Than Correlated Subqueries?

SQL Tricks: Generate Calendar Table

MySQL

PostgreSQL

MS SQL Server

Oracle

Conclusion

SQLtest.online - the site where you can test your SQL skills

Generate Date Series in popular Databases

MySQL

PostgreSQL

Microsoft SQL Server

Oracle

Exploring PostgreSQL's EXCLUDE Operator: Advanced Data Constraints

MySQL 8.0: Invisible Columns

The power of MERGE in PostgreSQL 15

The old MySQL bug (detective story)

Everything you wanted to know about Auto Increment but were afraid to ask

Simple query to remove duplicates from table

`ROWS`, `RANGE`, and `GROUPS`: the real difference

`ROWS`

`RANGE`

`GROUPS`

Example 4: full-partition aggregate without `GROUP BY`

The main trap: `ROWS` and `RANGE` can produce different running totals

When I use `ROWS` vs `RANGE`