JOIN in SQL

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

1. Combining Tables: JOIN combines rows from two or more tables based on a common column.
2. 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).
3. 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

Table: Departments

To retrieve employee names along with their department names:

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

​

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;

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;

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:

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;

​

Practical Use Case

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

Table: Students

Table: Courses

To retrieve all possible combinations of students and courses:

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

​

Key Takeaways

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