MySQL Queries ·

MySQL Query Notes: Joins, Updates, Ranks and Columns

By Manu Mathew, Technical Lead Engineer · Published 4 October 2026 · Last updated 5 October 2026

Isometric illustration of database tables joined together, with one highlighted row lifted out in blue

Most day-to-day SQL work comes down to a handful of patterns: count things per group, update several rows at once, copy data between tables, sort in an order the business cares about, and number the results. This page brings together the MySQL query examples from several short notes I wrote between 2013 and 2014. I have rewritten them in one place, with tested examples and current advice, so you don’t have to piece the answer together from fragments.

Quick answer

Count per group with JOIN and GROUP BY. Update different rows with different values using CASE, and two tables at once with a multi-table UPDATE … JOIN. Copy rows with INSERT … SELECT, sort by a custom list with FIELD(), and number rows with ROW_NUMBER() on MySQL 8.0 or MariaDB 10.2 and later.

Every query below was run on MariaDB 11.8 against the small example tables shown in the first section. The same syntax works on MySQL 8.0 unless I say otherwise.

How do I count rows per group across two tables?

Join the tables, filter the rows you want, then GROUP BY the column you are counting by.

Here, room_types lists the kinds of room and bookings holds one row per booking. The query counts active bookings for each active room type:

SELECT r.id, r.name, COUNT(*) AS bookings
FROM bookings b
JOIN room_types r ON r.id = b.room_type_id
WHERE r.active = 1 AND b.active = 1
GROUP BY r.id, r.name;
+----+--------+----------+
| id | name   | bookings |
+----+--------+----------+
|  1 | Single |        2 |
|  2 | Double |        2 |
+----+--------+----------+

Put every non-aggregated column from the SELECT list in the GROUP BY as well. MySQL 5.7 and later reject the query otherwise (the ONLY_FULL_GROUP_BY mode), and the rule keeps the results predictable.

How do I update different rows with different values in one query?

Use a CASE expression in the SET clause, and limit the rows with WHERE.

Isometric illustration of one query updating several table rows, each row receiving its own new value
UPDATE bookings
SET status = CASE id
               WHEN 1 THEN 'confirmed'
               WHEN 3 THEN 'cancelled'
               ELSE status
             END
WHERE id IN (1, 3);

The ELSE status branch matters: without it, any row the WHERE lets through but no WHEN matches would be set to NULL.

How do I update two tables in one query?

MySQL and MariaDB allow a multi-table UPDATE: join the tables, then set columns from both in one SET list, separated by commas.

UPDATE tasks t
JOIN task_log l ON l.task_id = t.task_id
SET t.state = 'done',
    l.changed_at = UNIX_TIMESTAMP()
WHERE t.task_id = 1024;

This marks task 1024 as done and stamps its log row with the current Unix time, in one statement. Test the WHERE clause with a SELECT first, since a multi-table update can touch more rows than you expect.

How do I copy rows from one table to another?

Use INSERT INTO … SELECT for an existing table, or CREATE TABLE … AS SELECT to create the table from the query.

