Thread by @Sukriti_Macker

Have you found the concepts of JOINS in SQL tricky? 😵‍💫

A Beginner-Friendly JOINS guide that would help you in your SQL & Data Science Journey.

Thread 🧵

☑️ JOINS (Some Theory)
Using JOIN, we can query data from two or more tables based on the related column present in both tables.

While performing a join, we need to specify the shared column & the condition on which we want to join tables.

☑️ Types of JOINS:-
1. Inner Join
2. Left Join
3. Right Join
4. Full Join

☑️ Inner Join
This join query would return rows when there is AT LEAST ONE ROW in both the tables that match the specified join condition.

👉 Query: select tablename1.col1, tablename2.col2 from tablename1 INNER JOIN tablename2 ON tablename1.col1 = tablename2.col2

In the above query,

1. tablename1.col1 means the column1 in tablename1

2. We have to mention after the SELECT keyword which columns we want to apply Join Function on.

3. tablename1.col1 = tablename2.col2 is the condition using which we want our INNER JOIN to consider.

Consider an example of 2 tables having 2 columns each, namely ID and Name.

See the tables in the figure below:-

We perform a query to find the inner join.

Query -> select * from table1 inner join table2 on table1.name = table2.ID

i.e. selecting all the columns from table1 & table 2 to perform an inner join on the condn that name col of table1 = ID col of table2

Look at the following explanation for the above query performed for more clarity:-

See Image

☑️ Left Join
Using LEFT JOIN, the query returns all the rows from the left table and only those rows from the right table where the join condition is matched.

Query: select tablename1.col1, tablename2.col2 from tablename1 LEFT JOIN tablename2 ON tablename1.col1 = tablename2.col2

Example: Consider the same 2 tables we considered for the inner join.

Query: select * from table1 LEFT JOIN table2 on table1.name = table2.ID

Observe the results in the image below.

Notice that the left table, i.e. the table 1, is entirely printed, and only those values of table 2 (the right side table) are printed where the join condition is met.

☑️ Right Join
It functions similar to the left join, but the only difference is that it returns all the rows from the RIGHT table and only those rows from the left table where the join condition is matched.

Query: select * from tablename1 RIGHT JOIN tablename2 ON tablename1.col1 = tablename2.col2

Try to implement this one yourself. :)

☑️ Full Join
It returns all rows when there is a match in either the left or the right table.