Demystifing Joins,Unions/Intersect with Python & TSQL.
Apologies for using the Venn diagram at times to explain the joins. Reason to apologize is its simply incorrect and insufficient to use Venn diagrams for JOINS. But its easy to symbolically represent the JOIN condition with Venn than elaborately showing it otherwise. Ideally, Venn diagram explains the set operations aptly.
Take away from this blog:
1. We need to understand the difference between the Operations (like UNION, EXCEPT & INTERSECT) and the JOIN conditions.
2. Many different kinds of JOINS (which are subset of CROSS JOIN).
3. How to use TSQL command and Python to perform such JOINs.
1. Left Join & Right Join
2. Full Join as a Union of Right & Left Join
3. Semi Join & Anti Join
4. Inner Join & Non-equi inner join
Note: In the examples, the fk column of Table 1 is mapped with id column of Table 2.
Note: The images are attached here.
Full Outer Join
Full Outer Join (aka Full Join) can be seen as UNION of Left & Right Join.
Union keeps only non-duplicate rows.
Full Outer Join
Left Join (also known as Left Outer Join) :
1. All rows from the left table.
2. The rows from the right table that match the join condition.
All the rows which doesn’t have a match would be returned as empty.
Note: the same analogy goes for Right Join.
Inner Join
Inner Join would return the records from both the tables that has a match on the JOIN condition.
Inner Non-equi Join return records based on a condition (Ex: fk >= id) between the two tables.
SEMI JOIN & ANTI JOIN:
SEMI JOIN is meant to find the rows of the left table which has match with right table.
ANTI JOIN is meant to find the rows of the left table which has no match with right table.
Some more less frequently used JOINS are LEFT JOIN WITH EXCLUSION, RIGHT JOIN WITH EXCLUSION.
Refer here to see the syntax in TSQL and Python.
Authors :
1. Piyush Kulkarni, Data Scientist, Germany.
2. Bhuvnesh Sharma
References
https://mode.com/blog/set-operations-python-sql/
https://www.codegrepper.com/code-examples/python/join+three+tables+pandas
https://stackoverflow.com/questions/32676027/how-to-do-df1-not-df2-dataframe-merge-in-pandas
https://www.linkedin.com/posts/activity-6823535807211692032-HAHP
https://docs.microsoft.com/en-us/power-query/merge-queries-left-anti
