JOIN in SQL

Documentation Index

Fetch the complete documentation index at: https://rajanand.org/llms.txt

Use this file to discover all available pages before exploring further.

​

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

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

​

LEFT JOIN

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

​

RIGHT JOIN

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

​

FULL JOIN

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

​

CROSS JOIN

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

​

Practical Use Case

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.