Skip to content

Day 5: Advance SQL For Data Science

 Becoming Human: Artificial Intelligence Magazine – Medium This blog contains type of joins like Inner join, Left join, Right join , Full join, Self join and Cross join.A JOIN clause is used to combine rows from two or more tables, based on a related column between them.1. Self JoinA self-join is a regular join, in which the table joins itself.Syntax of self-join:SELECT column_name(s) from table1 T1, table1 T2WHERE condition;Query: select T1.user_id , T1.name, T2.user_id, T2.name from user T1, user T2;Self JoinNOTE: T1 and T2 are different table aliases for the same table.2. Inner JoinThe INNER JOIN keyword selects records that have matching values in both tables.Syntax of Inner join :SELECT column_name(s) FROM table1INNER JOIN table2 ON table1.column_name = table2.column_name;Query: select * from user uinner join Guest G on G.Guest_user_id = u.user_id;Inner join provides only rows in which data is matched on both sides of the table.3. Left JoinThe LEFT JOIN keyword returns all records from the left table, and the matched records from the right table.Syntax of Left-join:SELECT column_name(s) FROM table1LEFT JOIN table2 ON table1.column_name = table2.column_name;Query: select * from user u left join Guest G on G.Guest_user_id = u.user_id;In Left join, we are applying left join to the user table which is mentioned first (In Query). It will return all the rows from table 1 i.e. user table and only matched rows from table 2 i.e. Guest table.4. Right JoinThe RIGHT JOIN keyword returns all records from the right table and the matched records from the left table.Syntax of Right-join:SELECT column_name(s) FROM table1RIGHT JOIN table2 ON table1.column_name = table2.column_name;Query: select * from user uright join Guest G on G.Guest_user_id = u.user_id;In right join, it returns all the rows from the right table i.e. Guest table and only matched rows from the first table i.e. user table.5. Full JoinThe Full JOIN keyword returns all records when there is a match in either the left or the right table.Syntax of full join:SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;6. Cross JoinThe CROSS JOIN keyword returns all records from both tables (table1 and table2).Syntax of Cross-join:SELECT column_name(s) FROM table1CROSS JOIN table2;Query: select * from user u cross join Guest GNote: On clause is optional in Self and cross join.JOINS with Aggregate/Ranking FunctionsWe can use the Aggregate, ranking and etc functions with joins and can manipulate queries as we want.Here is a short example of left join with group by and having clause.Query: select user_id, u.name, min(u.age) from user uleft join Guest G on G.Guest_user_id = u.user_id Group by user_id, u.name having min(G.age) <= 30;Thank you for reading it and liking it.=============================THE END==========================GitHub: Day 5 SessionPlease give it a star on Git Hub!!Reference :https://www.w3schools.com/sql/default.asphttps://www.geeksforgeeks.org/Hope you found it helpful! Thanks for reading!Follow me for more Data Science related posts!Let’s connect on LinkedIn!Day 5: Advance SQL For Data Science was originally published in Becoming Human: Artificial Intelligence Magazine on Medium, where people are continuing the conversation by highlighting and responding to this story. Read More 

Leave a Reply

Your email address will not be published. Required fields are marked *