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 MultipleBagFetchException Thanks to Multiset Nested Collections
Tag: JOIN
The Difference Between SQL’s JOIN .. ON Clause and the Where Clause
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 Between SQL’s JOIN .. ON Clause and the Where Clause
Faster SQL Through Occasionally Choosing Natural Keys Over Surrogate Keys
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 thus … Continue reading Faster SQL Through Occasionally Choosing Natural Keys Over Surrogate Keys
A Probably Incomplete, Comprehensive Guide to the Many Different Ways to JOIN Tables in SQL
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 Incomplete, Comprehensive Guide to the Many Different Ways to JOIN Tables in SQL
You Probably don’t Use SQL INTERSECT or EXCEPT Often Enough
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 don’t Use SQL INTERSECT or EXCEPT Often Enough
No CROSS JOIN in MS Access
For the upcoming jOOQ 3.3, we're now integrating support for the MS Access database through the JDBC-ODBC bridge, which is included in the JDK up until Java SE 7. Note that it will be removed in Java 8! Alternative access to access databases (pun intended) can be obtained through a hack involving ucanaccess, which is basically combining the … Continue reading No CROSS JOIN in MS Access
The Myth About Slow SQL JOIN Operations
In my recent SQL work for a large Swiss bank, I have maintained nested database view monsters whose unnested SQL code amounted up to 5k lines of code, joining the same table over and over again in separate subselects combined via UNION operations. This monster performed in way under 50ms, no matter how we queried … Continue reading The Myth About Slow SQL JOIN Operations
You never stop learning about Oracle features
Oracle's name is no coincidence. It is truly an oracle, telling you mystical, secret things about your database. It may take great expertise and skill to optimally extract that knowledge from this monster. While it implements vast parts of the SQL:2008 standard, it ships with a lot of features no other database has, but which … Continue reading You never stop learning about Oracle features
Let’s revise the SQL FROM clause
Intuitive SQL SQL is extremely simple and yet at times, tricky. Most SQL developers have an intuitive (as opposed to formal) understanding of how the language works, for two reasons: It is designed "intuitively", like a natural language. Maybe that keeps us from studying it more formally The formal language specification is not really freely … Continue reading Let’s revise the SQL FROM clause