Joins in DBMS: All Types Explained with SQL Examples

Updated on April 16, 2026

•

13 min read

ARTICLE OUTLINE

What is Join in DBMS? – Definition Types of Join in DBMS – Quick Reference Inner Join in DBMS Theta Join in DBMS Equi Join in DBMS Natural Join in DBMS Outer Join in DBMS Self Join in DBMS Cross Join in DBMS Join Dependency in DBMS Join Strategies in DBMS SQL Join Operators in DBMS – Comparison Conclusion People Also Ask FAQs

How do databases retrieve related data stored across multiple tables? The answer is joins in dbms. A join in database management system links rows from two or more tables based on a related column, enabling meaningful data retrieval from normalised database structures. This guide covers every type of join in dbms – inner, outer, natural, equi, theta, self, cross – with SQL examples, join strategies in dbms, and the join dependency in dbms concept from database theory.

What is Join in DBMS? – Definition

To define join in dbms: a join operator in dbms is an SQL operation that combines rows from two or more tables based on a matching condition. Without joins in dbms, retrieving related data stored in separate normalised tables would require multiple queries and manual result assembly.

Property	Description
Join in database management system	SQL operator combining rows from multiple tables on a related column
Join condition (predicate)	The ON clause that specifies which columns must match between tables
Join operator in dbms	The keyword (INNER JOIN, LEFT JOIN, etc.) that determines which rows are included
Result set	Combined columns from all joined tables, filtered by the join condition
Why needed	Normalised databases split related data across tables – joins reassemble it on demand

Key Concept: A join in database management system is the direct consequence of normalisation. When data is distributed across related tables to eliminate redundancy, the join operator in dbms is the mechanism to reassemble that data efficiently at query time.

POSTGRADUATE PROGRAM IN

Multi Cloud Architecture & DevOps

Master cloud architecture, DevOps practices, and automation to build scalable, resilient systems.

Types of Join in DBMS – Quick Reference

All joins and its types in dbms at a glance:

Join Type	Rows Returned	NULLs?	Keyword
Inner Join	Matched rows from both tables only	No	INNER JOIN
Left Outer Join	All left rows + matched right	Right side	LEFT JOIN
Right Outer Join	All right rows + matched left	Left side	RIGHT JOIN
Full Outer Join	All rows from both tables	Both sides	FULL OUTER JOIN
Cross Join	Every row × every row (Cartesian)	No	CROSS JOIN
Self Join	Table joined with itself	Depends	Aliased table
Natural Join	Auto-matched on common-name columns	If no match	NATURAL JOIN
Equi Join	Equality condition only (=)	No (inner behaviour)	JOIN … ON a.id = b.id
Theta Join	Any comparison operator (>, <, !=)	Depends	JOIN … ON condition

Inner Join in DBMS

The inner join in dbms returns only rows with matching values in both tables. Unmatched rows from either table are excluded. It is the most widely used sql join in dbms and the default when no outer qualifier is specified.

Students Table:

`student_id`	`name`	`age`
`1`	`Neha`	`20`
`2`	`Murali`	`22`
`3`	`Divyansh`	`19`

Courses Table:

`course_id`	`student_id`	`course_name`
`101`	`1`	`Math`
`102`	`2`	`Science`
`103`	`4`	`History`

-- inner join in dbms

SELECT Students.name, Courses.course_name

FROM Students

INNER JOIN Courses ON Students.student_id = Courses.student_id;

Output:

`name`	`course_name`
`Neha`	`Math`
`Murali`	`Science`

Divyansh (student_id=3) has no matching course record – excluded by the inner join in dbms. course_id=103 maps to student_id=4 which doesn’t exist in Students – also excluded. Only rows with matches in both tables appear.

Theta Join in DBMS

Theta join is a generalisation of the join operator in dbms that allows any comparison operator in the condition – >, <, >=, <=, or !=. This makes it more flexible than the equi join for range-based or inequality-based matching. This is one of the key join in dbms with example patterns used for salary bands, price ranges, and date intervals.

Employees Table:

`emp_id`	`name`	`salary`	`dept_id`
`1`	`Divyansh`	`50000`	`10`
`2`	`Krish`	`60000`	`20`
`3`	`Neha`	`55000`	`30`

Departments Table:

`dept_id`	`dept_name`	`min_salary`
`10`	`HR`	`45000`
`20`	`IT`	`55000`
`30`	`Sales`	`52000`

-- theta join - any comparison operator (>=)

SELECT Employees.name, Departments.dept_name

FROM Employees

JOIN Departments ON Employees.salary >= Departments.min_salary;

Output:

`name`	`dept_name`
`Divyansh`	`HR`
`Krish`	`IT`
`Krish`	`HR`
`Neha`	`Sales`
`Neha`	`HR`

An employee may match multiple departments because the theta condition (salary >=) is satisfied against more than one department’s min_salary. Krish (60,000) qualifies for both IT (min 55,000) and HR (min 45,000).

82.9%

of professionals don't believe their degree can help them get ahead at work.

Equi Join in DBMS

