Understanding SQL Joins

SQL Joins combine data from multiple tables using common columns. This helps reduce data redundancy and promotes better organization.

Example:

Let’s say we have two tables: students and courses.

• students table: student_id, student_name, course_id
• courses table: course_id, course_name

The course_id in students is a foreign key linking to the courses table.

SQL Syntax:

SELECT students.student_name, courses.course_name
FROM students
JOIN courses ON students.course_id = courses.course_id;

This query lists student names along with their enrolled course names.

Types of SQL Joins

1. Inner Join

• Combines rows from two tables where the join condition is met.
• Only returns matching rows from both tables.
• Ideal for fetching related data from both tables.

Syntax:

SELECT table1.column1, table2.column2
FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_column;

2. Left Join (Left Outer Join)

• Combines rows from two tables, returning all rows from the left table and matching rows from the right table.
• Non-matching rows from the right table will have NULL values.
• Ideal for fetching all records from the left table, regardless of matching records in the right table.

Syntax:

SELECT table1.column1, table2.column2
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;

3. Right Join (Right Outer Join)

• Combines rows from two tables, returning all rows from the right table and matching rows from the left table.
• Non-matching rows from the left table will have NULL values.
• Ideal for fetching all records from the right table, regardless of matching records in the left table.

Syntax:

SELECT table1.column1, table2.column2
FROM table1
RIGHT JOIN table2 ON table1.common_column = table2.common_column;

4. Full Join

– Combines rows from two tables, returning all matching and non-matching rows.
– Non-matching rows from either table will have NULL values.
– Ideal for fetching all records from both tables, including those without matches.

Syntax:

SELECT table1.column1, table2.column2
FROM table1
FULL JOIN table2 ON table1.common_column = table2.common_column;

5. Cross Join

• Combines rows from two tables by pairing every row from the first table with every row from the second table.
• Returns the Cartesian product of the tables.
• Ideal for scenarios where all combinations of rows are needed.

6. Self Join

• Joins a table with itself to compare rows within the same table.
• Requires an alias to differentiate the table.
• Ideal for hierarchical or self-referential data.

Syntax:

SELECT a.column1, b.column2
FROM table_name a
JOIN table_name b ON a.common_column = b.common_column;