SQL CROSS APPLY – A Beginner’s Guide

Introduction In this article, we are going to see how the SQL CROSS APPLY works and how we can use it to cross-reference rows from a subquery with rows in the outer table and build compound result sets. CROSS APPLY is basically equivalent to the LATERAL JOIN, and it’s been supported by SQL Server since version 2005 and Oracle since version 12c.

Merge Join Algorithm

Introduction In this article, we are going to see how the Merge Join Algorithm, also known as Sort-Merge Join, works and when it’s suitable for a relational database system to employ it in order to execute an SQL JOIN query.

Hash Join Algorithm

Introduction In this article, we are going to see how the Hash Join Algorithm works and when it’s suitable for a relational database system to employ it in order to execute an SQL JOIN query.

Nested Loop Join Algorithm

Introduction In this article, we are going to see how the Nested Loop Join Algorithm works and when it’s suitable for a relational database system to employ it in order to execute an SQL JOIN query.

SQL LATERAL JOIN – A Beginner’s Guide

Introduction In this article, we are going to see how the SQL LATERAL JOIN works, and how we can use it to cross-reference rows from a subquery with rows in the outer table and build compound result sets. A LATERAL JOIN can be used either explicitly, as we will see in this article, or implicitly as it’s the case for the MySQL JSON_TABLE function.

SQL JOIN USING – A Beginner’s Guide

Introduction In this article, we are going to see how the JOIN USING clause works in SQL, and how we can use it to replace the ON condition clause when the columns used to join the two tables have the same name in both the parent and the child tables.

SQL LEFT JOIN – A Beginner’s Guide

Introduction In this article, we are going to see how LEFT JOIN works in SQL, and how we can use it to associate rows belonging to different tables and build compound result sets. Unlike JOIN or INNER JOIN, LEFT JOIN is an OUTER JOIN. So, LEFT JOIN and LEFT OUTER JOIN are equivalent. Other OUTER JOIN types are RIGHT OUTER JOIN and FULL OUTER JOIN. However, since the SQL OUTER keyword is superfluous, it’s usually omitted.

SQL INNER JOIN – A Beginner’s Guide

Introduction In this article, we are going to see how INNER JOIN works in SQL, and how we can use it to associate rows belonging to different tables and build compound result sets.

How to JOIN unrelated entities with JPA and Hibernate

Introduction Answering questions on the Hibernate forum is a neverending source of inspiration. The following question is about joining unrelated entities using entity queries, and this post is going to explain how you can do it when using JPA and Hibernate.

How to customize an entity association JOIN ON clause with Hibernate @JoinFormula

Introduction As I explained in this previous article, you can map calculated properties using Hibernate @Formula, and the value is generated at query time. In this post, you’ll see how you can use a custom SQL fragment to customize the JOIN relationship between two entities, or an entity and a collection of embeddable types.