Equi join in dbms is a theta join where the condition exclusively uses equality (=). It is functionally the same as an inner join on matching key columns. The equi join in dbms is the most common join condition pattern in relational database design – virtually every foreign key-to-primary key join is an equi join.

Authors Table:

`author_id`	`author_name`
`1`	`Mark Twain`
`2`	`Jane Austen`
`3`	`J.K. Rowling`

Books Table:

`book_id`	`title`	`author_id`
`101`	`Tom Sawyer`	`1`
`102`	`Pride and Prejudice`	`2`
`103`	`Harry Potter`	`3`

-- equi join in dbms - equality condition

SELECT Authors.author_name, Books.title

FROM Authors

INNER JOIN Books ON Authors.author_id = Books.author_id;

Output:

`author_name`	`title`
`Mark Twain`	`Tom Sawyer`
`Jane Austen`	`Pride and Prejudice`
`J.K. Rowling`	`Harry Potter`

Natural Join in DBMS

Natural join in dbms automatically joins tables on all columns that share the same name and compatible data type – no ON clause is needed. The natural join in dbms eliminates duplicate common columns from the output, showing them only once.

Employees Table:

`emp_id`	`name`	`dept_id`
`1`	`Suman`	`10`
`2`	`Sonali`	`20`
`3`	`Aditi`	`30`

Departments Table:

`dept_id`	`dept_name`
`10`	`HR`
`20`	`IT`
`30`	`Sales`

-- natural join in dbms - auto-matches on dept_id

SELECT Employees.name, Departments.dept_name

FROM Employees NATURAL JOIN Departments;

Output:

`name`	`dept_name`
`Suman`	`HR`
`Sonali`	`IT`
`Aditi`	`Sales`

Key Concept: Use natural join in dbms with caution. If both tables share multiple column names (name, id, date), the natural join matches on ALL of them simultaneously – potentially producing no results or wrong results. Explicit ON conditions are always safer for production SQL.

Outer Join in DBMS

The outer join in dbms includes rows that do not have a matching record in the joined table, replacing missing values with NULL. This distinguishes the outer join in dbms from the inner join – no row from the preserved side is ever excluded.

Left Outer Join

Customers Table:

`customer_id`	`customer_name`
`1`	`Murali`
`2`	`Divyansh`
`3`	`Anupriya`

Orders Table:

`order_id`	`customer_id`	`product_name`
`101`	`1`	`Laptop`
`102`	`2`	`Phone`
`103`	`4`	`Tablet`

-- left outer join - sql join in dbms

SELECT Customers.customer_name, Orders.product_name

FROM Customers LEFT JOIN Orders

ON Customers.customer_id = Orders.customer_id;

Output:

`customer_name`	`product_name`
`Murali`	`Laptop`
`Divyansh`	`Phone`
`Anupriya`	`NULL`

Anupriya (customer_id=3) has no order – the outer join in dbms preserves her row with NULL for product_name.

Right Outer Join

Products Table:

`product_id`	`product_name`
`1`	`Laptop`
`2`	`Phone`
`3`	`Tablet`

Suppliers Table:

`supplier_id`	`product_id`	`supplier_name`
`101`	`1`	`Supplier A`
`102`	`2`	`Supplier B`
`103`	`4`	`Supplier C`

-- right outer join - join in dbms with example

SELECT Products.product_name, Suppliers.supplier_name

FROM Products RIGHT JOIN Suppliers

ON Products.product_id = Suppliers.product_id;

Output:

`product_name`	`supplier_name`
`Laptop`	`Supplier A`
`Phone`	`Supplier B`
`NULL`	`Supplier C`

Full Outer Join

-- full outer join - all rows from both tables

SELECT Customers.customer_name, Orders.product_name

FROM Customers

FULL OUTER JOIN Orders ON Customers.customer_id = Orders.customer_id;

-- Includes: all customers (NULL if no order) + all orders (NULL if no customer)

Full outer join is the most inclusive type of outer join in dbms – every row from both tables appears, with NULLs wherever no match exists on either side.

Self Join in DBMS

A self join in dbms joins a table with itself by aliasing it twice – useful for hierarchical or self-referencing relationships within a single table. The self join in dbms requires two different aliases so the query engine treats the same table as two logical tables.

Employees Table (with manager_id):

`emp_id`	`name`	`manager_id`
`1`	`Alice`	`NULL`
`2`	`Bob`	`1`
`3`	`Carol`	`1`
`4`	`Dave`	`2`

-- self join in dbms - employee-manager hierarchy

SELECT e.name AS employee, m.name AS manager

FROM Employees e

LEFT JOIN Employees m ON e.manager_id = m.emp_id;

Output:

`employee`	`manager`
`Alice`	`NULL`
`Bob`	`Alice`
`Carol`	`Alice`
`Dave`	`Bob`

Self Join Use Case	Description
Employee-Manager hierarchy	Find who each employee reports to via a self-referencing manager_id
Product category tree	Navigate parent-child category relationships in a single Category table
Bill of Materials	Identify components that are also sub-assemblies of other components
Social network connections	Find mutual connections within a single Users table

