SQL JOIN

← Previous Next →

SQL Joins in SQL are used to combine rows from two or more tables based on a related column between them (usually a primary key and foreign key).
Joins help retrieve data from multiple tables in a single query.

Types of SQL Joins:
1. INNER JOIN
2. LEFT JOIN (LEFT OUTER JOIN)
3. RIGHT JOIN (RIGHT OUTER JOIN)
4. FULL JOIN (FULL OUTER JOIN)
5. CROSS JOIN

INNER JOIN: Returns only matching records from both tables. 

SELECT students.name, marks.marks
FROM students
INNER JOIN marks
ON students.student_id = marks.student_id;

Explanation:
Only students whose IDs exist in both tables appear.

LEFT JOIN (LEFT OUTER JOIN): Returns all records from the left table and matching records from the right table. 

SELECT students.name, marks.marks
FROM students
LEFT JOIN marks
ON students.student_id = marks.student_id;

Explanation:
All students appear, even if they don’t have marks.

RIGHT JOIN (RIGHT OUTER JOIN): Returns all records from the right table and matching records from the left table.

SELECT students.name, marks.marks
FROM students
RIGHT JOIN marks
ON students.student_id = marks.student_id;

Explanation:
Marks exist for student_id 4, but that student is not in the students table.

FULL JOIN (FULL OUTER JOIN): Returns all records from both tables. Unmatched values show NULL.

SELECT students.name, marks.marks
FROM students
FULL JOIN marks
ON students.student_id = marks.student_id;

CROSS JOIN: Returns all possible combinations of rows from both tables.

SELECT students.name, marks.marks
FROM students
CROSS JOIN marks;

Explanation:
If there are 3 students and 3 marks, result = 9 rows.


Quick Summary:
Join Type -- Result
----------------------------------------
INNER JOIN -- Only matching rows
LEFT JOIN -- All left + matching right
RIGHT JOIN -- All right + matching left
FULL JOIN -- All rows from both tables
CROSS JOIN -- All combinations

← Previous Next →
Topics