What is Join?

A JOIN is a single optimized query handled by the DBMS, reducing network roundtrips and leveraging indexes.
It matches rows from different tables using a common column (like CustomerID) that shares related data, as seen in Orders and Customers tables.
Advantages:
  1. Reduces the need for multiple, separate queries to different tables, improving performance and lowering server load.

How join is internally implemented

How join is different from I doing query to table-1 storing data and then doing query to table-2?
JOINs are implemented by the database engine (e.g., via nested loops, hash joins, or merge joins). It scans indexes or tables, matches rows on the join condition, and returns combined results.
No separate handles are opened per table; the optimizer plans a single execution path, often using memory or temp storage for efficiency.

Kinds of Joins

Lets consider we have 2 tables(employee and sales)

Employee-Table          Departments-Table
ID      Name            emp-id      dept
1       Alice           1           HR
2       Bob             2           IT
3       Charlie         4           Sale
Type Description Example
INNER JOIN Returns matching rows from both tables Inner join (on empid)

ID      Name               dept
1       Alice              HR
2       Bob                IT
LEFT JOIN (or LEFT OUTER JOIN) Returns all rows from left table, matching from right; NULLs for non-matches. LEFT JOIN on ID=EmpID

ID      Name               dept
1       Alice              HR
2       Bob                IT
3       Charlie            null
RIGHT JOIN (or RIGHT OUTER JOIN) Returns all rows from right table, matching from left; NULLs for non-matches. Right JOIN on ID=EmpID

ID      Name               dept
1       Alice              HR
2       Bob                IT
4       null               Sales
FULL OUTER JOIN Returns all rows from both tables; NULLs for non-matches. FULL OUTER JOIN on ID=EmpID

ID      Name               dept
1       Alice              HR
2       Bob                IT
3       Charlie            null
4       null               Sales
CROSS JOIN Cartesian product; all combinations without condition Returns 9 rows (3x3 combinations), e.g., Alice-HR, Alice-IT, Alice-Sales, etc.