Cross Join in DBMS

A cross join produces the Cartesian product of two tables – every row from Table A combined with every row from Table B. If Table A has m rows and Table B has n rows, the result has m × n rows. No join condition is specified.

Colours (3 rows) × Sizes (2 rows) = 6 rows:

-- cross join - cartesian product

SELECT Colours.colour, Sizes.size

FROM Colours CROSS JOIN Sizes;

`colour`	`size`
`Red`	`Small`
`Red`	`Large`
`Green`	`Small`
`Green`	`Large`
`Blue`	`Small`
`Blue`	`Large`

Key Concept: Cross joins generate very large result sets quickly – 1,000 rows × 1,000 rows = 1,000,000 rows. Use intentionally for combination matrices (e.g., product configurations). Accidental cross joins on large tables are a common performance disaster.

Join Dependency in DBMS

Join dependency in dbms is a normalisation concept from 5th Normal Form (5NF/PJNF). A join dependency in dbms exists when a table can be losslessly decomposed into multiple projections and exactly reconstructed via natural joins without producing spurious (incorrect) or missing rows.

Aspect	Description
Definition	A table satisfies a join dependency if it can be decomposed into n projections and reconstructed via joins without data loss
Related Normal Form	Fifth Normal Form (5NF) – also called Project-Join Normal Form (PJNF)
Goal	Eliminate all join dependencies not implied by candidate keys
When it occurs	Three-way (or higher) relationships that cannot be safely split into two-way tables
Violation consequence	Decomposed tables, when rejoined, produce missing rows or incorrect spurious tuples
Classic example	Supplier-Part-Project: three-way relationship that cannot be losslessly split into three two-way tables

Join dependency in dbms affects schema design decisions. Violating a join dependency means the joins in dbms on decomposed tables will either miss valid combinations or produce incorrect extra rows. Designing to 5NF ensures all join dependencies are properly handled – making every sql join in dbms produce correct, complete results.

Join Strategies in DBMS

Join strategies in dbms are the internal algorithms a query engine uses to physically execute a join. The query optimiser selects from these join strategies in dbms based on table sizes, available indexes, memory, and the join condition. Understanding them helps diagnose slow queries.

Strategy	Mechanism	Best For
Nested Loop Join	For each outer row, scan all inner rows for matches – O(m×n)	Small tables or indexed inner table (O(m×log n))
Hash Join	Build hash table from smaller table; probe with larger – O(m+n)	Large equality joins without usable index
Sort-Merge Join	Sort both on join key; merge in one pass – O(m log m + n log n)	Pre-sorted data; range joins; large tables
Index Nested Loop	Nested loop using index on inner table	Selective equality conditions with existing index
Grace Hash Join	Partitioned hash for joins exceeding memory	Very large tables beyond available RAM

-- See join strategies in dbms via EXPLAIN (PostgreSQL)

EXPLAIN ANALYZE

SELECT Students.name, Courses.course_name

FROM Students

INNER JOIN Courses ON Students.student_id = Courses.student_id;

-- Output shows: Hash Join / Nested Loop / Merge Join

-- chosen based on table statistics and available indexes

SQL Join Operators in DBMS – Comparison

Reference table for all sql join operators in dbms – behaviour, NULL handling, and use case:

SQL Join Operator	Rows Returned	NULLs	Typical Use Case
INNER JOIN	Matched rows only	No	Fetch records existing in both tables
LEFT JOIN	All left + matched right	Yes (right)	Preserve all left-table records; find unmatched
RIGHT JOIN	All right + matched left	Yes (left)	Preserve all right-table records; find unmatched
FULL OUTER JOIN	All rows, both tables	Yes (both)	Complete picture – nothing excluded
CROSS JOIN	All combinations (m×n)	No	Generate all possible row combinations
NATURAL JOIN	Common-column matched	Depends	Quick join on shared names (use cautiously)
SELF JOIN	Same table with aliases	Depends	Hierarchical/self-referencing data

Most sql join operators in dbms work identically across MySQL, PostgreSQL, Oracle, and SQL Server. Notable exception: FULL OUTER JOIN is not supported natively in MySQL – simulate with UNION of LEFT and RIGHT JOIN. NATURAL JOIN behaviour varies by database – explicit ON conditions are always recommended for production.

Conclusion

Joins in dbms are the fundamental mechanism for retrieving related data from normalised relational databases. Each type of join in dbms serves a specific purpose: the inner join in dbms for matched records only, the outer join in dbms for preserving unmatched rows, the natural join in dbms for automatic column matching, the equi join in dbms for equality conditions, and the self join in dbms for self-referencing hierarchical data.

Understanding join dependency in dbms ensures schemas are designed correctly at the normalisation level, while knowledge of join strategies in dbms helps optimise query performance. Mastering all sql join operators in dbms – and knowing when to explain joins in dbms to stakeholders – is essential for database professionals and backend developers.

To build complete expertise in database management and full-stack development, explore the Full Stack Development with Specialisation for Web and Mobile powered by Hero Vired.