-- Copy chosen columns into an existing table
CREATE TABLE products_archive (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO products_archive (id, name)
SELECT id, name FROM products WHERE category = 'kitchen';

-- Or create a new table from a query in one step
CREATE TABLE home_products AS
SELECT id, name FROM products WHERE category = 'home';

The SELECT … INTO new_table form you may have seen elsewhere is SQL Server syntax. In MySQL, SELECT … INTO writes to variables or a file, not a new table.

How do I match several values without chaining OR?

Use IN (…). It means the same as a chain of OR conditions on one column, and is easier to read and to build from a list.

SELECT id, name
FROM products
WHERE category IN ('kitchen', 'office');

An older version of this note said IN is faster than OR. The optimiser treats simple cases alike, so pick IN for clarity and check real speed with EXPLAIN and an index on the column.

How do I sort results in a custom order?

ORDER BY FIELD(column, 'a', 'b', 'c') sorts rows in the order you list the values.

SELECT id, name, category
FROM products
ORDER BY FIELD(category, 'office', 'kitchen', 'home'), name;
+----+---------+----------+
| id | name    | category |
+----+---------+----------+
|  5 | Pen     | office   |
|  1 | Kettle  | kitchen  |
|  3 | Toaster | kitchen  |
|  2 | Lamp    | home     |
|  4 | Rug     | home     |
+----+---------+----------+

Values that are not in the list get position 0, so they come first. Add DESC after FIELD(…) if you would rather have them last, and list the values in reverse.

How do I get the row number or rank of each row?

Use the window functions ROW_NUMBER() and RANK(), available in MySQL 8.0 and MariaDB 10.2 and later.

SELECT name,
       stock_qty,
       ROW_NUMBER() OVER (ORDER BY stock_qty DESC, name) AS row_num,
       RANK()       OVER (ORDER BY stock_qty DESC)       AS stock_rank
FROM products;
+---------+-----------+---------+------------+
| name    | stock_qty | row_num | stock_rank |
+---------+-----------+---------+------------+
| Kettle  |        30 |       1 |          1 |
| Toaster |        30 |       2 |          1 |
| Lamp    |        20 |       3 |          3 |
| Rug     |        10 |       4 |          4 |
| Pen     |         5 |       5 |          5 |
+---------+-----------+---------+------------+

ROW_NUMBER() always counts 1, 2, 3; RANK() gives tied rows the same rank and then skips (1, 1, 3). The old trick with a user variable (@x := @x + 1) is deprecated in MySQL 8.0, so avoid it in new code.

How do I list the columns of a table?

SHOW COLUMNS FROM table gives the quick view; information_schema.COLUMNS gives the same data as a normal query you can filter.

SHOW COLUMNS FROM products;

SELECT COLUMN_NAME, DATA_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products'
ORDER BY ORDINAL_POSITION;

From Perl, DBI can return the names straight away:

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect('dbi:MariaDB:database=shop', $ENV{DB_USER}, $ENV{DB_PASS},
                       { RaiseError => 1 });

# Column names from the table definition
my $columns = $dbh->selectcol_arrayref('SHOW COLUMNS FROM products');
print "@$columns\n";                       # id name category stock_qty

# Or the column names of any query, without fetching rows
my $sth = $dbh->prepare('SELECT id, name FROM products WHERE 1 = 0');
$sth->execute;
print "@{ $sth->{NAME} }\n";               # id name

The WHERE 1 = 0 query returns no rows but still describes its columns, which is handy when you build a CSV header. If you are on DBD::mysql rather than DBD::MariaDB, change the DSN to dbi:mysql:….

Frequently asked questions

Do these queries work on both MySQL and MariaDB?

Yes. Every query here runs on current MariaDB and on MySQL 8.0 or later. The main version limit is the window functions ROW_NUMBER() and RANK(), which need MySQL 8.0 or MariaDB 10.2. Multi-table UPDATE and FIELD() are MySQL extensions, so they won’t carry over to PostgreSQL unchanged.

How can I check an UPDATE before it changes live data?

Run a SELECT with the same JOIN and WHERE clause first and check how many rows it returns. On InnoDB tables you can also run the UPDATE inside START TRANSACTION, look at the result, then COMMIT or ROLLBACK. Take a backup before large changes.

What is the difference between RANK() and DENSE_RANK()?

Both give tied rows the same number. RANK() then skips numbers, so two rows tied for first are followed by third (1, 1, 3). DENSE_RANK() carries on without a gap (1, 1, 2). Use ROW_NUMBER() when every row needs its own number, even when values tie.

Why does my GROUP BY query fail with an ONLY_FULL_GROUP_BY error?

The SELECT list includes a column that is neither in GROUP BY nor inside an aggregate such as COUNT() or MAX(), so MySQL can’t tell which row’s value to show. Add the column to GROUP BY, wrap it in an aggregate, or use ANY_VALUE() if any value will do.

Related notes: MySQL Unix timestamps and time zones; Perl files, folders, CSV and downloads; Perl DBI transactions.

Need help with Perl code?

If you have Perl code that needs fixing, extending or upgrading, I’m happy to take a look. I work remotely from Kozhikode, Kerala, with teams across India and elsewhere; Perl development and maintenance explains how I help.

You can reach me through the contact section or email me at manu@manu.co.in. A few lines about the system and what you need is a good start.

Sources: MySQL 8.0: UPDATE; MySQL 8.0: INSERT … SELECT; MySQL 8.0: FIELD(); MySQL 8.0: window functions; MySQL 8.0: GROUP BY handling; MariaDB: ROW_NUMBER.

Back to the blog