Archive for the ‘MySQL’ Category
Posted by jpluimers on 2025/07/08
While writing Some SQLite things I recently learned a while ago I bumped into SQLite extending the SQL syntax allowing SELECT column aliases in WHERE and ON conditions of JOIN clauses whereas standard SQL only allows it in the HAVING clause (some other DMBS allow this as well).
It reminded I never wrote a blog post about WHERE versus HAVING, which can be phrased into just two points:
WHERE is applied before the SELECT is assembledHAVING is applied after the SELECT is assembled
This also means WHERE has a lot more influence on performance than HAVING, so using HAVING just so you can use aliases there instead of in the WHERE clauses is not a smart idea unless you verify in the query plan and query optimisation output of our DBMS this has no influence (some DBMS even allow you to see the results of query rewriting which you can use to verify if two queries really are equivalent from the DBMS perspective).
Since HAVING can be used without GROUP BY, many people work around the WHERE alias limitation. Given the above effects on performance, this is not a wise idea, despite the same end-results. See the example below that uses the [Wayback/Archive] MySQL :: Sakila Sample Database: customer table.
Luckily there has been enough other material explaining WHERE versus HAVING, so I can reference it from here, for instance (note note limited to MySQL/MariaDB) [Wayback/Archive] mysql – WHERE vs HAVING – Stack Overflow (thanks [Wayback/Archive] baloo and [Wayback/Archive] Fishdrowned):
Read the rest of this entry »
Posted in Conference Topics, Conferences, Database Development, Development, Event, MariaDB, MySQL, SQL, SQLite | Tagged: 1054, this | Leave a Comment »
Posted by jpluimers on 2025/07/03
Almost 3 years ago, I wrote about some database/SQL fiddle sites in Database fiddle sites (which covers [Wayback/Archive] two different [Wayback/Archive] dbfiddle sites and the [Wayback/Archive] SQL Fiddle site).
In the meantime, I figured out that [Wayback/Archive] Toolbox for Developers has a few fiddle pages and database/SQL tools online (in their order, which is not alphabetical):
Read the rest of this entry »
Posted in Database Development, Development, JavaScript/ECMAScript, MariaDB, MongoDB, MySQL, NoSQL, PHP, PostgreSQL, Scripting, Software Development, SQL Server, SQLite, TypeScript | Leave a Comment »
Posted by jpluimers on 2025/07/02
Since I keep forgetting which DBMS uses which method to select just a plain value without a table. I always remember it as my search phrase [Wayback/Archive] SELECT from DUAL, but actually better titled like the question below:
[Wayback/Archive] sql – SELECT from nothing? – Stack Overflow
Posted in Access, Database Development, DB2, Development, Firebird, InterBase, MariaDB, MySQL, OracleDB, PL/SQL, PostgreSQL, SQL, SQL Server, SQLite | Leave a Comment »
Posted by jpluimers on 2025/01/23
I love how Kristian Köhntopp often turns series of valuable tweets in a blog post. [Wayback/Archive] MySQL: Boiling JFrogs | Die wunderbare Welt von Isotopp is no different and has much more than the few quotes below (especially about the process of finding the solutions):
Read the rest of this entry »
Posted in Database Development, Development, MySQL, Profiling, Profiling-Performance-Measurement, Software Development | Leave a Comment »
Posted by jpluimers on 2025/01/07
For my link archive: [Wayback/Archive] MySQL: Row Literals | Die wunderbare Welt von Isotopp
Question on the Libera/#mysql IRC channel:
Is there a way to split a simple select into multiple returned rows? For example, select 1, 2, 3 to be returned as rows?
This is actually asking for a table literal notation. I know of four ways to construct a table literal in MySQL:
They are based on UNION ALL, JSON_TABLE and VALUES statement (the latter in two forms). I knew about the first (which I used in other database environments), not about the others.
Read the rest of this entry »
Posted in Database Development, Development, MySQL, SQL | Leave a Comment »
Posted by jpluimers on 2024/08/07
At the time of writing a lot of this might be more recent, but for quite some time codepoints.net had not been updated with code point information newer Unicode releases.
Basically it was stuck at Unicode version 8.0 with some 120k glyphs. At the time of writing Unicode version 15.0 is in beta and the difference between 15.0 and 8.0 is some 24k glyphs.
So I had a quick twitter chat with the author and jotted down the links in this blog post so I won’t forget them.
There I learned it was open source (I think it is the only Unicode codepoint site that is).
Here it goes:
Read the rest of this entry »
Posted in *nix, *nix-tools, Apache2, codepoints.net, Conference Topics, Conferences, Database Development, Debian, Development, DVCS - Distributed Version Control, Encoding, Event, GitHub, Linux, MySQL, PHP, Power User, Scripting, Software Development, Source Code Management, Unicode, Web Development | Leave a Comment »
Posted by jpluimers on 2024/07/18
I love how Kris answers with these concise bits of SQL query results, this time about the sleep function and expression reuse of function results:
Read the rest of this entry »
Posted in Database Development, Development, MySQL, SQL Server | Leave a Comment »
Posted by jpluimers on 2023/11/07
From quite a while ago, and still interesting:
Why are these interesting?
For me it is because tiny hick-ups can be just as hard for senior people as for novices.
–jeroen
Posted in Database Development, Development, MySQL | Leave a Comment »
Posted by jpluimers on 2022/10/27
I knew there was JSFiddle for live playing around with JavaScript and more in your browser, so I wondered if there was a similar site for databases and SQL queries.
There are, so here are a few database fiddle sites: SQL playgrounds where you can live play with SQL queries (sometimes even without an underlying example database).
All via [Wayback/Archive.is] database fiddle – Google Search:
Read the rest of this entry »
Posted in Conference Topics, Conferences, Database Development, DB2, Development, Event, Firebird, JavaScript/ECMAScript, JSFiddle, MariaDB, MySQL, OracleDB, PL/SQL, PostgreSQL, Scripting, Software Development, SQL, SQL Server, SQLite, T-SQL | Leave a Comment »
The Wiert Corner - irregular stream of stuff 