SQL — Comprehensive guide for Cross Join||Self-Join||Inner Join||Intersect||Union||Union All

💻 Hi, I am a student from Data Science field. In this post, I would like to share what I’ve learned, while solving MySQL problems in LeetCode and HackerRank. I’ll cover the following topics in this post :

• Cross Join vs. Self-Join
  - Definition of Cross Join and Self-Join with codes
  - Comparison of Cross Join and Self-Join
• Inner Join vs. Intersect
  - Definition of Inner Join and Intersect with codes
  - Comparison of Inner Join and Intersect
• Union vs. Union All
  - Definition of Union and Union All with codes
  - Comparison of Union and Union All

Let’s dive in!

Image from Microsoft

Cross Join vs. Self-Join

Cross Join

Cross Join function is used to generate a paired combination of each row of the FIRST table with each row of the SECOND table. This join is also known as Cartesian Join. We can say Cross Join enables all possible combinations of each row from 2 tables.

Examples with Tables

Assuming that we have 2 tables as below and let’s say we want to combine them all by using Cross Join (Cartesian Join).

SELECT *
FROM A CROSS JOIN B

-- OR

SELECT *
FROM…