Posted on

Understanding Hash Joins in MySQL 8

In MySQL 8.0.18 there is a new feature called Hash Joins, and I wanted to see how it works, and in which situations it can help us. Here, you can find a nice detailed explanation about how it works under the hood.

The high-level basics are the following: if there is a join, it will create an in-memory hash table based on one of the tables and will read the other table row by row, calculate a hash, and do a lookup on the in-memory hash table.

Posted on

Database Fundamentals #22: Using the Join Operator, CROSS JOIN

CROSS JOIN

While the CROSS JOIN is not used much, and, depending on the size of your data it can be dangerous, there are some uses for it. For example, you may want to write a query that will summarize all the sales for the companies and people in your system. You can do this using what is called an aggregate operation or a GROUP BY:

SELECT c.CompanyName,
p.LastName,
SUM(ft.TransactionAmount) AS 'TransactionTotals'
FROM Management.Company AS c
JOIN Finance.FinancialTransaction AS ft
ON c.CompanyID = ft.CompanyID
JOIN Personnel.Person AS p
ON p.PersonID = ft.PersonID
GROUP BY c.CompanyName, p.LastName;
You might also like:  Overview of SQL Joins in SQL Server

This will add all the values up in the SUM operation for each company and each person that has values so that your data will look like this: