Working on semi and anti joins

In this recipe, we will be discussing hash different joining methods.

Getting ready

Semi or anti joins are kind of sub join types to the joining methods such as hash, merge, and nested loop, where the optimizer prefers to use them for EXISTS/IN or NOT EXISTS/NOT IN operators.

Semi join will return a single value for all the matching records from the other table. That is, if the second table has multiple matching entries for the first table's record, then it will return only one copy from the first table. However, a normal join it will return multiple copies from the first table.

Anti-join will return rows, when no matching records are found in the second table. It is quite opposite to the semi join, since it is returning records from the first table, when there is no match in the second table.

How to do it…

Let's run a query in the benchmarksql database to get the list of items that are in stock:

benchmarksql=#
EXPLAIN SELECT * FROM bmsql_item WHERE EXISTS (SELECT...