JOIN in SQL - Rajanand

A JOIN operator in SQL is used to combine rows from two or more tables based on a related column between them. It allows you to retrieve data from multiple tables in a single query, making it a powerful tool for working with relational databases.

Key Points

Combining Tables: JOIN combines rows from two or more tables based on a common column.
Types of JOINs: The most common types of JOIN are:
- INNER JOIN: Returns only matching rows.
- LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right table.
- RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and matching rows from the left table.
- FULL JOIN (or FULL OUTER JOIN): Returns all rows when there is a match in either table.
- CROSS JOIN: Returns the Cartesian product of the two tables (all possible combinations).
Alias Support: You can use table aliases to simplify queries.

Syntax

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

column1, column2, ...: The columns you want to retrieve.
table1, table2: The tables you want to join.
common_column: The column that relates the two tables.

Examples

INNER JOIN

Suppose you have two tables: Employees and Departments. Table: Employees

EmployeeID	Name	City	DepartmentID
1	Anand	Chennai	101
2	Bala	Coimbatore	102
3	Kavitha	Karaikal	103
4	Raj	Madurai	104
5	Kumar	Trichy	105

Table: Departments

DepartmentID	DepartmentName
101	HR
102	Finance
103	IT
104	Marketing
105	Sales

To retrieve employee names along with their department names:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Name	DepartmentName
Anand	HR
Bala	Finance
Kavitha	IT
Raj	Marketing
Kumar	Sales

LEFT JOIN

A LEFT JOIN returns all rows from the left table (Employees) and matching rows from the right table (Departments). If there is no match, NULL values are returned for columns from the right table.

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Name	DepartmentName
Anand	HR
Bala	Finance
Kavitha	IT
Raj	Marketing
Kumar	Sales

If there were employees without a department, their DepartmentName would appear as NULL.

RIGHT JOIN

A RIGHT JOIN returns all rows from the right table (Departments) and matching rows from the left table (Employees). If there is no match, NULL values are returned for columns from the left table.

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Name	DepartmentName
Anand	HR
Bala	Finance
Kavitha	IT
Raj	Marketing
Kumar	Sales

If there were departments without employees, the Name column would appear as NULL.

FULL JOIN

A FULL JOIN returns all rows when there is a match in either table. If there is no match, NULL values are returned for columns from the table without a match. Query:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
FULL JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Name	DepartmentName
Anand	HR
Bala	Finance
Kavitha	IT
Raj	Marketing
Kumar	Sales

If there were employees without departments or departments without employees, the missing values would appear as NULL.

CROSS JOIN

A CROSS JOIN returns the Cartesian product of the two tables, meaning it combines each row of the first table with each row of the second table.

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
CROSS JOIN Departments;

Name	DepartmentName
Anand	HR
Anand	Finance
Anand	IT
Anand	Marketing
Anand	Sales
Bala	HR
Bala	Finance
Bala	IT
Bala	Marketing
Bala	Sales
Kavitha	HR
Kavitha	Finance
Kavitha	IT
Kavitha	Marketing
Kavitha	Sales
Raj	HR
Raj	Finance
Raj	IT
Raj	Marketing
Raj	Sales
Kumar	HR
Kumar	Finance
Kumar	IT
Kumar	Marketing
Kumar	Sales

Practical Use Case

Suppose you have a table named Students and another table named Courses. Table: Students

StudentID	Name	City
1	Ram	Chennai
2	Karthik	Coimbatore
3	David	Bangalore

Table: Courses

CourseID	CourseName
101	Mathematics
102	Science
103	English

To retrieve all possible combinations of students and courses:

SELECT Students.Name, Courses.CourseName
FROM Students
CROSS JOIN Courses;

Name	CourseName
Ram	Mathematics
Ram	Science
Ram	English
Karthik	Mathematics
Karthik	Science
Karthik	English
David	Mathematics
David	Science
David	English

Key Takeaways

JOIN combines rows from two or more tables based on a related column.
Common types of JOIN include INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and CROSS JOIN.
INNER JOIN returns only matching rows, while LEFT JOIN and RIGHT JOIN include non-matching rows with NULL values.
CROSS JOIN returns the Cartesian product of the two tables.
Using JOIN allows you to retrieve and analyze data from multiple tables in a single query.

​Key Points

​Syntax

​Examples

​INNER JOIN

​LEFT JOIN

​RIGHT JOIN

​FULL JOIN

​CROSS JOIN

​Practical Use Case

​Key Takeaways