Machine Learning News Hubb
Advertisement Banner
  • Home
  • Machine Learning
  • Artificial Intelligence
  • Big Data
  • Deep Learning
  • Edge AI
  • Neural Network
  • Contact Us
  • Home
  • Machine Learning
  • Artificial Intelligence
  • Big Data
  • Deep Learning
  • Edge AI
  • Neural Network
  • Contact Us
Machine Learning News Hubb
No Result
View All Result
Home Artificial Intelligence

Day 5: Advance SQL For Data Science | by Sunita Rawat | Jan, 2023

admin by admin
January 25, 2023
in Artificial Intelligence


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.

A self-join is a regular join, in which the table joins itself.

Syntax of self-join:

SELECT column_name(s) from table1 T1, table1 T2
WHERE condition;

Query: select T1.user_id , T1.name, T2.user_id, T2.name from user T1, user T2;

Self Join

NOTE: T1 and T2 are different table aliases for the same table.

The INNER JOIN keyword selects records that have matching values in both tables.

Syntax of Inner join :

SELECT column_name(s) FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

Query: select * from user u
inner 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.

The 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 table1
LEFT 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.

The 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 table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Query: select * from user u
right 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.

The 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;

The CROSS JOIN keyword returns all records from both tables (table1 and table2).

Syntax of Cross-join:

SELECT column_name(s) FROM table1
CROSS JOIN table2;

Query: select * from user u cross join Guest G

Note: On clause is optional in Self and cross join.

We 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 u
left 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 Session

Please give it a star on Git Hub!!

Reference :

  1. https://www.w3schools.com/sql/default.asp
  2. https://www.geeksforgeeks.org/

Hope you found it helpful! Thanks for reading!

Follow me for more Data Science related posts!

Let’s connect on LinkedIn!



Source link

Previous Post

A Pirate’s Guide to Accuracy, Precision, Recall, and Other Scores

Next Post

Build a loyalty points anomaly detector using Amazon Lookout for Metrics

Next Post

Build a loyalty points anomaly detector using Amazon Lookout for Metrics

What is Mortgage Origination?

Fine-tuning OpenAI GPT-3 to build Custom Chatbot | by Olasimbo Arigbabu | Jan, 2023

Related Post

Artificial Intelligence

3 Ways to Build a Geographical Map in Python Altair | by Angelica Lo Duca | Jan, 2023

by admin
January 30, 2023
Machine Learning

Want to get a quick and profound overview of the 42 most common used Machine Learning Algorithms? | by Murat Durmus (CEO @AISOMA_AG) | Jan, 2023

by admin
January 30, 2023
Machine Learning

Scan Business Cards to Excel or Google Contacts

by admin
January 30, 2023
Artificial Intelligence

Amazon SageMaker built-in LightGBM now offers distributed training using Dask

by admin
January 30, 2023
Artificial Intelligence

Don’t blame a Data Scientist on failed projects! | by Darya Petrashka | Dec, 2022

by admin
January 30, 2023
Edge AI

BrainChip Tapes Out AKD1500 Chip in GlobalFoundries 22nm FD SOI Process

by admin
January 30, 2023

© 2023 Machine Learning News Hubb All rights reserved.

Use of these names, logos, and brands does not imply endorsement unless specified. By using this site, you agree to the Privacy Policy and Terms & Conditions.

Navigate Site

  • Home
  • Machine Learning
  • Artificial Intelligence
  • Big Data
  • Deep Learning
  • Edge AI
  • Neural Network
  • Contact Us

Newsletter Sign Up.

No Result
View All Result
  • Home
  • Machine Learning
  • Artificial Intelligence
  • Big Data
  • Deep Learning
  • Edge AI
  • Neural Network
  • Contact Us

© 2023 JNews - Premium WordPress news & magazine theme by Jegtheme.