Hello everyone. I’m writing this post as a part of my journey with MySQL and since joins is a confusing thing in the SQL, I’m explaining this by simplest terms as possible. I’m taking data from a schema and posting the code here. We will be going through each and every joins in MySQL and let’s hop in. First, let’s see what joins is all about and then types of joins and their explanations with examples.

Note : I have outsourced some data and values from Sakila dataset and also some definitions from various websites for easy understanding.

---

MySQL join :

• MySQL join is a function that is join two or more tables.
• As we handle many kinds of data in future, we are gonna link many tables
• These tables are linked by columns which are common between tables
• In case of not common columns, a different kind of join is used
• Sometimes, same table is joined or precisely compared to get values

Types of MySQL join :

Imagine Venn diagrams in your mind so that this will be very easy to grasp. A little bit of tenth standard and 11th standard will surely help. Let’s refresh our knowledge in Venn a bit. Venn diagrams are fun and in our boards they help us to get more marks. In this, we will look forward using those here.

Venn functions — [union, intersection, set difference]

• Inner Join — Works like intersection
• Right Join — Set difference (B-A)
• Left Join — Set difference (A-B)
• Outer Join — Union
• Cross join : This sorta works like nested for loop (ref python)

---

Inner join : Inner join is basically a join that includes the common columns in both the tables. This works like a sort of intersection in our mathematics. In intersection, the A intersection B, the result takes the common value between A and B. Similarly, in inner join or common join (ms access there is only 3 types of join) — the join happens between common columns. Let’s jump into the syntax :

Select table1.col1, table1.col2, table2.col1, table2.col2 from table1 JOIN table2 on common col of table1 = common col of table2;

Note : we can use the groupby and having and orderby and where clauses in this syntax.

Breaking down the syntax :

_Select _— the common select syntax

table1.col1, table1.col2, table2.col1, table2.col2— here table1,table2 denotes where the column is taken from

Join_ : Here the join denotes the join function indicating that a join has happened here_

_on _— On statement is used to denote on which column the table is joined

**Example **: Use sakila schema in the MySQL

Highlighted portion shows where the join has happened

We are gonna find the full name and the address of the customers by joining customer and address table as c and ad respectively.

Result :

We can see full_name and full address of the person

We can also do multiple inner joins on various tables to get the desired output.

**Using multiple tables : **Use the Sakila schema

Here we have joined the customer, address, city and country

We are gonna get the full_name, address and the country_name from the tables customer, address, city, country

**Result **:

We get the country name in the table

Thus by this way we can also join multiple tables and we can include various statements and execute our block. Make sure that we join on the correct columns — to avoid error.

#analytics-vidhya #data-science #dbms #database #mysql