SQL Joins

⏱ Estimated reading time: 3 min

SQL JOIN is a powerful feature used to combine rows from two or more tables based on a related column between them. Joins help retrieve meaningful data from multiple tables by establishing relationships using primary key – foreign key connections.

Joins are essential in relational databases because data is stored in separate normalized tables, and queries often require combining these tables to produce meaningful results.

Types of SQL Joins

SQL supports several types of joins:

1. INNER JOIN

2. LEFT OUTER JOIN

3. RIGHT OUTER JOIN

4. FULL OUTER JOIN

5. CROSS JOIN

6. SELF JOIN

Let’s understand each in detail with examples.

1️⃣ INNER JOIN

Returns only the matching rows from both tables.

Syntax

SELECT columns
FROM table1
INNER JOIN table2
ON table1.col = table2.col;

Example

Student Table

Course Table

Query

SELECT Student.Name, Course.Course
FROM Student
INNER JOIN Course
ON Student.Sid = Course.Sid;

Output

Only matching rows are returned.

2️⃣ LEFT OUTER JOIN (LEFT JOIN)

Returns all rows from the left table, and matched rows from the right table.
Non-matching rows from the right table become NULL.

Syntax

SELECT *
FROM table1
LEFT JOIN table2
ON table1.col = table2.col;

Output Example

From the previous tables:

Neha has no matching course, so NULL appears.

3️⃣ RIGHT OUTER JOIN (RIGHT JOIN)

Returns all rows from the right table, and matched rows from the left table.

Syntax

SELECT *
FROM table1
RIGHT JOIN table2
ON table1.col = table2.col;

Output Example

(Using Student and Course tables)

If any course exists without a student, it would appear with NULL values from Student.

4️⃣ FULL OUTER JOIN

Returns all rows from both tables, with NULL where no match exists.

(Some DBMS like MySQL don’t support FULL OUTER JOIN directly.)

Syntax

SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.col = table2.col;

Output Example

(This would happen if a course existed without a student.)

5️⃣ CROSS JOIN

Produces the Cartesian product of two tables.
Rows = rows in table1 × rows in table2.

Syntax

SELECT *
FROM table1
CROSS JOIN table2;

Example

If Student has 3 rows and Course has 2 rows → Output = 6 rows.

6️⃣ SELF JOIN

Self join means joining a table with itself.

Use Case

Finding employees and their managers in the same table.

Syntax

SELECT A.Name AS Employee, B.Name AS Manager
FROM Employee A
JOIN Employee B
ON A.ManagerID = B.EmpID;

Differences Between Joins

Conclusion

SQL Joins enable combining data from multiple related tables, making relational databases powerful and efficient. INNER JOIN is most commonly used, while LEFT JOIN is useful when unmatched rows must be included. FULL JOIN, CROSS JOIN, and SELF JOIN serve special use cases. Understanding joins is essential for writing complex SQL queries.