SQL Joins

1. What is JOIN?

A JOIN clause is used to retrieve data from multiple tables by establishing a relationship between them using keys.

• Combines data from multiple tables
• Uses primary key and foreign key
• Improves data analysis

2. Types of SQL Joins

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• SELF JOIN

3. INNER JOIN

Returns records that have matching values in both tables.

SELECT student.Name, course.CourseName
FROM student
INNER JOIN course
ON student.CourseID = course.CourseID;

Table A ∩ Table B  →  Matching records only

4. LEFT JOIN

Returns all records from the left table and matching records from the right table. Non-matching rows return NULL.

SELECT student.Name, course.CourseName
FROM student
LEFT JOIN course
ON student.CourseID = course.CourseID;

Table A ⟕ Table B  →  All from A + matches from B

5. RIGHT JOIN

Returns all records from the right table and matching records from the left table.

SELECT student.Name, course.CourseName
FROM student
RIGHT JOIN course
ON student.CourseID = course.CourseID;

Table A ⟖ Table B  →  All from B + matches from A

6. SELF JOIN

A SELF JOIN is a join where a table is joined with itself. It is useful for hierarchical data.

SELECT e1.Name AS Employee, e2.Name AS Manager
FROM employee e1
INNER JOIN employee e2
ON e1.ManagerID = e2.EmpID;

7. Example Tables

STUDENT                    COURSE
-----------                ------------
RollNo (PK)                CourseID (PK)
Name                        CourseName
CourseID (FK)

8. Comparison of SQL Joins

Join Type     Description
------------ ------------------------------------------
INNER JOIN   Matching rows in both tables
LEFT JOIN    All left + matched right rows
RIGHT JOIN   All right + matched left rows
SELF JOIN    Table joined with itself

Practice Questions

1. What is JOIN in SQL?
2. Explain INNER JOIN with example.
3. Difference between LEFT and RIGHT JOIN.
4. What is SELF JOIN?
5. When is JOIN used?

Practice Task