Implementing Semi, Anti Joins in MySQL

Mahesh Dhiman
Feb 14 · 3 min read

SQL Joins are used to combine and retrieve data from multiple tables in an RDBMS. Most of the business objectives can be achieved using primitive Joins (Inner, Left, Right, Outer, Cross), however sometimes we come across scenarios where some complex operations are needed with joins, like combine the data from multiple tables but retrieve columns from just one table. In such scenarios, Semi and Anti joins come to rescue.


Left Semi Join means a Half Join that returns records from the left table, when matching records are found in the right table. Even if more than one match is found in the right table for a given record, the result contains that record just once.

Let’s understand this concept with the help of an example.

There are two tables: tblTeamA and tblTeamB that contain records from a pool of players with their Name, Skill and Age. A player can be choosen for both the teams.

CREATE TABLE tblTeamA (NAME VARCHAR(20) NOT NULL, SKILL VARCHAR(20) NOT NULL, AGE int(3) NOT NULL);
CREATE TABLE tblTeamA (NAME VARCHAR(20) NOT NULL, SKILL VARCHAR(20) NOT NULL, AGE int(3) NOT NULL);
Creating tables
Populating tblTeamA
Populating tblTeamB

Following are few use-cases:

1. Find the players who are selected for both the teams.
2. Find the players who are selected just for Team A.
3. Find the players who are selected just for Team B.

In addition, the result shall contain a player just once, there is no point to list the same player twice (rows from both the tables).

1. Find the players who are selected for both the teams.

The EXISTS clause in the above query, is used to implement the Left Semi Join. The query compares records from left table with records in right table. If a match is found, the rows from left table are returned.

2. Find the players who are selected just for Team A.

Using NOT with EXISTS clause excludes the common records in both the tables and hence return the unique records from left table that do not exists in right table.

3. Find the players who are selected just for Team B.

Anti Join is also used to find records in one table that are unavailable in other table. Here a RIGHT JOIN is combined with WHERE clause to implement the anti join.

Summary

Left Semi or Left Anti might sound fancy, however their implementation is not that complex. Unlike primitive joins, a semi or anti join does not have their own syntax but can be implemented by combination of basic join(s) and some clauses.

Here, I attempted to keep it simple and would love to hear your opinion.

Thanks for reading!

Analytics Vidhya

Mahesh Dhiman

Written by

Data Science Enthusiast

Analytics Vidhya

Analytics Vidhya is a community of Analytics and Data Science professionals. We are building the next-gen data science ecosystem https://www.analyticsvidhya.com

More From Medium

More from Analytics Vidhya

More from Analytics Vidhya

More from Analytics Vidhya

Get More Out of Google Colab

More from Analytics Vidhya

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade