SQL Joins Lecture Notes

Introduction

• Importance of joins in SQL
• Essential for real-time projects
• Data is often stored in multiple tables

What is a Join?

• Combines data from two or more tables
• Common columns required for combining tables
• Definition: Used to combine data based on related columns

Example Question

• Question: How much amount was paid by customer Madan and what was the payment date?
• Tables Involved: Customer table, Payment table
• Common Column: Customer ID

Types of Joins

• Inner Join: Returns only the matching rows between tables
• Left Join: Returns all rows from the left table and matching rows from the right table. Non-matching rows from the right table return null.
• Right Join: Returns all rows from the right table and matching rows from the left table. Non-matching rows from the left table return null.
• Full Join: Returns all rows when there is a match in either left or right table or both. Non-matching rows return null.

Inner Join Detailed

• Syntax: SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
• Example: Combining Customer and Payment tables on Customer ID
• Output: Common data from both tables

Left Join Detailed

• Syntax: SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
• Example: Keep all data from Customer table and matching data from Payment table
• Output: All rows from left table with matched rows from right table; unmatched rows from right table are null.

Right Join Detailed

• Syntax: SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
• Example: Keep all data from Payment table and matching data from Customer table
• Output: All rows from right table with matched rows from left table; unmatched rows from left table are null.

Full Join Detailed

• Syntax: SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
• Example: Combine all data from both tables, fill with null where there is no match
• Output: All rows from both tables; unmatched rows contain nulls.

Choosing the Right Join

• Inner Join: For matching data only
• Left Join: Keep all data from left table
• Right Join: Keep all data from right table
• Full Join: Keep all data from both tables

Summary Sheet

• Provides a quick reference for different types of joins and their syntax

Conclusion

• Joins are crucial for combining data from multiple tables
• Different types of joins serve different purposes depending on the use case
• Practice is essential for mastering joins