ANTI JOIN is a very useful operator from relational algebra. Regrettably, only few dialects support it in terms of SQL syntax, as we’ve written earlier. In jOOQ, you can write it as follows: If your RDBMS supports this natively (e.g. ClickHouse, Databricks), then it is rendered as such. Otherwise, jOOQ will translate this to: But … Continue reading Simplifying ANTI…
#sql
267 posts
7 May
4 May
Some SQL operators are as esoteric as they’re powerful. One of the oldest operator that you’ve likely hardly ever used in real world applications is NATURAL JOIN which is the default in relational algebra. We’ve covered a funky use-case for NATURAL JOIN earlier on this blog. The main reason why it’s not very useful is … Continue reading Why JOIN…
24 Mar
Expedia Group Technology — Data Workload‑aware routing for Trino Photo by Joseph Barrientos on Unsplash Trino — a fork of PrestoSQL — is a powerful tool in modern data analytics, enabling organizations to query large datasets quickly and efficiently. As a distributed SQL query engine, Trino provides fast, scalable insights without requiring data relocation. While Trino is robust on its…
27 Mar 2025
ARRAY types are a part of the ISO/IEC 9075 SQL standard. The standard specifies how to: But it is very unopinionated when it comes to function support. The ISO/IEC 9075-2:2023(E) 6.47 <array value expression> specifies concatenation of arrays, whereas the 6.48 <array value function> section lists a not extremely useful TRIM_ARRAY function, exclusively (using which … Continue reading When SQL…
13 Mar 2025
RIGHT JOIN is an esoteric feature in the SQL language, and hardly ever seen in the real world, because almost every RIGHT JOIN can just be expressed as an equivalent LEFT JOIN. The following two statements are equivalent: It’s not unreasonable to expect these two statements to produce the same execution plan on most RDBMS, … Continue reading Think About…
14 Jan 2025
Introduction In this article, I’m going to explain you should use compact table columns when designing your database schema. By using compact table columns, you can cache more table records and index entries and, therefore, speed up your SQL queries. Database caching As I explained in this article, relational database systems cache the pages that are loaded from the disk…
3 Jun 2024
A cool standard SQL:2003 feature is the aggregate FILTER clause, which is supported natively by at least these RDBMS: The following aggregate function computes the number of rows per group which satifsy the FILTER clause: This is useful for pivot style queries, where multiple aggregate values are computed in one go. For most basic types … Continue reading Emulating SQL…
22 May 2024
Introduction In this article, we are going to see how we can use the PostgreSQL COPY command to export a large result set to an external file. ETL (Extract, Transform, Load) When implementing an ETL (Extract, Transform, Load) process, you might have to extract a large result set from a relational database in order to transform it according to some…
18 Apr 2024
Introduction In this article, we are going to investigate how to calculate percentiles with the SQL PERCENTILE_CONT function. Domain Model Let’s consider we have the following quotes table that holds the historical price values of various stocks and indexes: The quotes table is populated with data that looks like this: SQL PERCENTILE_CONT The SQL Standard provides support for a great…
10 Apr 2024
Introduction In this article, we are going to investigate the difference between the PostgreSQL FOR UPDATE and FOR NO KEY UPDATE when locking a parent record and inserting a child row. Domain Model To see the difference between the PostgreSQL FOR UPDATE and FOR NO KEY UPDATE locking clauses, consider the following one-to-many table relationship where the post table is…
26 Mar 2024
Introduction In this article, we are going to analyze how PostgreSQL Heap-Only-Tuple or HOT Update optimization works, and why you should avoid indexing columns that change very frequently. PostgreSQL Tables and Indexes Unlike SQL Server or MySQL, which store table records in a Clustered Index, in Oracle and PostgreSQL, records are stored in Heap Tables that have unique row identifiers.…
11 Mar 2024
Introduction In this article, we are going to analyze the PostgreSQL Index Types so that we can understand when to choose one index type over the other. When using a relational database system, indexing is a very important topic because it can help you speed up your SQL queries by reducing the number of pages that have to be scanned…
1 Mar 2024
I’ve blogged about generic ways of getting top 1 or top n per category queries before on this blog. An Oracle specific version in that post used the arcane KEEP syntax: This is a bit difficult to read when you see it for the first time. Think of it as a complicated way to say … Continue reading Getting Top…
16 Feb 2024
In a previous blog post, we’ve advertised the use of SQL EXISTS rather than COUNT(*) to check for existence of a value in SQL. I.e. to check if in the Sakila database, actors called WAHLBERG have played in any films, instead of: Do this: (Depending on your dialect you may require a FROM DUAL clause, … Continue reading An Efficient…
18 Jan 2024
Strava challenges offer a fun way for athletes to compete against themselves and others! Back in 2020, our legacy challenge leaderboard system was running into bottlenecks and scalability problems on a regular basis, and we often found ourselves putting out fires to keep the system stable. In late 2020 and early 2021, I worked on a project to replace the…
20 Dec 2023
In MySQL, you cannot do this: The UPDATE statement will raise an error as follows: SQL Error [1093] [HY000]: You can’t specify target table ‘t’ for update in FROM clause People have considered this to be a bug in MySQL for ages, as most other RDBMS can do this without any issues, including MySQL clones: … Continue reading Workaround for…
13 Dec 2023
Do you need to add a JDBC driver to your application, and don’t know its Maven coordinates? This blog post lists the most popular drivers from the jOOQ integration tests. Look up the latest versions directly on https://central.sonatype.com/ with parameters g:groupId a:artifactId, for example, the H2 database and driver: https://central.sonatype.com/search?q=g%3Acom.h2database+a%3Ah2 The list only includes drivers … Continue reading Maven Coordinates…
1 Dec 2023
Need to connect to your RDBMS with JDBC and don’t have the JDBC connection URL or driver name at hand? No problem, just look up your RDBMS below:
22 Nov 2023
Introduction In this article, we are going to see how Index Selectivity works in relational database systems and why the database Optimizer might choose to avoid using an index if the number of matching records is large. Index selectivity is inversely proportional to the number of index entries matched by a given value. So, a unique index has the highest…
20 Sept 2023
Introduction In this article, we are going to see how we can find the source of an SQL query generated by Hibernate. Knowing where a given SQL query originates from is very useful when trying to investigate performance issues caused by either long-running queries or queries that are executed excessively (e.g., N+1 query issues). Hypersistence Utils This feature that we…
31 Aug 2023
Introduction In this article, we’re going to see how the PostgreSQL JDBC Driver implements Statement Caching and what settings we need to configure in order to optimize the performance of our data access layer. Prepared Statements The JDBC API allows you to create a PreparedStatement by calling prepareStatement(java.lang.String) method on a given Connection reference. For this reason, it’s very common…
14 Jun 2023
Introduction In this article, we are going to see what is the best way to use one-to-one table relationships. I decided to write this article after reading this Tweet: One-to-one table relationships As I explained in this article, a relational database system defines three table relationship types: one-to-many one-to-one many-to-many The one-to-one table relationships rely on the fact that both…
17 May 2023
Introduction In this article, we are going to see what is the standard SQL operation order. Once you understand the order in which SQL operations are executed, then it will be clear why the Oracle legacy pagination query required a Derived Table in order to make sure that the ROWNUM pseudocolumn is computed after executing the ORDER BY clause. SQL…
13 Apr 2023
Introduction In this article, we are going to see how the PostgreSQL Auto Explain feature works and why you should use it to gather the actual execution plan for SQL statements that execute on a production system. SQL Execution Plan As I explained in this article, when you send a SQL statement to PostgreSQL, the statement is executed as illustrated…
24 Mar 2023
Occasionally, you want to write a SQL query and fetch a hierarchy of data, whose flat representation may look like this: The result might be: |id |parent_id|label | |---|---------|-------------------| |1 | |C: | |2 |1 |eclipse | |3 |2 |configuration | |4 |2 |dropins | |5 |2 |features | |7 |2 |plugins | |8 |2 … Continue reading How to…
10 Mar 2023
Introduction In this article, we are going to see what is the best way to use the amazing SQL NOWAIT feature that allows us to avoid blocking when acquiring a row-level lock. Since all the top major database support this functionality, Hibernate offers a NOWAIT option that allows you to render the proper database-specific syntax associated with this feature without…
24 Feb 2023
One of the more frequent questions about jOOQ is how to write a derived table (or a CTE). The jOOQ manual shows a simple example of a derived table: In SQL: In jOOQ: And that’s pretty much it. The question usually arises from the fact that there’s a surprising lack of type safety when working … Continue reading How to…
14 Feb 2023
By: Kyler Stole Are you a car shopper but don’t know which car fits your needs? TrueCar’s Model Search can help narrow it down. It consists of a search results page (SRP) of vehicle models along with a slew of combinable filters. But what powers such a thing?? Well, Elasticsearch, because relational databases struggle with the aggregations to display counts…
7 Feb 2023
By: Kyler Stole If you ever struggle to express a query in ActiveRecord, you may be able to build it with Arel instead. Arel is the platform that manages the abstract syntax tree (AST) used to build SQL queries in Rails. It’s the implementation underneath the veneer of ActiveRecord (AR) and it’s considerably more flexible than just the functionality that…
6 Feb 2023
I’ve found an interesting question on Twitter, recently. Is there any performance impact of using FILTER in SQL (PostgreSQL, specifically), or is it just syntax sugar for a CASE expression in an aggregate function? As a quick reminder, FILTER is an awesome standard SQL extension to filter out values before aggregating them in SQL. This … Continue reading The Performance…
20 Jan 2023
One of MySQL 8’s biggest improvements is the support of window functions. As I always said in conferences, there’s SQL before window functions and SQL after window functions. Once you start using them, you’ll use them everywhere. Some of you poor souls are unfortunate enough to be stuck on MySQL 5.7, either of your own … Continue reading Emulating Window…
11 Jan 2023
Introduction In this article, we are going to see the overhead of acquiring a new connection when using YugabyteDB and why connection pooling is mandatory for performance. Acquiring a database connection using JDBC To interact with a database system, first, we need to acquire a database connection. And, when using Java, we need to obtain a Connection object from the…
4 Nov 2022
The standard SQL WITH clause has been tremendously helpful in structuring SQL queries. Instead of nesting everything in unreadable derived tables like this: People have started moving the logic up front, just like in any other programming language, where we declare things first, lexically, then use them: Both queries will produce the 5 actors with … Continue reading LATERAL is…
1 Sept 2022
No need for expensive Tableau subscriptions. Ditch Microsoft Excel. Just use native PostgreSQL to quickly visualise your data! Here’s an idea I had for a while. As you may know, jOOQ can produce fancy charts from your jOOQ results. But that requires you use jOOQ, and you may not be using jOOQ, because you’re not … Continue reading How to…
23 Aug 2022
Probably the hardest thing to standardise in SQL is RETURNING data from DML statements. In this article, we’ll look at various ways of doing that with jOOQ, in many of jOOQ’s supported dialects, and with JDBC directly. How to do it with jOOQ Assuming the usual table from the sakila database: jOOQ took syntactic inspiration … Continue reading The Many…
10 Jun 2022
I stumbled upon a very interesting question on Stack Overflow about how to use jOOQ’s MULTISET operator to nest a collection, and then filter the result by whether that nested collection contains a value. The question is jOOQ specific, but imagine, you have a query that nests collections using JSON in PostgreSQL. Assuming, as always, … Continue reading How to…
31 May 2022
Every now and then, I see folks lament the SQL syntax’s peculiar disconnect between the lexical order of operations (SELECT .. FROM) the logical order of operations (FROM .. SELECT) Most recently here in a Youtube comment reply to a recent jOOQ/kotlin talk. Let’s look at why jOOQ didn’t fall into this trap of trying … Continue reading Changing SELECT…
11 May 2022
An interesting hint by Vladimir Sitnikov has made me think about a new benchmark for jOOQ: The benchmark should check whether single row queries should have a JDBC Statement.setFetchSize(1) call made to them by default. The Javadoc of the method says: Gives the JDBC driver a hint as to the number of rows that should … Continue reading Setting the…
1 Mar 2022
A lot of RDBMS support standard SQL sequences of some form. The standard SQL syntax to create a sequence is: The following is how you could fetch a value from this sequence, using jOOQ, assuming you’re using the code generator: The sequence expression translates to a variety of dialects: You can also embed the S.nextval() … Continue reading How to…
24 Feb 2022
For SQL beginners, there’s a bit of an esoteric syntax named PARTITION BY, which appears all over the place in SQL. It always has a similar meaning, though in quite different contexts. The meaning is similar to that of GROUP BY, namely to group/partition data sets by some grouping/partitioning criteria. For example, when querying the … Continue reading Various Meanings…
22 Feb 2022
Questions that might be a bit more difficult to solve using ordinary SQL are questions of the kind: What films have the same actors as a given film X? As always, we’re using the sakila database for this example. What would be a possible way to solve this with SQL (for example, PostgreSQL, to be … Continue reading Use MULTISET…
21 Feb 2022
A long standing feature request has seen little love from the jOOQ community, despite a lot of people probably wanting it. It goes by the unimpressive title Let Table<R> extend SelectField<R>: https://github.com/jOOQ/jOOQ/issues/4727 What does the feature mean, specifically? The awesome PostgreSQL Let’s have a look at a really cool PostgreSQL feature. In PostgreSQL, it is … Continue reading Projecting Type…
8 Feb 2022
I’ve recently stumbled upon this interesting Stack Overflow question about Hibernate’s popular MultipleBagFetchException. The question is super popular, and the answers are plenty. The various limitations are discussed throughout the question, it all boils down to a simple fact: Joins are the wrong tool to nest collections. Given a schema like the Sakila database: There … Continue reading No More…
4 Feb 2022
If you’re running on PostgreSQL, you could try the following cool query: What does it print (after a while)? It prints e (almost). Here are some sample results: 2.7169115477960698 2.7164145522690296 2.7172065451410937 2.7170815462660836 Not perfect, sure, here’s a better approximation written in SQL: Producing: 2.718281828459045 Close enough… How does it work? It’s a cool approximation that … Continue reading Approximating e…
14 Jan 2022
I recently stumbled upon a standard SQL feature that was implemented, to my surprise, in HSQLDB. The keyword is CORRESPONDING, and it can be used with all set operations, including UNION, INTERSECT, and EXCEPT. Let’s look at the sakila database. It has 3 tables with people in it: Similar, but not the same. What if … Continue reading A Rarely…
15 Nov 2021
The upcoming jOOQ 3.16 will finally offer support for the various RDBMS GIS extensions via issue #982. This is great news per se, and will be covered in a future blog post, when the integration is ready. This post here is about something else. Adding support for such a feature is a great source of … Continue reading Fun with…
5 Nov 2021
I’ve recently discovered a pleasant new addition to PostgreSQL 14, the new enable_memoize flag that improves the performance of some nested loop joins where statistics hint at this being appropriate. I mean, who can resist this temptation: Improving query speed by 1000x hints at something very suboptimal having been going on before, and a tool … Continue reading PostgreSQL 14’s…
29 Oct 2021
The SQL standard knows an interesting feature where you can project any functional dependencies of a primary (or unique) key that is listed in the GROUP BY clause without having to add that functional dependency to the GROUP BY clause explicitly. What does this mean? Consider this simple schema: In order to count the number … Continue reading Functional Dependencies…
19 Oct 2021
jOOQ’s parser can’t parse every possible SQL syntax. Try this random PostgreSQL syntax: And the jOOQ parser will complain: DOMAIN, INDEX, SCHEMA, SEQUENCE, SESSION, TABLE, TYPE, or VIEW expected: [1:7] ALTER [*]SYSTEM RESET ALL That’s perfectly fine. The goal of the jOOQ parser isn’t to understand all vendor specific syntax. The goal is to offer … Continue reading The jOOQ…
19 Aug 2021
A very little known feature in jOOQ is the Formattable.formatChart() capability, which allows for formatting any jOOQ result as an ASCII chart. This can be useful for quick plotting of results in your console application. Assuming you have a result set of this form (which is what you’re getting when you call result.format() or just … Continue reading Formatting ASCII…
27 Jul 2021
It’s been almost 1 year now since jOOQ 3.14 was released in October 19, 2020 with SQL/JSON (and SQL/XML) support. Half a year later, we’ve released jOOQ 3.15 with MULTISET support, which builds on top of these features to offer type-safe nested collections, the way every ORDBMS should implement them. Building (dogfooding) on top of … Continue reading Standard SQL/JSON…
12 May 2021
Do you need to know what RDBMS Server version you’re on, and you only have SQL at your disposal? No problem. Most RDBMS provide you with that information in some form of meta data table. Here’s how: Missing this info for your own RDBMS? Feel free to comment.
22 Apr 2021
A problem few developers are aware of is the possibility of running into “cursor cache contention” or “execution plan cache contention” problems when using IN lists in SQL. The problem that is described in lengths in previous articles, can be summarised as this. All of these are distinct SQL queries and need to be parsed … Continue reading Use IN…
11 Mar 2021
When paginating results in SQL, we use standard SQL OFFSET .. FETCH or a vendor specific version of it, such as LIMIT .. OFFSET. For example: As always, we’re using the Sakila database for this example. This is rather straightforward. It will give us page 2 out of N pages, with a page size of … Continue reading Calculating Pagination…
15 Feb 2021
I’ve run across a fun little trick to simulate latency in your development environments when testing some SQL queries. Possible use-cases including to validate that backend latency won’t bring down your frontend, or that your UX is still bearable, etc. The solution is PostgreSQL and Hibernate specific, though to doesn’t have to be. Besides, it … Continue reading Simulating Latency…
10 Feb 2021
In the past years, we’ve invested a lot of effort into improving our procedural language capabilities in jOOQ. What started with a simple internal API to support the emulations of DDL clauses like these: … evolved into a full fledged API for all sorts of procedural logic executed in your database server. Anonymous blocks The … Continue reading Translating Stored…
8 Feb 2021
So, @rotnroll666 nerd sniped me again. Apparently, the Neo4j Cypher query language supports arbitrary reductions, just like any functional collection API, oh say, the JDK Stream API: SQL doesn’t have this, yet it would be very useful to be able to occasionally do that. An arbitrary reduction can be implemented “easily” in SQL. Let’s look … Continue reading Implementing a…
17 Nov 2020
While jOOQ is mostly being used as an internal SQL DSL for embedded, dynamic SQL in Java, where it offers the best solution on the market, jOOQ is increasingly also used for one of its secondary features: Its parser. Having been introduced in jOOQ 3.9 primarly for the purpose of being able to parse DDL … Continue reading Automatically Transform…
9 Oct 2020
One of the main features of ORMs is M as in Mapping. Libraries like jOOQ help auto-mapping flat or nested database records onto Java classes that have the same structure as the SQL result set. The following has always been possible in jOOQ, assuming PostgreSQL’s INFORMATION_SCHEMA (using the generated code from the jOOQ-meta module): The … Continue reading Nesting Collections…
10 Sept 2020
I was asked a very interesting question on Twitter just now: @lukaseder quick q: in pg can I have a composite foreign key where one value is a constant… or do I have to store the constant in the table? constraint foreign key (foo_id, ‘bar_subtype’) references foo(foo_id,foo_type) ? — Look! The Emperor is NAKED V⃝ … Continue reading Having “constant”…
5 Aug 2020
There are a few ways to compare two similar tables in SQL. Assuming PostgreSQL syntax, we might have this schema: It is now possible to use UNION and EXCEPT as suggested by Chris Saxon: In PostgreSQL, we can write: Notice how TABLE x is just standard SQL, and PostgreSQL, syntax sugar for SELECT * FROM … Continue reading Use NATURAL…
5 May 2020
SQL Server supports transforming flat tabular SQL result sets into hierarchical structures by convention using the convenient FOR XML or FOR JSON syntaxes. This is really convenient and less verbose than the standard SQL/XML or SQL/JSON APIs – although the standard ones are more powerful. In this blog post, I’d like to show a few … Continue reading Using SQL…
10 Apr 2020
The SQL MERGE statement is a device whose mystery is only exceeded by its power. A simple example shows its full power according to standard SQL. Imagine you have a production table for product prices, and a staging table from which you want to load the latest prices. For once, I’m using the Db2 LuW … Continue reading The Many…
3 Mar 2020
It appears that our recent beginner SQL articles explaining SQL syntax were quite popular. These include: A Beginner’s Guide to the True Order of SQL Operations A Probably Incomplete, Comprehensive Guide to the Many Different Ways to JOIN Tables in SQL 10 Easy Steps to a Complete Understanding of SQL How SQL DISTINCT and ORDER … Continue reading 5 Ways…
2 Mar 2020
A very common misconception I often encounter with SQL users is the idea that DISTINCT is something like a function, and that it can take parenthesised arguments. Just recently, I’ve seen this Stack Overflow question where the OP was looking for a way to express this in jOOQ: Notice the parentheses around (emp.id), which look … Continue reading SQL DISTINCT…
13 Nov 2019
It’s been a while since I’ve ranted on this blog, but I was recently challenged by a reddit thread to write about this topic, so here goes… So, you’re writing a service that produces some JSON from your database model. What do you need? Let’s see: STOP IT No, seriously. Just stop it right there! … Continue reading Stop Mapping…
29 Oct 2019
One of Java’s big strengths, in my opinion, is the fact that most naming conventions have been established by the creators of the language. For example: If someone does not adhere to these conventions, the resulting code quickly looks non-idiomatic. What about SQL? Many do not agree on the “correct” case: There seems to be … Continue reading A Guide…
25 Oct 2019
Dogfooding, or eating your own dog food, is a practice that all product developers should implement all the time. According to wikipedia: Dogfooding, occurs when an organization uses its own product. This can be a way for an organization to test its products in real-world usage. Hence dogfooding can act as quality control, and eventually … Continue reading Dogfooding in…
27 Sept 2019
MySQL 8 does not yet support the BOOLEAN type as specified in the SQL standard. There is a DDL “type” called BOOL, which is just an alias for TINYINT: The above produces: TABLE_NAME|COLUMN_NAME|DATA_TYPE|COLUMN_TYPE| ----------|-----------|---------|-----------| t |b |tinyint |tinyint(1) | Notice that BOOL translates to a specific “type” of TINYINT, a TINYINT(1), where we might be … Continue reading How to…
19 Sept 2019
One of the biggest and undead myths in SQL is that COUNT(*) is faster than COUNT(1). Or was it that COUNT(1) is faster than COUNT(*)? Impossible to remember, because there’s really no reason at all why one should be faster than the other. But is the myth justified? Let’s measure! How does COUNT(…) work? But … Continue reading What’s Faster?…
11 Sept 2019
Using the right data type for some calculation sounds like some obvious advice. There are many blogs about using temporal data types for temporal data, instead of strings. An obvious reason is data integrity and correctness. We don’t gain much in storing dates as 2019-09-10 in one record, and as Nov 10, 2019 in the … Continue reading Oracle’s BINARY_DOUBLE…
9 Sept 2019
A nice little gem in PostgreSQL’s SQL syntax is the DISTINCT ON clause, which is as powerful as it is esoteric. In a previous post, we’ve blogged about some caveats to think of when DISTINCT and ORDER BY are used together. The bigger picture can be seen in our article about the logical order of … Continue reading Using DISTINCT…
5 Sept 2019
Quantified comparison predicates One of SQL’s weirdes features are quantified comparison predicates. I’ve hardly ever seen these in the wild: The above example is equivalent to using the much more readable IN predicate: This equivalence is defined in the SQL standard. There are more esoteric cases that could be solved using such quantified comparison predicates … Continue reading Quantified LIKE…
16 Jul 2019
Oracle 12c has introduced the useful SQL standard IDENTITY feature, which is essentially just syntax sugar for binding a sequence to a column default. We can use it like this: Which produces COL1 ---- 1 2 3 COL2 ---- 1 For unit testing against our database, we might want to know what “state” our identities … Continue reading How to…
24 Apr 2019
I found a very interesting SQL question on Twitter recently: Hi @sfonplsql we have some scenario, Let us 01Jan Mkt Value 100, 02Jan 120, next entry available 25th Jan 125, from 3rd Jan 24 Jan, our value should be 120. How to arrive ? Thanks @oraclebase — Vikki (@vikkiarul) April 23, 2019 Rephrasing the question: … Continue reading Using IGNORE…
16 Apr 2019
One of the most wanted features in the Oracle database is the BOOLEAN type. The SQL standard specified it a while ago, and RDBMS like PostgreSQL show how powerful it can be, e.g. when using the EVERY() aggregate function. Before you move on reading this article, note that starting with Oracle 23c, the standard BOOLEAN … Continue reading Calling an…
9 Apr 2019
A question that is frequently occurring among my SQL training‘s participants is: What’s the difference between putting a predicate in the JOIN .. ON clause and the WHERE clause? I can definitely see how that’s confusing some people, as there seems to be no difference at first sight, when running queries like these, e.g. in … Continue reading The Difference…
26 Mar 2019
What’s a good natural key? This is a very difficult question for most entities when you design your schema. In some rare cases, there seems to be an “obvious” candidate, such as a variety of ISO standards, including: ISO 639 language codes ISO 3166 country codes ISO 4217 currency codes But even in those cases, … Continue reading The Cost…
15 Mar 2019
I stumbled upon a very interesting jOOQ question on Stack Overflow that required the calculation of a weighted average. Why is that. Problem description Assuming you have this database (using PostgreSQL syntax): As can be seen, this schema is slightly denormalised as the number of lines per transaction are precalculated in the transactions.lines column. This … Continue reading Calculating Weighted…
14 Feb 2019
A fun report to write is to calculate a cumulative percentage. For example, when querying the Sakila database, we might want to calculate the percentage of our total revenue at any given date. The result might look like this: Notice the beautifully generated data. Or as raw data: payment_date |amount |percentage -------------|--------|---------- 2005-05-24 |29.92 |0.04 … Continue reading How to…
28 Jan 2019
In my previous article, I showed what the very useful percentile functions (also known as inverse distribution functions) can be used for. Unfortunately, these functions are not ubiquitously available in SQL dialects. As of jOOQ 3.11, they are known to work in these dialects: Dialect As aggregate function As window function MariaDB 10.3.3 No Yes … Continue reading How to…
22 Jan 2019
B-Tree indexes are perfect when your data is uniformly distributed. They are not really useful, when you have skewed data. I’ll explain later why this is the case, but let’s first learn how to detect “skew” What is skew? Skew is a term from statistics when a normal distribution is not symmetric. The example given … Continue reading Calculate Percentiles…
2 Jan 2019
Standard SQL is a beautiful language. Vendor specific implementations, however, have their warts. In Oracle, for example, it’s not possible to update any columns in a MERGE statement, which have been referenced by the ON clause. For example: Now, in MySQL, we can run a non-standard INSERT .. ON DUPLICATE KEY UPDATE statement like this: … Continue reading How to…
16 Nov 2018
A customer of my popular SQL training (which you should book!) has recently challenged me to optimise a hierarchical query that merges an archive log’s deltas in order to obtain a snapshot of some record at a given point in time. In this article, I will reproduce their problem statement in a simplified version and … Continue reading How to…
9 Oct 2018
All SQL databases support the standard aggregate functions COUNT(), SUM(), AVG(), MIN(), MAX(). Some databases support other aggregate functions, like: EVERY() STDDEV_POP() STDDEV_SAMP() VAR_POP() VAR_SAMP() ARRAY_AGG() STRING_AGG() But what if you want to roll your own? Java 8 Stream Collector When using Java 8 streams, we can easily roll our own aggregate function (i.e. a … Continue reading Writing Custom…
26 Sept 2018
At a customer site, I recently refactored a “slow-by-slow” PL/SQL loop and turned that into an efficient set based UPDATE statement saving many lines of code and running much faster. In this blog post, I will show how that can be done. The blog post will focus on Oracle and UPDATE, but rest assured, this … Continue reading How to…
21 Sept 2018
Everyone knows the SQL SUM() aggregate function (and many people also know its window function variant). When querying the Sakila database, we can get the daily revenue (using PostgreSQL syntax): The result will look something like this: date |daily_revenue |cumulative_revenue -----------|--------------|------------------- 2005-05-24 |29.92 |29.92 2005-05-25 |573.63 |603.55 2005-05-26 |754.26 |1357.81 2005-05-27 |685.33 |2043.14 2005-05-28 |804.04 … Continue reading How to…
20 Sept 2018
SQL is a verbose language, and one of the most verbose features are window functions. In a stack overflow question that I’ve encountered recently, someone asked to calculate the difference between the first and the last value in a time series for any given day: Input volume tstamp --------------------------- 29011 2012-12-28 09:00:00 28701 2012-12-28 10:00:00 … Continue reading How to…
7 Sept 2018
I recently stumbled upon a curious query on a customer’s productive Oracle database: Two things caught my attention: The query was executed many billions of times per month, accounting for about 0.3% of that system’s load. That’s 0.3% for something extremely silly! I don’t think that customer would ever qualify the DUAL table as SYS.DUAL, … Continue reading Beware of…
3 Sept 2018
I’ve stumbled across this fun SQL question on reddit, recently. The question was looking at a time series of data points where some events happened. For each event, we have the start time and the end time timestamp start end ----------------------------------- 2018-09-03 07:00:00 1 null 2018-09-03 08:00:00 null null 2018-09-03 09:00:00 null null 2018-09-03 10:00:00 … Continue reading Find the…
15 Aug 2018
Oracle is one of the few databases that implements the SQL standard ORDBMS extensions, which essentially allow for nested collections. Other databases that have these features to some extent are CUBRID, Informix, PostgreSQL. Oracle has two types of nested collections: The main difference at first is that a nested table can be of arbitrary size, … Continue reading How to…
13 Jul 2018
One of the things that confuse SQL users all the time is how DISTINCT and ORDER BY are related in a SQL query. The Basics Running some queries against the Sakila database, most people quickly understand: This returns results in an arbitrary order, because the database can (and might apply hashing rather than ordering to … Continue reading How SQL…
5 Jul 2018
Exciting discovery when playing around with PostgreSQL 11! New SQL standard window function clauses have been supported. If you want to play with this, you can do so very easily using docker: docker pull postgres:11 docker run --name POSTGRES11 -e POSTGRES_PASSWORD=postgres -d postgres:11 docker run -it --rm --link POSTGRES11:postgres postgres psql -h postgres -U postgres … Continue reading PostgreSQL 11’s…
27 Jun 2018
Imagine you have a configuration table like the following: It specifies a set of rules that Can be enabled / disabled Can be given a priority among themselves Include a set of flags which correspond to the thing you want to configure (e.g. some check to execute) Those flags can be ordered as well So, … Continue reading Using UNPIVOT…
25 Jun 2018
One of Oracle 12c’s coolest features was the introduction of the SQL standard OFFSET .. FETCH clause, as we can now write things like: This is querying the Sakila database. Most other databases had this clause (or a non-standard version of it) for ages, e.g. MySQL with LIMIT. For all the different LIMIT syntaxes, check … Continue reading Oracle’s OFFSET…
25 May 2018
The SQL standard knows a lesser known feature called GROUPING SETS. One particular side-effect of that feature is that we can group by “nothing” in SQL. E.g. when querying the Sakila database: This will yield: count | ------| 1000 | What’s the point, you’re asking? Can’t we just omit the GROUP BY clause? Of course, … Continue reading How to…
14 May 2018
Google’s BigQuery has a very interesting SQL language feature, which I’ve missed many times in other databases: select: SELECT [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ] [ REPLACE ( expression [ AS ] column_name [, ...] ) ] | expression [ [ AS ] alias … Continue reading Selecting all…
2 May 2018
When inserting records into SQL databases, we often want to fetch back generated IDs and possibly other trigger, sequence, or default generated values. Let’s assume we have the following table: DB2 DB2 is the only database currently supported by jOOQ, which implements the SQL standard according to which we can SELECT from any INSERT statement, … Continue reading How to…
19 Apr 2018
Something that has been said many times, but needs constant repeating until every developer is aware of the importance of this is the performance difference between row-by-row updating and bulk updating. If you cannot guess which one will be much faster, remember that row-by-row kinda rhymes with slow-by-slow (hint hint). Disclaimer: This article will discuss … Continue reading The Performance…
13 Apr 2018
In a previous blog post, I wrote about why you should (almost) always default to using bind variables. There are some exceptions, which I will cover in another follow-up post, but by default, bind variables are the right choice, both from a performance and from a security perspective. In this article, I will show an … Continue reading When Using…
12 Apr 2018
A common problem with dynamic SQL is parsing performance in production. What makes matters worse is that many developers do not have access to production environments, so they are unaware of the problem (even if there’s nothing new about this topic). What exactly is the problem? Execution plan caches Most database vendors these days ship … Continue reading Why SQL…
13 Mar 2018
The SQL standard is a nice thing. But it’s a standard. And as such, while it may provide good guidelines on how to implement some SQL features, most dialects deviate from it in one way or another (sometimes drastically, cheers, MySQL). But that doesn’t have to be a bad thing. Innovation is not driven by … Continue reading Top 10…
27 Feb 2018
Sounds fancy, right? But it’s a really nice and reasonable approach to doing dynamic SQL with jOOQ. This blog post is inspired by a Stack Overflow question, where a user wanted to turn a set of values into a dynamic UNION query like this: Note, both the Stack Overflow user and I are well aware … Continue reading Map Reducing…
20 Feb 2018
One of the biggest contributors to SQL syntax verbosity is the need to explicitly JOIN every table that somehow contributes to the query, even if that contribution is “trivial”. When looking at the Sakila database, an example could be seen easily when fetching customer data: That single access to the country information cost us 3 … Continue reading Type Safe…
5 Feb 2018
A really geeky way to start a Monday morning is to be nerd-sniped by the cool Fermat’s Library twitter account… … reading up on the cool Tupper’s Self-Referential Formula thinking “Can This be Done in SQL?™” As we all know from a previous article, SQL is turing complete, so the answer must be yes. And … Continue reading Calculating Tupper’s…
12 Jan 2018
jOOQ’s main value proposition is obvious: Type safe embedded SQL in Java. People who actively look for such a SQL builder will inevitably stumble upon jOOQ and love it, of course. But a lot of people don’t really need a SQL builder – yet, jOOQ can still be immensely helpful in other situations, through its … Continue reading Top 5…
22 Dec 2017
Thanks to the generous contributions of Timur Shaidullin, jOOQ 3.11 will now support GRANT and REVOKE statements through #6812. While implementing integration tests for these new features, I had researched the different ways how these statements work on a variety of databases, and the good news is, they’re all mostly quite standardised (in fact, they’re … Continue reading Do not…
This answer to a beautiful Stack Overflow question I’ve given recently needs further explanation in a blog post. When working with Microsoft Excel, we can create beautiful and also very insightful Pivot Tables with grand totals. What are they? This is best explained visually. Assuming you have this normalised form for your raw data. As … Continue reading Creating a…
18 Dec 2017
Or: Move That Loop into the Server Already! This article will illustrate the significance of something that I always thought to be common sense, but I keep seeing people getting this (very) wrong in their productive systems. Chances are, in fact, that most applications out there suffer from this performance problem – and the fix … Continue reading The Cost…
15 Dec 2017
When working with Oracle stored procedures, it is not uncommon to have debug log information available from DBMS_OUTPUT commands. For instance, if we have a procedure like this: The procedure works just the same, regardless if we’re reading the output from the DBMS_OUTPUT call. It is there purely for logging purposes. Now, if we call … Continue reading How to…
6 Nov 2017
Usually, this blog is 100% pro window functions and advocates using them at any occasion. But like any tool, window functions come at a price and we must carefully evaluate if that’s a price we’re willing to pay. That price can be a sort operation. And as we all know, sort operations are expensive. They … Continue reading How to…
28 Sept 2017
Cost Based Optimisation is the de-facto standard way to optimise SQL queries in most modern databases. It is the reason why it is really really hard to implement a complex, hand-written algorithm in a 3GL (third generation programming language) such as Java that outperforms a dynamically calculated database execution plan, that has been generated from … Continue reading 10 Cool…
22 Sept 2017
A very common type of SQL query is the TOP-N query, where we need the “TOP N” records ordered by some value, possibly per category. In this blog post, we’re going to look into a variety of different aspects to this problem, as well as how to solve them with standard and non-standard SQL. These … Continue reading How to…
1 Sept 2017
The SQL language has one great advantage over procedural, object oriented, and “ordinary” functional programming languages. The fact that it is truly declarative (i.e. a 4GL / fourth generation programming language) means that a sophisticated optimiser can easily transform one SQL expression into another, equivalent SQL expression, which might be faster to execute. How does … Continue reading JOIN Elimination:…
22 Aug 2017
SQL is a really cool language. I can write really complex business logic with this logic programming language. I was again thrilled about SQL recently, at a customer site: Writing some nifty SQL queries for a customer. Wondering why anyone would even consider using a 3GL for any business logic at all?? — Lukas Eder … Continue reading Finding all…
25 Jul 2017
jOOQ has been around for a while now (since 2009!) and by now we can say we’ve seen quite a bit of things about the SQL and Java languages. Some of our design decisions are particular in the way jOOQ thinks about programming with SQL. These include: Nullability (let’s stop fighting it) Value types (let’s … Continue reading 5 Things…
24 Jul 2017
In recent months, there had been some really exciting news from the MySQL team: (Recursive) Common Table Expressions in MySQL Introducing Window Functions These two SQL standard language features are among the most powerful SQL features that are available from most other databases. I frequently include them in conference talks about SQL (see my article … Continue reading jOOQ 3.10…
29 Jun 2017
In recent days, I’ve seen a bit too much of this: Something is very wrong with the above example. Can you see it? No? Let me rename those variables for you. Better now? Exactly. The above algorithm is O(N) when it could be O(1): (Let’s assume the lack of explicit ordering is irrelevant) I’m working … Continue reading A Basic…
28 Jun 2017
In this article, I will establish how the SQL language and its implementations distinguish between changed values and modified values, where a changed value is a value that has been “touched”, but not necessarily modified, i.e. the value might be the same before and after the change. Many ORMs, unfortunately, either update all of a … Continue reading ORMs Should…
2 Jun 2017
ANOTHER SQL Post this week? I got nerd-sniped: Lazy Internet: In Oracle, how do i query for where (A, B, C) in (('a', 'b', null), 'a', null, 'c') considering Oracle's null handling? — Rafael Winterhalter (@rafaelcodes) June 2, 2017 Oooooh, challenge accepted! So, let’s assume we have a table T with columns (A, B, C) … Continue reading How to…
1 Jun 2017
The following two indexes are redundant in most SQL databases: It is usually safe to drop the first index, because all queries that query the LAST_NAME column only can still profit from the second index I_ACTOR_2. The reason being that LAST_NAME is the first column of the composite index I_ACTOR_2 (it would be a different … Continue reading How to…
31 May 2017
I stumbled upon an interesting question on Stack Overflow recently. A user wanted to query a table for a given predicate. If that predicate returns no rows, they wanted to run another query using a different predicate. Preferably in a single query. Challenge accepted! Canonical Idea: Use a Common Table Expression We’re querying the Sakila … Continue reading How to…
30 May 2017
Users of jOOQ, PL/SQL, T-SQL are spoiled as they hardly ever need to worry about bind values. Consider the following statements: Using jOOQ The method parameters firstName and lastName will be automatically mapped to bind values in the generated SQL statement. Here’s the debug log output when running the above, where the first statement is … Continue reading When to…
22 May 2017
There are some situations where you would like to have at least one (empty) row in your result set in SQL. Imagine the following situation. We’re querying the Sakila database for actors and their films: yielding something like: +------------+-----------+---------------------+ | FIRST_NAME | LAST_NAME | TITLE | +------------+-----------+---------------------+ | ... | ... | ... | | … Continue reading How to…
8 May 2017
The Difficulty of Tuning Queries Over a Database Link – Or How I Learned to Stop Worrying and Love the DUAL@LINK Table
jOOQA large-ish customer in banking (largest tables on that particular system: ~1 billion rows) once decided to separate the OLTP database from the “log database” in order to better use resources and prevent contention on some tables, as the append-only log database is used heavily for analytic querying of all sorts. That seems to make … Continue reading The Difficulty…
3 May 2017
In SQL, quite often, we want to compare several values with each other. For instance, when we’re looking for a specific user by their first and last names, we’ll write a query like this one: We’re getting: CUSTOMER_ID FIRST_NAME LAST_NAME ------------------------------------ 8 SUSAN WILSON Surely, everyone agrees that this is correct and perfectly fine as … Continue reading Don’t Use…
20 Apr 2017
At a customer site, I’ve recently encountered a report where a programmer needed to count quite a bit of stuff from a single table. The counts all differed in the way they used specific predicates. The report looked roughly like this (as always, I’m using the Sakila database for illustration): And then, unsurprisingly, combinations of … Continue reading How to…
31 Mar 2017
When looking at execution plans in Oracle, we’ll have to do several steps to be able to call the DBMS_XPLAN package functions. In fact, we have to find out the SQL_ID for a given statement first, and only then we can get its plan. I’ve blogged about this previously, here. However, thanks to lateral unnesting, … Continue reading How to…
30 Mar 2017
Hah! Got nerd-sniped again: https://stackoverflow.com/questions/43099226/how-to-make-jooq-to-use-arrays-in-the-in-clause/43102102 A jOOQ user was wondering why jOOQ would generate an IN list for a predicate like this: Java SQL … when in fact there could have been the following predicate being generated, instead: In the second case, there would have been only one single bind variable instead of 4, and … Continue reading SQL IN…
29 Mar 2017
Tuning SQL isn’t always easy, and it takes a lot of practice to recognise how any given query can be optimised. One of the most important slides of my SQL training is the one summarising “how to be fast”: Some of these bullets were already covered on this blog. For instance avoiding needless, mandatory work, … Continue reading How to…
17 Mar 2017
I was nerd-sniped: So tables dee and dum are two theoretical tables in SQL, and they can be characterised as such: [Dee] is the relation that has no attributes and a single tuple. It plays the role of True. [Dum] is the relation that has no attributes and no tuples. It plays the role of … Continue reading Creating Tables…
16 Mar 2017
There are many many opinions out there regarding the old surrogate key vs. natural key debate. Most of the times, surrogate keys (e.g. sequence generated IDs) win because they’re much easier to design: They’re easy to keep consistent across a schema (e.g. every table has an ID column, and that’s always the primary key) They’re … Continue reading Faster SQL…
8 Mar 2017
Probably the most impactful thing you could learn about when writing efficient SQL is indexing. A very close runner-up, however, is the fact that a lot of SQL clients demand tons of “unnecessary, mandatory work” from the database. Repeat this after me: Unnecessary, Mandatory Work What is “unnecessary, mandatory work”? It’s two things (duh): Unnecessary … Continue reading Many SQL…
7 Mar 2017
There are already only very few real-world use-cases for FULL [ OUTER ] JOIN, but maybe, you have run into this beast in the past. But when was the last time you’ve seen a NATURAL JOIN? Right. A quick reminder from our article about JOINs: FULL JOIN A FULL JOIN is a type of OUTER … Continue reading Impress Your…
10 Feb 2017
Sometimes, SQL can just be so beautiful. One of the less mainstream features in SQL is the array type (or nested collections). In fact, it’s so not mainstream that only 2 major databases actually support it: Oracle and PostgreSQL (and HSQLDB and H2 in the Java ecosystem). In PostgreSQL, you can write: Or in Oracle: … Continue reading Beautiful SQL:…
8 Feb 2017
Earlier this week, I’ve blogged about how to execute SQL batches with JDBC and jOOQ. This was useful for the MySQL, SQL Server, and Sybase users among you. Today, we’ll discuss a slightly more difficult task, how to fetch Oracle 12c implicit cursors – which are essentially the same thing. What’s an implicit cursor? Oracle … Continue reading How to…
6 Feb 2017
Some databases (in particular MySQL and T-SQL databases like SQL Server and Sybase) support a very nice feature: They allow for running a “batch” of statements in a single statement. For instance, in SQL Server, you can do something like this: This is a batch of 4 statements, and it can be executed as a … Continue reading How to…
18 Jan 2017
A very interesting feature of the SQL Server and PostgreSQL databases (and some others, including SQLite) is the partial index (sometimes also called “filtered index”). That’s an index that contains only “parts” of the table data. For instance, we can write the following index in SQL Server and PostgreSQL: Let’s imagine you have a house … Continue reading How to…
12 Jan 2017
Perhaps the most powerful SQL feature is the JOIN operation. It is the envy of all non-relational databases, because the concept is so simple, yet so universally applicable, when you want to “combine” two data sets. Put simply, when joining two tables, you’re combining every row from one table with every row from another table, … Continue reading A Probably…
10 Jan 2017
SQL Server has this nice feature called table-valued parameters (TVP), where users can pass table variables to a stored procedure for bulk data processing. This is particularly nice when the stored procedure is an inline table valued function, i.e. a function that returns a table as well. For instance: The above function creates a cross … Continue reading jOOQ 3.10…
14 Dec 2016
Recently, at Devoxx, I’ve seen this beautiful slide in a talk by Kevlin Henney In his talk, he was displaying a variety of approaches to solve the FizzBuzz “problem”, including a couple of very elegant solutions in completely declarative approaches and languages. In this particular slide, Kevlin used a notation that is derived from maths. … Continue reading SQL, Streams,…
9 Dec 2016
The SQL language is very intuitive. Until it isn’t. Over the years, a lot of people have criticised the SQL language for a variety of reasons. For instance: IDEs cannot easily guess what auto completion options to offer, because as long as you don’t specify the FROM clause, there are no tables in scope (yet): … Continue reading A Beginner’s…
5 Dec 2016
As long as we allow ourselves to write string-based dynamic SQL embedded in other programming languages like Java, we will have a certain risk of being vulnerable to SQL injection. That’s a fact. Don’t believe it? Check out this website exposing all vulnerabilities on Stack Overflow for PHP questions: https://laurent22.github.io/so-injections In a previous blog post, … Continue reading Prevent SQL…
8 Nov 2016
Some of the biggest limitations when working with Oracle PL/SQL from Java is the lack of support for a variety of PL/SQL features through the JDBC interface. This lack of support is actually not limited to JDBC, but also extends to Oracle SQL. For instance, if you’re using the useful PL/SQL BOOLEAN type as such: … Continue reading Use jOOQ…
2 Nov 2016
I’m very happy to have another interesting blog post by Vlad Mihalcea on the jOOQ blog, this time about his Open Source library flexypool. Read his previous jOOQ Tuesdays post on Hibernate here. Vlad is a Hibernate developer advocate and he’s the author of the popular book High Performance Java Persistence, and he knows 1-2 … Continue reading Applying Queueing…
1 Nov 2016
I’ve recently discovered a rather significant performance issue on a productive Oracle 11g customer database. And I’m sure you have this issue too, which is why I’m documenting it here. This is a simplified representation of the setup at the customer site: ID PAYMENT_DATE TEXT ---------- ------------ ----------------------------------- 33803 21.05.16 DcTNBOrkQIgMtbietUWOsSFNMIqGLlDw... 29505 09.03.16 VIuPaOAQqzCMlFBYPQtvqUSbWYPDndJD... 10738 … Continue reading Why You…
31 Oct 2016
I’m frequently telling developers to put window functions almost everywhere, because they’re so awesome! One feature that I rarely see in the wild (even if it is extremely useful for reporting) is called “logical windowing” in Oracle, and it’s most useful when used with INTERVAL ranges. Let’s see what we may want to do. I … Continue reading A Little…
28 Oct 2016
In a recent blog post, I’ve advocated against the use of COUNT(*) in SQL, when a simple EXISTS() would suffice. This is important stuff. I keep tuning productive queries where a customer runs a COUNT(*) query like so: … where after they discard the exact count to only check for existence: It doesn’t matter if … Continue reading Don’t Even…
21 Oct 2016
Are you working with someone else’s schema and they haven’t declared nice names for all their constraints? Unfortunately, it is all too easy to create a table like this: Or like this: Sure, you get a little convenience when writing the table. But from now on, you’re stuck with weird, system generated names both for … Continue reading How to…
20 Oct 2016
Everyone who writes complex SQL (or dare I say, move business logic into the database?) wishes for parameterised views from time to time. This hypothetical syntax would be terrific: Or as user-defined functions if you insist (as available in SQL Server): As always on this blog, I’m using the useful Sakila database for examples. The … Continue reading Be Careful…
7 Oct 2016
Adding the right index to speed up your queries is essential. But after a while, as your system grows, you may find yourself with tons of indexes, which all slow down writing to the database – as with each write to the table, the index needs to be updated as well in the same transaction. … Continue reading Does Your…
5 Oct 2016
In my SQL Masterclass, I frequently remind participants of the fact how important statistics are for a modern cost based optimiser. For instance, if you consider the fact that in an average E-Banking system’s bookings table, you will probably have a transaction amount histogram like the following: In other words, most of your transactions are … Continue reading Why You…
28 Sept 2016
Some people make architecture decisions purely based on the loudest consultant: no, we base all our important architectural decisions on # of tweets. It's a Twitter Oriented Architecture ;) — Gareth Western (@gareth) September 21, 2016 For most others, however, decisions are not as simple as this. For instance: When should we start evaluating NoSQL … Continue reading When to…
27 Sept 2016
A recent question on Stack Overflow about jOOQ caught my attention. The question essentially asked: Why do both of these loops work? And indeed, just like in PL/SQL, you can use any jOOQ ResultQuery as a Java 5 Iterable, because that’s what it is. An Iterable<R> where R extends Record. The semantics is simple. When … Continue reading A Hidden…
14 Sept 2016
A while ago, I blogged about the importance of avoiding unnecessary COUNT(*) queries:https://blog.jooq.org/sql-tip-of-the-day-be-wary-of-select-count … and how to replace them with equivalent EXISTS queries As I’m updating the SQL training to show also PostgreSQL performance characteristics in addition to Oracle, I really have to reiterate this topic. Please repeat after me: Thou shalt not use COUNT(*) … Continue reading Avoid Using…
17 Aug 2016
Welcome to the jOOQ Tuesdays series. In this series, we’ll publish an article on the third Tuesday every other month (today, exceptionally on a Wednesday because of technical issues) where we interview someone we find exciting in our industry from a jOOQ perspective. This includes people who work with SQL, Java, Open Source, and a … Continue reading jOOQ Tuesdays:…
10 Aug 2016
Pagination is one of those things that almost everyone gets wrong for two reasons: Here’s why. What’s wrong with pagination? Most applications blindly produce pagination like this: This is how GMail implements pagination. With my current settings, it displays 100 E-Mails at a time and also shows how many E-Mails there are in total, namely … Continue reading Why Most…
8 Aug 2016
I’ve recently made an embarassing discovery: wha. I've never used while loops in PL/SQL. TIL :) — Lukas Eder (@lukaseder) July 26, 2016 Yes. In all of my professional work with PL/SQL (and that has been quite a bit, in the banking industry), I have never really used a WHILE loop – at least not … Continue reading Why I…
20 Jul 2016
We programmers keep cargo culting these wrong ideas. Recently, we said “NO” to Venn diagrams. Today we’re going to say no to surrogate keys. The surrogate keys vs. natural keys non-debate is one of the most overheated debates in data architecture, and I don’t get why everyone is so emotional. Both sides claim to hold … Continue reading Say NO…
15 Jul 2016
It seems that perfection is attained not when there is nothing more to add, but when there is nothing more to remove. – Antoine de Saint Exupéry in Terre des Hommes As SQL developers, we keep adding more and more indexes to our tables. Every time we run new queries that are potentially slow, a … Continue reading How to…
14 Jul 2016
A lot of people use SQL constraints mainly to enforce data integrity, and that’s already a very good thing. A UNIQUE constraint, for instance, makes sure that there is at most one instance of any possible value (or tuple, in the case of a composite constraint) in a table. For instance: Constraints are also good … Continue reading How Adding…
12 Jul 2016
A recent Tweet by Aaron Bertrand (whom you’ve certainly encountered on Stack Overflow) has triggered my interest Or the one that asks a bunch of questions about ANY / ALL syntax, which nobody has used since Celko was still in college? — Aaron Bertrand (@AaronBertrand) July 11, 2016 Indeed, few people I’ve met and who’ve … Continue reading Quantified Comparison…
7 Jul 2016
Catchy headline, yes. But check out this Stack Overflow question by user Mike: (I’m duplicating it here on the blog, as it might be deleted soon) It’s a pretty open ended question. I’ll be starting out a new project and am looking at different ORMs to integrate with database access. Do you have any favorites? … Continue reading “What Java…
5 Jul 2016
In recent times, there have been a couple of tremendously popular blog posts explaining JOINs using Venn Diagrams. After all, relational algebra and SQL are set oriented theories and languages, so it only makes sense to illustrate set operations like JOINs using Venn Diagrams. Right? Google seems to say so: Everyone uses Venn Diagrams to … Continue reading Say NO…
21 Jun 2016
Welcome to the jOOQ Tuesdays series. In this series, we’ll publish an article on the third Tuesday every other month where we interview someone we find exciting in our industry from a jOOQ perspective. This includes people who work with SQL, Java, Open Source, and a variety of other related topics. I’m very excited to … Continue reading jOOQ Tuesdays:…
27 May 2016
A common myth in SQL is the idea that correlated subqueries are evil and slow. For example, this query here: It “forces” the database engine to run a nested loop of the form (in pseudo code): So, for every actor, collect all the corresponding film_actors and count them. This will produce the number of films … Continue reading Correlated Subqueries…
25 Apr 2016
Listicles like these do work – not only do they attract attention, if the content is also valuable (and in this case it is, trust me), the article format can be extremely entertaining. This article will bring you 10 SQL tricks that many of you might not have thought were possible. The article is a … Continue reading 10 SQL…
12 Apr 2016
Security is important, especially on the data access layer. Most commercial databasese allow for fine-grained privilege control using database access grants. For instance, you would be restricting access from a user to a certain set of tables (or even better: views), via GRANT statements: With this fine-grained access control, write operations on certain database objects … Continue reading Using jOOQ’s…
11 Apr 2016
Few people know about this very very awesome feature of the Stack Exchange platform. The Stack Exchange Data Explorer To be found here: http://data.stackexchange.com As you may know, much of the Stack Exchange platform runs on SQL Server (interesting architecture details here: http://stackexchange.com/performance), and the team has had the courtesy of making a lot of … Continue reading Using SQL…
24 Mar 2016
This Stack Overflow question has yet again nerd-sniped me [finding the] maximum element in the array that would result from performing all M operations Here’s the question by John that was looking for a Java solution: With an array of N elements which are initialized to 0. we are given a sequence of M operations … Continue reading Time for…
17 Mar 2016
Too many programmers think SQL is a bit of a beast. It is one of the few declarative languages out there, and as such, behaves in an entirely different way from imperative, object-oriented, or even functional languages (although, some say that SQL is also somewhat functional). As a SQL trainer (do visit our training, it’s … Continue reading 10 Easy…
9 Mar 2016
A lot of developers get the distinction between JOIN and SEMI-JOIN wrong. Let me explain… What are JOIN and SEMI-JOIN A little bit of relational algebra first. What is an (INNER) JOIN? An JOIN is nothing but a filtered cartesian product. And what is a cartesian product? Wikipedia explains this very nicely: for sets A … Continue reading SQL JOIN…
28 Jan 2016
Just now, we implemented a nice little feature in jOOQ’s code generator: https://github.com/jOOQ/jOOQ/issues/4974 It detects whenever the jOOQ code generator runs a slow query to reverse engineer schema meta information. Why? In our development and integration test environment, we don’t have huge schemas with all the different performance edge cases put in place. For instance, … Continue reading How to…
18 Jan 2016
I’ve recently encountered a very interesting question on Stack Overflow by an unnamed user. The question was about generating a table of the following form in Oracle, using a table valued function: Description COUNT ------------------- TEST1 10 TEST2 15 TEST3 25 TEST4 50 The logic that should be implemented for the COUNT column is the … Continue reading Impress Your…
14 Jan 2016
Notice that the examples in this article may be outdated, as Typesafe’s Activator works differently now. The blog post will not be maintained to provide up-to-date Activator examples. We’re very happy to continue our a guest post series on the jOOQ blog by Manuel Bernhardt. In this blog series, Manuel will explain the motivation behind … Continue reading Reactive Database…
30 Dec 2015
Like many old databases, Oracle has legacy data types, which are rather nasty to work with in every day SQL. Usually, you don’t run into wild encounters of LONG and LONG RAW data types anymore, but when you’re working with an old database, or with the dictionary views, you might just have to deal with … Continue reading Oracle LONG…
17 Dec 2015
The following is a very common problem in all data related technologies and we’re going to look into two very lean, SQL-based solutions for it: How do I fill the cells of a sparse data set with the “previous non-empty value”? The problem The problem is really simple and I’m reusing the example provided by … Continue reading How to…
16 Dec 2015
Notice that the examples in this article may be outdated, as Typesafe’s Activator works differently now. The blog post will not be maintained to provide up-to-date Activator examples. We’re very happy to continue our a guest post series on the jOOQ blog by Manuel Bernhardt. In this blog series, Manuel will explain the motivation behind … Continue reading Reactive Database…
10 Dec 2015
Relational databases define the term “Functional Dependency” as such (from Wikipedia): In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, functional dependency is a constraint that describes the relationship between attributes in a relation. In SQL, functional dependencies appear whenever … Continue reading SQL GROUP…
3 Dec 2015
Notice that the examples in this article may be outdated, as Typesafe’s Activator works differently now. The blog post will not be maintained to provide up-to-date Activator examples. We’re very happy to announce a guest post series on the jOOQ blog by Manuel Bernhardt. In this blog series, Manuel will explain the motivation behind so-called … Continue reading Reactive Database…
7 Nov 2015
A very interesting problem that can be solved very easily with SQL is to find consecutive series of events in a time series. But what is a consecutive series of events in a time series? Take Stack Overflow, for example. Stack Overflow has a cool reputation system that uses badges to reward certain behaviour. As … Continue reading How to…
26 Oct 2015
I’ve stumbled upon this very interesting question on Stack Overflow, recently. Its title is: [How to] compare a number with sum of subset of numbers In this article, we’ll compare the user’s imperative approach to the extremely elegant (Oracle) SQL approach. We’ll be making use of any combination of these awesome SQL features: Window functions … Continue reading How to…
15 Oct 2015
About two years ago, we’ve published this post about the 10 most popular DB engines, where we analyzed the data published by Solid IT on their DB Ranking website. In the meantime, the Solid IT measurement system has found to be a credible source, such that the website has also been cited at Gartner, InfoWorld, … Continue reading The 10…
13 Oct 2015
Relational algebra nicely describes the various operations that we know in SQL as well from a more abstract, formal perspective. One of the most common relational JOIN operations is the “equi-join” or SQL INNER JOIN. The above example “equi-joins” the ACTOR, FILM_ACTOR, and FILM tables from the Sakila database, in order to produce a new … Continue reading Semi Join…
8 Oct 2015
Do you want to know real quick what kind of indexes there are on any given table in your Oracle schema? Nothing simpler than that. Just run the following query: The above query is ran against the Sakila database. Just replace the “FILM_ACTOR” table by your table and you’re all set. The result looks like: … Continue reading How to…
6 Oct 2015
When people talk about SQL JOIN, they often use Venn Diagrams to illustrate inclusion and exclusion of the two joined sets: While these Venn diagrams are certainly useful to understand (and remember) SQL JOIN syntax, they’re not entirely accurate, because SQL JOIN is a special type of a cartesian product, the CROSS JOIN. In a … Continue reading You Probably…
30 Sept 2015
Exciting times are ahead for Java/SQL developers. SQL is strong and popular as ever. It is the right language for relational databases just as much as for Big Data storage systems. Just this week, Pivotal has announced their open sourcing HAWQ and MADlib as a part of their Hadoop Native SQL strategy. At Data Geekery, … Continue reading Let’s Meet…
24 Sept 2015
We’re very happy to announce a guest post by Marco Behler, who has been blogging about jOOQ in the past. Marco started out in programming (reverse-engineering, actually) and now mainly programmes on the JVM in his day-to-day work. He also always had a sweet tooth for strategy and marketing. Marco Behler GmbH is the result … Continue reading It is…
15 Sept 2015
I’m seeing people do this all the time. They want to hammer a date or timestamp constant into their SQL query, and the only function they know is the TO_DATE() or TO_TIMESTAMP() date parsing function: As observed in this Stack Overflow question, for instance: Date parsing is important only if your date input is really … Continue reading Don’t Format…
26 Aug 2015
Much of the ORM criticism of the last decade missed the point, being inaccurate. By the end of this article, we will conclude with the following: There is no significant difference between the relational (data) model and object oriented models How to come to this conclusion? Read on! How we came to believe in this … Continue reading There is…
18 Aug 2015
Welcome to the jOOQ Tuesdays series. In this series, we’ll publish an article on the third Tuesday every other month where we interview someone we find exciting in our industry from a jOOQ perspective. This includes people who work with SQL, Java, Open Source, and a variety of other related topics. We have the pleasure … Continue reading jOOQ Tuesdays:…
13 Aug 2015
Functional programming allows for quasi-declarative programming in a general purpose language. By using powerful fluent APIs like Java 8’s Stream API, or jOOλ’s sequential Stream extension Seq or more sophisticated libraries like vavr or functionaljava, we can express data transformation algorithms in an extremely concise way. Compare Mario Fusco’s imperative and functional version of the … Continue reading Common SQL…
5 Aug 2015
Your data fits in RAM. Yes, it does. Don’t believe it? Visit the hilarious yourdatafitsinram.com website. But there is an entirely new dimension to this since last week’s announcement by Intel, which hasn’t gotten enough attention in the blogosphere yet. New 3D XPoint™ technology brings non-volatile memory speeds up to 1,000 times faster than NAND, … Continue reading RAM is…
4 Aug 2015
Have you ever wondered how you could express a predicate that “feels” like the following, in SQL: /u/CyBerg90 has, on reddit. The idea was to create a predicate that yields true whenever both values Var1 and Var2 yield either 1, 2, or 3. The canonical solution The canonical solution would obviously be to write it … Continue reading INTERSECT –…
22 Jul 2015
A short while ago, I gave this answer on Quora. The question was “What is the significance of NULL in SQL?” and most of the existing answers went on about citing C.J. Date or Tony Hoare and unanimously declared NULL as “evil”. So, everyone rants about NULL all the time. Let me counter-rant. Academics Of … Continue reading NULL is…
17 Jun 2015
Before you read on, please note that since jOOQ 3.19, policies are supported out of the box, to implement the same functionality in a much simpler way than what this article suggests! Some time ago, we’ve promised to follow up on our Constraints on Views article with a sequel showing how to implement client-side row-level … Continue reading Implementing Client-Side…
2 Jun 2015
You probably know about “ordinary views” already, but I’m sure you’ll find one or two things in this article that you haven’t thought about in this way yet… What exactly are SQL views? Views in SQL are a means of treating complex queries in the same way as “ordinary” tables. In fact, SQL is all … Continue reading What Exactly…
12 May 2015
Whenever you feel that itch… Can’t I calculate this with SQL? The answer is: Yes you can! And you should! Let’s see how… Calculating time differences between rows Let’s consider the following database containing timestamps (e.g. in a log database). We’re using PostgreSQL syntax for this: Obviously, you’ll be adding constraints and indexes, etc. Now, … Continue reading Use this…
7 May 2015
PostgreSQL has a lot of secret data types. In recent times, PostgreSQL’s JSON and JSONB support was hyped as being the NoSQL on SQL secret (e.g. as advertised by ToroDB) that allows you to get the best out of both worlds. But there are many other useful data types, among which the range type. How … Continue reading PostgreSQL’s Best-Kept…
5 May 2015
In the recent past, we’ve explained the syntactic implications of the SQL GROUP BY clause. If you haven’t already, you should read our article “Do You Really Understand SQL’s GROUP BY and HAVING clauses?“. In essence, adding a GROUP BY clause to your query transforms your query on very implicit levels. The following reminder summarises … Continue reading How SQL…
28 Apr 2015
I keep encountering situations where RDBMS users think that one second for query execution is anything near fast. Most recently, in this Stack Overflow question: Hibernate SQL In clause making CPU usage to 100% The poster’s original question was why a similar query executes in one second when executed in SQL Server Management Studio whereas … Continue reading Do Not…
27 Apr 2015
LOBs are a PITA in all databases, as well as in JDBC. Handling them correctly takes a couple of lines of code, and you can be sure that you’ll get it wrong eventually. Because you have to think of a couple of things: Foremost, LOBs are heavy resources that need special lifecycle management. Once you’ve … Continue reading Let’s Review…
13 Apr 2015
Syntax is one of those topics. One of those emotional topics that lead to very very very important discussions. I personally like PL/SQL. It is extremely verbose, and precise. It forces you to adhere to a very strong and rigid type system, slowing you down, which is likely to help you avoid mistakes. There is … Continue reading It’s the…
7 Apr 2015
Sometimes you simply cannot avoid it: Pessimistic locking via SQL. In fact, it’s an awesome tool when you want to synchronise several applications on a shared, global lock. Some may think this is abusing the database. We think use the tools you have if they can solve the problem you have. For instance, the RDBMS … Continue reading How to…
26 Mar 2015
Some queries shouldn’t hit the database all the time. When you query for master data (such as system settings, languages, translations, etc.), for instance, you may want to avoid sending the same silly query (and the results) over the wire all the time. For example: Most databases maintain buffer caches to accelerate these queries, so … Continue reading Hack up…
24 Mar 2015
Hibernate has become a de-facto standard in the Java ecosystem, and after the fact, also an actual JavaEE standard implementation if standards matter to you, and if you put the JCP on the same level with ISO, ANSI, IEEE, etc. This article does not intended to discuss standards, but visions. Hibernate shares JPA’s vision of … Continue reading jOOQ vs.…
12 Mar 2015
The past decade has been an extremely exciting one in all matters related to data. We have had: An ever increasing amount of data produced by social media (once called “Web 2.0”) An ever increasing amount of data produced by devices (a.k.a. the Internet of Things) An ever increasing amount of database vendors that explore … Continue reading 3 Reasons…
2 Mar 2015
We’ve published an article in the German magazine www.java-aktuell.de, which is published by the iJUG e.V.. You can read and download the article free of charge from our blog! In Java gibt es kein Standard-API, das die Ausdrucksstärke und Mächtigkeit von SQL direkt unterstützt. Alle Aufmerksamkeit ist auf objekt-relationales Mapping und andere höhere Abstraktionslevel gerichtet, … Continue reading jOOQ –…
26 Feb 2015
This can happen ever so easily. You adapt a table by adding a new column: You go on, implementing your business logic – absolutely no problem. But then, later on (perhaps in production), some batch job fails because it makes some strong assumptions about data types. Namely, it assumes that the two tables payments and … Continue reading How to…
25 Feb 2015
The Modern SQL Twitter account (by Markus Winand) published a hint about how to extract a date part in SQL: The right way to get a part of a date/time is: EXTRACT(YEAR FROM CURRENT_DATE) = 2015http://t.co/UNLyUoQdVb Retweet to spread the word! — Modern SQL (@ModernSQL) February 24, 2015 Is it true? Yes it is, in … Continue reading How to…
13 Feb 2015
We’ve been blogging about Java and SQL for a while now, on the jOOQ blog. Over the years, while researching interesting blog topics, we’ve discovered a lot of SQL gems in the blogosphere that have inspired our work and our passion for SQL. Today, we’re presenting to you a list of 10 articles that we … Continue reading 10 SQL…
3 Feb 2015
We’ve been blogging a lot about the merits of modern SQL on the jOOQ blog. Specifically, window functions are one of the most fascinating features. But there are many many others. Markus Winand, author of the popular book SQL Performance Explained has recently given a very well-researched talk about modern SQL. We particularly like his … Continue reading Still Using…
8 Jan 2015
In the recent past, we’ve shown how Java 8 and functional programming will bring a new perspective to Java developers when it comes to functional data transformation of SQL data using jOOQ and Java 8 lambdas and Streams. Today, we take this a step further and transform the data into JavaFX XYChart.Series to produce nice-looking … Continue reading Transform Your…
6 Jan 2015
Some databases are awesome enough to implement the MEDIAN() aggregate function. Remember that the MEDIAN() is sligthly different from (and often more useful than) the MEAN() or AVG() (average). While the average is calculated as the SUM(exp) / COUNT(exp), the MEDIAN() tells you that 50% of all values in the sample are higher than the … Continue reading How to…
30 Dec 2014
Sometimes when aggregating data with SQL, we’d love to add some additional filters. For instance, consider the following world bank data: GDP per capita (current US$) 2009 2010 2011 2012 CA 40,764 47,465 51,791 52,409 DE 40,270 40,408 44,355 42,598 FR 40,488 39,448 42,578 39,759 GB 35,455 36,573 38,927 38,649 IT 35,724 34,673 36,988 33,814 … Continue reading The Awesome…
29 Dec 2014
We’ve recently published an article about how to bind the Oracle DATE type correctly in SQL / JDBC, and jOOQ. This article got a bit of traction on reddit with an interesting remark by Vlad Mihalcea, who is frequently blogging about Hibernate, JPA, transaction management and connection pooling on his blog. Vlad pointed out that … Continue reading Leaky Abstractions,…
22 Dec 2014
We all know that Oracle’s DATE is not really a date as in the SQL standard, or as in all the other databases, or as in java.sql.Date. Oracle’s DATE type is really a TIMESTAMP(0), i.e. a timestamp with a fractional second precision of zero. Most legacy databases actually use DATE precisely for that, to store … Continue reading Are You…
18 Dec 2014
We’ve just added support for the EVERY() aggregate function (#1391) to jOOQ, and would like to take the opportunity of letting you know of this true SQL gem that can come in handy EVERY(now and then) (pun intended). Let’s assume we have four books in our table: Now the question is: Is EVERY() ID lower … Continue reading A True…
16 Dec 2014
We’re excited to launch a new series on our blog: the jOOQ Tuesdays. In this series, we’ll publish an article on the third Tuesday every month where we interview someone we find exciting in our industry from a jOOQ perspective. This includes people who work with SQL, Java, Open Source, and a variety of other … Continue reading jOOQ Tuesdays:…
11 Dec 2014
Sometimes there are these moments of truth. They happen completely unexpectedly, such as when I read this tweet: Good discussion of Facebook Flow – http://t.co/5KTKakDB0w — David J. Pearce (@whileydave) November 23, 2014 David is the author of the lesser-known but not at all lesser-interesting Whiley programming language, a language that has a lot of … Continue reading The Inconvenient…
4 Dec 2014
There are some things in SQL that we simply take for granted without thinking about them properly. One of these things are the GROUP BY and the less popular HAVING clauses. Let’s look at a simple example. For this example, we’ll reiterate the example database we’ve seen in this previous article about the awesome LEAD(), … Continue reading Do You…
20 Nov 2014
MySQL is a database that has been bending the SQL standard in ways that make it hard to move off MySQL. What may appear to be a clever technique for vendor lockin (or maybe just oversight of the standard) can be quite annoying in understanding the real meaning of the SQL language. One such example … Continue reading Use MySQL’s…
12 Nov 2014
Now that I have your attention, I’d like to invite you to a critical review of where we’re at in the MySQL vs. MariaDB debate. Around one month ago, I visited Oracle Open World 2014, and I’ve met with Morgan Tocker, the MySQL community manager at Oracle to learn about where MySQL is heading. Who … Continue reading Don’t Migrate…
11 Nov 2014
When writing DDL in SQL, you can specify a couple of constraints on columns, like NOT NULL or DEFAULT constraints. Some people might wonder, if the two constraints are actually redundant, i.e. is it still necessary to specify a NOT NULL constraint, if there is already a DEFAULT clause? The answer is: Yes! Yes, you … Continue reading Have You…
7 Nov 2014
If you’re using a commercial database or PostgreSQL / Firebird / CUBRID, you will be able to take advantage of the full power of window functions. We’ve blogged about window functions’ awesomeness a couple of times, in particular about ROW_NUMBER(), RANK(), DENSE_RANK(). Today, we’re going to look into some awesome window functions that produce values … Continue reading Don’t Miss…
4 Nov 2014
A procedural language combined with SQL can do miracles in terms of productiveness, performance and expressivity. In this article, we’ll see later on, how we can achieve the same with SQL (and PL/SQL) in Java, using jOOQ, which offers much more functionality than Oracle’s own now desupported JPublisher. But first, a little bit of history… … Continue reading Access PL/SQL…
31 Oct 2014
The jOOQ ecosystem and community is continually growing. We’re personally always thrilled to see other Open Source projects built on top of jOOQ. Today, we’re very happy to introduce you to a very interesting approach at combining REST and RDBMS by Björn Harrtell. Björn Harrtell is a swedish programmer since childhood. He is usually busy … Continue reading A RESTful…
20 Oct 2014
One of MongoDB’s arguments when evangelising MongoDB is the fact that MongoDB is a “schemaless” database: Why Schemaless? MongoDB is a JSON-style data store. The documents stored in the database can have varying sets of fields, with different types for each field. And that’s true. But it doesn’t mean that there is no schema. There … Continue reading Stop Claiming…
2 Oct 2014
More and more people are catching up with the latest update to our platform by adopting functional programming also for their businesses. At Data Geekery, we’re using Java 8 for our jOOQ integration tests, as using the new Streams API with lambda expressions makes generating ad-hoc test data so much easier. However, we don’t feel … Continue reading Don’t Miss…
26 Sept 2014
Controversial database topics are a guaranteed success on reddit, because everyone has an opinion on those topics. More importantly, many people have a dogmatic opinion, which always triggers more debate than pragmatism. So, recently, I posted a link to an older article titled The Database As Queue Anti-Pattern by Mike Hadlow, and it got decent … Continue reading Using Your…
23 Sept 2014
Reactive programming is the new buzzword, which essentially just means asynchronous programming or messaging. Fact is that functional syntax greatly helps with structuring asynchronous execution chains, and today, we’ll see how we can do this in Java 8 using jOOQ and the new CompletableFuture API. In fact, things are quite simple: What did really happen … Continue reading Asynchronous SQL…
2 Sept 2014
CHECK constraints are already pretty great when you want to sanitize your data. But there are some limitations to CHECK constraints, including the fact that they are applied to the table itself, when sometimes, you want to specify constraints that only apply in certain situations. This can be done with the SQL standard WITH CHECK … Continue reading Awesome SQL…
29 Aug 2014
For many PL/SQL developers, this might be common sense, but for one of our customers, this was an unknown PL/SQL feature: Backtraces. When your application raises an error somewhere deep down in the call stack, you don’t get immediate information about the exact source of the error. For large PL/SQL applications, this can be a … Continue reading PL/SQL backtraces…
18 Aug 2014
Oracle SYNONYMs are a great feature. You can implement all sorts of backwards-compatibility tweaks simply by creating SYNONYMs in your database. Consider the following schema: Now you can query your same old table through three different names, it’ll all result in the same output: The trouble is, when you see my_table_bak in code (or some … Continue reading All You…
14 Aug 2014
Have you ever wondered about the use-case behind SQL’s ANY (also: SOME) and ALL keywords? You have probably not yet encountered these keywords in the wild. Yet they can be extremely useful. But first, let’s see how they’re defined in the SQL standard. The easy part: 8.7 <quantified comparison predicate> Function Specify a quantified comparison. … Continue reading A Wonderful…
12 Aug 2014
One of the best features in SQL are window functions. Dimitri Fontaine put it bluntly: There was SQL before window functions and SQL after window functions If you’re lucky enough to be using any of these databases, then you can use window functions yourself: CUBRID DB2 Firebird H2 Informix MariaDB MySQL Oracle PostgreSQL SQLite SQL … Continue reading The Difference…
8 Aug 2014
Recently, I’ve encountered this sort of query all over the place at a customer site: Unfortunately, COUNT(*) is often the first solution that comes to mind when we want to check our relations for some predicate. But COUNT() is expensive, especially if all we’re doing is checking our relations for existence. Does the word ring … Continue reading SQL Tip…
6 Aug 2014
Markus Winand from Use The Index, Luke! did it again. He started an exciting battle against one the biggest flaws in the SQL language: We’ve blogged about this before. OFFSET pagination is terribly slow, once you reach higher page numbers. Besides, chances are, that your database doesn’t even implement it correctly, yet (and your emulation … Continue reading Join the…
5 Aug 2014
Every once in a while, we run into these rare SQL issues where we’d like to do something that seems out of the ordinary. One of these things is pivoting rows to columns. A recent question on Stack Overflow by Valiante asked for precisely this. Going from this table: +------+------------+----------------+-------------------+ | dnId | propNameId | … Continue reading Are You…
30 Jul 2014
Starting with jOOQ 3.5 we’ve included an awesome new feature for those of you using the Oracle database: Native support for Oracle AQ! And your client code will be so easy to write, you’ll be putting those AQs all over your database immediately. How does it work? jOOQ rationale The biggest reason why many of … Continue reading Using Oracle…
28 Jul 2014
jOOQ implements your SQL statements as AST (Abstract Syntax Tree). This means that your SQL statement is modelled in a non-text form prior to serialising it as a textual SQL statement to your JDBC driver. One advantage of this is that you can freely manipulate this AST any way you want. This can be done … Continue reading jOOQ Tip…
22 Jul 2014
So you might’ve seen Destroy all Software’s talk about JavaScript: Here’s a similar talk (less funny more scary) about MySQL:
14 Jul 2014
Imagine you want to collect detailed usage statistics to tune your Oracle database, e.g. if you want to have A-Rows and A-Time values in your execution plans (by default, Oracle only reports E-Rows and E-Time with “E” for “Estimated”. But usually, you will care more about the “A” for “Actual”). All you have to do … Continue reading Logon Triggers:…
11 Jul 2014
In the past, we’ve been providing you with a new article every Friday about what’s new in Java 8. It has been a very exciting blog series, but we would like to focus again more on our core content, which is Java and SQL. We will still be occasionally blogging about Java 8, but no … Continue reading Java 8…
7 Jul 2014
Table-valued functions are an awesome thing. Many databases support them in one way or another and so does PostgreSQL. In PostgreSQL, (almost) everything is a table. For instance, we can write: … and believe it or not, this is a table! We can write: And the above will return: +----+ | v2 | +----+ | … Continue reading PostgreSQL’s Table-Valued…
26 Jun 2014
Writing tests that use an actual database is hard. Period. Now that this has been established, let’s have a look at a blog post by Marco Behler, in which he elaborates on various options when testing database code, with respect to transactionality. Testing database transactions is even harder than just testing database code. Marco lists … Continue reading Stop Unit…
25 Jun 2014
When performing database migrations, we at Data Geekery recommend using jOOQ with Flyway – Database Migrations Made Easy. In this post, we’re going to look into a simple way to get started with the two frameworks. Philosophy There are a variety of ways how jOOQ and Flyway could interact with each other in various development … Continue reading Flyway and…
24 Jun 2014
Infrequent SQL developers often get confused about when to put parentheses and/or aliases on derived tables. There has been this recent Reddit discussion about the subject, where user Elmhurstlol was wondering why they needed to provide an alias to the derived table (the subselect with the UNION) in the following query: The question really was … Continue reading Should I…
23 Jun 2014
Most databases that support default values on their column DDL, it is also possible to actually alter that default. An Oracle example: Unfortunately, this isn’t possible in SQL Server, where the DEFAULT column property is really a constraint, and probably a constraint whose name you don’t know because it was system generated. But luckily, jOOQ … Continue reading SQL Server…
14 Jun 2014
After deep consideration with our lawyers, we would like to follow suit with Oracle and provide you with the following legal disclaimer about our jOOQ-related conference talks, as presented at the awesome GeekOut conference in Tallinn. Please do read them and take them seriously.
9 Jun 2014
I’m pretty sure you’ve gotten it wrong in numerous ways, so far. And you probably won’t get it right any time soon. So why waste your precious time on SQL tweaking, when you could be implementing business logic? Let me explain… It hasn’t been until the recent SQL:2008 standard that what MySQL users know as … Continue reading Stop Trying…
2 Jun 2014
In most cases, simply creating an Oracle SEQUENCE with all defaults is good enough: This sequence can then be used immediately in triggers when inserting new records in a table: But if your table has heavy throughput with millions of insertions per day (e.g. a log table), you better configure the sequence cache correctly. The … Continue reading Don’t Forget…
29 May 2014
This week, Timo Westkämper from QueryDSL has announced feature completeness on the QueryDSL user group, along with his call for contributions and increased focus on bugfixes and documentation. Timo and us, we have always been in close contact, observing each other’s products. In the beginning of jOOQ in 2009, QueryDSL was ahead of us. But … Continue reading QueryDSL vs.…
28 May 2014
What if developing an application just took 1-2 days? What if I can create it myself with only 10 clicks? What if I don’t need you developers anymore? Said every manager since the beginning of history. This is what all managers dream of. Click click click, next next next, and you’re done! Easy, right? Time-to-market: … Continue reading What if…
26 May 2014
Yet Another 10 Common Mistakes Java Developers Make When Writing SQL (You Won’t BELIEVE the Last One)
jOOQ(Sorry for that click-bait heading. Couldn’t resist ;-) ) We’re on a mission. To teach you SQL. But mostly, we want to teach you how to appreciate SQL. You’ll love it! Getting SQL right or wrong shouldn’t be about that You’re-Doing-It-Wrong™ attitude that can be encountered often when evangelists promote their object of evangelism. Getting … Continue reading Yet Another…
22 May 2014
If we want to analyse execution plans on the SQL console, we probably need to find a SQL_ID first, which we can the pass to the DBMS_XPLAN.DISPLAY_CURSOR function. One way to find this SQL_ID is by querying the v$sql table first, e.g.: Now, often, you will then get an error message like: ORA-00942: table or … Continue reading Oracle Tip:…
13 May 2014
SQL Server is known to have a very strict interpretation of the SQL standard. For instance, the following expressions or statements are not possible in SQL Server: Strictly speaking, that limitation makes sense because the above ROW_NUMBER() or OFFSET expressions are non-deterministic. Two subsequent executions of the same query might produce different results. But then … Continue reading SQL Server…
12 May 2014
Recently, at the office: Bob: I’ve looked into that slow query you’ve told me about yesterday, Alice. I’ve added the indexes you wanted. Everything should be fine now Alice: Thanks Bob. I’ll quickly check … Nope Bob, still slow, it didn’t seem to work Bob: You’re right Alice! It looks like Oracle isn’t picking up … Continue reading The Index…
7 May 2014
I’ve recently stumbled upon this interesting Stack Overflow question, where the user essentially wanted to ensure that resulting records are delivered in a well-defined order. They wrote They got CE367FAACDHCANPH-151556 CE367FAACEX9ANPH-153877 GE526OTACCD3ANPH-149839 NI564FAACJSFANPH-162605 They wanted CE367FAACDHCANPH-151556 CE367FAACEX9ANPH-153877 NI564FAACJSFANPH-162605 GE526OTACCD3ANPH-149839 Very often, according to your business rules, sorting orders are not “natural”, as in numeric sortin
29 Apr 2014
If anything at all, our jOOQ talks at various JUGs and conferences have revealed mostly one thing: Java developers don’t know SQL. And it isn’t even necessarily our fault. We’re just not exposed to SQL nowadays. But consider this: We developers (or our customers) are paying millions of dollars every year to Oracle, Microsoft, IBM, … Continue reading NoSQL? No,…
15 Apr 2014
Haven’t we all been wondering: How can I do this? I have these data in Excel and I want to group / sort / assign / combine … While you could probably pull up a Visual Basic script doing the work or export the data to Java or any other procedural language of choice, why … Continue reading How can…
24 Mar 2014
When doing reporting or statistics with SQL, you better know your window functions. There are many of them, and few SQL developers know about them. CUME_DIST() is one such function. We’ve recently re-discovered it on Stack Overflow. The following query yields two times the same result for fraction1 and fraction2: The above query then yields: … Continue reading CUME_DIST(), a…
21 Mar 2014
At Data Geekery, we love Java. And as we’re really into jOOQ’s fluent API and query DSL, we’re absolutely thrilled about what Java 8 will bring to our ecosystem. For our Java 8 series, we’re honoured to host a very relevant guest post by Dr. Ming-Yee Iu. Dr. Ming-Yee Iu completed a PhD on Database … Continue reading Java 8…
19 Mar 2014
Leland Richardson, Founder of Tech.Pro has recently published a very interesting article about BQL, his visions of a better query language (than SQL). The deciding feat of his new language proposal is the fact that it is really a superset of SQL itself. SQL is a very rich and expressive language to query relational databases. … Continue reading A Better…
17 Mar 2014
There are a tremendous amount of SQL APIs natively written in Scala. Manuel Bernhardt has summarised a nice collection in his a post. Another collection of Scala SQL APIs can be seen in this Stack Overflow question. One API that we want to focus on in particular is ScalikeJDBC (licensed ASL 2.0), which has recently … Continue reading A SQL…
25 Feb 2014
Interestingly, boolean types have been introduced only late in the SQL standard, namely in SQL:1999. Even today, not all databases natively support BOOLEAN or BIT types. Most importantly, we can still wait for them in Oracle for a while. Here’s “Ask Tom”‘s point of view from 2002 on the subject: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6263249199595 Oracle. Why U No … Continue reading The JDBC…
19 Feb 2014
Recently, we’ve added support for the MS Access database in jOOQ 3.3. This may well have been our hardest integration so far. The MS Access database has its ways, and many of them, too. But luckily, jOOQ’s internal SQL transformation abilities are already very advanced, as we’ve shown previously in a blog post about the … Continue reading An MS…