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 π§΅
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.
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
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
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.
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:-
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
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
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
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.
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.
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. :)
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.
It returns all rows when there is a match in either the left or the right table.
β’ β’ β’
Missing some Tweet in this thread? You can try to
force a refresh
γ
