Scribd
Upload
12 views3 pages

Join Operators Notes

The document provides an overview of various SQL JOIN operators used to combine rows from multiple tables based on related columns. It explains INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN, SELF JOIN, and NATURAL JOIN, along with examples and their outputs. Additionally, it includes a comparison table and applications for each type of JOIN.

Uploaded by

tommyathish
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
12 views3 pages

Join Operators Notes

The document provides an overview of various SQL JOIN operators used to combine rows from multiple tables based on related columns. It explains INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN, SELF JOIN, and NATURAL JOIN, along with examples and their outputs. Additionally, it includes a comparison table and applications for each type of JOIN.

Uploaded by

tommyathish
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

■ Join Operators – Notes

Introduction
A JOIN in SQL is used to combine rows from two or more tables based on a related column. It
retrieves meaningful data spread across multiple relations and is equivalent to relational algebra
join (■).

INNER JOIN
Returns rows where there is a match in both tables.
Example:
SELECT s.name, c.course_name
FROM students s
INNER JOIN courses c ON s.course_id = c.course_id;

Output: Only students enrolled in existing courses.

LEFT JOIN (LEFT OUTER JOIN)


Returns all rows from the left table and matched rows from the right. If no match, NULL values
appear.
Example:
SELECT s.name, c.course_name
FROM students s
LEFT JOIN courses c ON s.course_id = c.course_id;

Output: All students, even those without a course.

RIGHT JOIN (RIGHT OUTER JOIN)


Returns all rows from the right table and matched rows from the left. If no match, NULL values
appear.
Example:
SELECT s.name, c.course_name
FROM students s
RIGHT JOIN courses c ON s.course_id = c.course_id;

Output: All courses, even those with no students.

FULL JOIN (FULL OUTER JOIN)


Returns all rows from both tables. Non-matching rows are filled with NULLs.
Example:
SELECT s.name, c.course_name
FROM students s
FULL JOIN courses c ON s.course_id = c.course_id;
Output: All students + all courses.

CROSS JOIN
Returns the Cartesian product (all combinations).
Example:
SELECT s.name, c.course_name
FROM students s
CROSS JOIN courses c;

Output: Each student paired with each course.

SELF JOIN
A table joined with itself using aliases. Useful for hierarchical data like employees and managers.
Example:
SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.id;

NATURAL JOIN
Automatically joins based on columns with the same name and compatible type.
Example:
SELECT * FROM students NATURAL JOIN courses;

Comparison Table
INNER JOIN: Only matching rows.
LEFT JOIN: All left rows + matches.
RIGHT JOIN: All right rows + matches.
FULL JOIN: All rows from both tables.
CROSS JOIN: Cartesian product.
SELF JOIN: Table joined with itself.
NATURAL JOIN: Auto-join on common attributes.

Applications
• INNER JOIN – common data analysis.
• OUTER JOIN – include unmatched data for reporting.
• CROSS JOIN – test combinations.
• SELF JOIN – employee-manager hierarchy.
• NATURAL JOIN – shorthand for automatic joins.

Quick Recap
✔ INNER JOIN: Only matches.
✔ LEFT JOIN: All left + matches.
✔ RIGHT JOIN: All right + matches.
✔ FULL JOIN: All rows.
✔ CROSS JOIN: Cartesian product.
✔ SELF JOIN: Table with itself.
✔ NATURAL JOIN: Auto-join.

You might also like