SQL Joins Explained

SQL joins are one of the most important DBMS and SQL interview topics because real databases almost never keep all data in one table. Instead, data is split into related tables, and joins are used to combine it.

What is a Join?

A join is an SQL operation used to combine rows from two or more tables based on a related column.

Example Tables

Imagine two tables:

• students(student_id, name, dept_id)
• departments(dept_id, dept_name)

Joins can combine student information with department information.

INNER JOIN

INNER JOIN returns only matching rows from both tables.

Example idea:

If a student has a matching department id, that student row appears.

LEFT JOIN

LEFT JOIN returns all rows from the left table and matching rows from the right table.

If no match exists, right-side values become null.

RIGHT JOIN

RIGHT JOIN returns all rows from the right table and matching rows from the left table.

FULL JOIN

FULL JOIN returns all matching rows plus unmatched rows from both sides.

SELF JOIN

SELF JOIN joins a table with itself.

It is useful when records in the same table are related, such as employee-manager relationships.

Difference Between Common Joins

| Join Type | Result | | --- | --- | | INNER JOIN | Only matched rows | | LEFT JOIN | All left rows + matched right rows | | RIGHT JOIN | All right rows + matched left rows | | FULL JOIN | All rows from both tables |

When to use joins

• use INNER JOIN when only matched data is needed
• use LEFT JOIN when all records from main table are required
• use SELF JOIN when table relates to itself

Interview-style answer

A join is used to combine data from related tables. INNER JOIN returns only matched records, LEFT JOIN returns all rows from the left table and matched rows from the right table, RIGHT JOIN returns all right-table rows, and FULL JOIN returns all rows from both sides.

Common mistakes

• joining on wrong column
• forgetting table aliases
• confusing LEFT JOIN with INNER JOIN
• not understanding null values in unmatched rows

Quick Revision Points

• joins combine related tables
• INNER JOIN = matched rows only
• LEFT JOIN = all left rows
• RIGHT JOIN = all right rows
• SELF JOIN = same table joined with itself

Viva Questions

1. What is a join in SQL?
2. What is the difference between INNER JOIN and LEFT JOIN?
3. What is SELF JOIN?
4. Why are joins important in DBMS?
5. Give one real-world use case of SQL joins.