DBMS Joins: THETA, Inner, Outer, Equi Types of Join

By : Fiona Brown Fiona Brown
Hours Updated

โšก Smart Summary

Join in DBMS is a binary operation that combines rows from two or more tables using their primary and foreign keys. Joins split into inner joins, which return only matching rows, and outer joins, which keep unmatched rows and fill gaps with null.

  • ๐Ÿ”— Core Purpose: A join merges related tables into a single result set based on a join condition.
  • ๐ŸŽฏ Inner Join: Returns only rows that satisfy the condition in both tables; the default join type.
  • ๐Ÿ“ Theta and EQUI: A theta join uses any comparison operator; an EQUI join uses only equality.
  • ๐Ÿ”„ Natural Join: Matches on identically named attributes and removes the duplicate column.
  • โฌ…๏ธ Left Outer Join: Keeps every row from the left table, filling null where the right has no match.
  • โžก๏ธ Right Outer Join: Keeps every row from the right table, filling null where the left has no match.
  • ๐ŸŒ Full Outer Join: Keeps all rows from both tables regardless of match.
Read More

Joins in DBMS Left Right

What is a Join in DBMS?

Join in DBMS is a binary operation that allows you to combine a join product and a selection in a single statement. The goal of creating a join condition is to combine the data from two or more tables. The tables are associated using their primary keys and foreign keys.

Types of Join

There are mainly two types of joins in DBMS:

  1. Inner Joins: Theta, Natural, EQUI
  2. Outer Joins: Left, Right, Full

The table below previews all the join types before each is explained with an example.

Join Returns Unmatched rows
Inner (Theta / EQUI / Natural) Rows matching the condition in both tables Dropped
Left Outer All left rows plus matches Kept from left, null on right
Right Outer All right rows plus matches Kept from right, null on left
Full Outer All rows from both tables Kept from both, null where missing

Inner Join

Inner Join is used to return rows from both tables that satisfy the given condition. It is the most widely used join operation and can be considered the default join type.

An inner join, or equijoin, is a comparator-based join that uses equality comparisons in the join predicate. However, if you use other comparison operators like “>”, it cannot be called an equijoin. Inner join is further divided into three subtypes:

  • Theta join
  • Natural join
  • EQUI join

Theta Join

Theta Join allows you to merge two tables based on the condition represented by theta. Theta joins work for all comparison operators. It is denoted by the symbol ฮธ. The general case of a JOIN operation is called a theta join.

Syntax:

A โ‹ˆฮธ B

A theta join can use any conditions in the selection criteria.

Consider the following tables.

Table A Table B
column 1 column 2 column 1 column 2
1 1 1 1
1 2 1 3

For example:

A โ‹ˆ A.column 2 > B.column 2 (B)
A โ‹ˆ A.column 2 > B.column 2 (B)
column 1 column 2
1 2

RELATED ARTICLES

EQUI Join

EQUI Join is done when a theta join uses only the equivalence condition. EQUI join is the most difficult operation to implement efficiently in an RDBMS, and it is one reason why an RDBMS can have performance problems.

For example:

A โ‹ˆ A.column 2 = B.column 2 (B)
A โ‹ˆ A.column 2 = B.column 2 (B)
column 1 column 2
1 1

Natural Join (โ‹ˆ)

Natural Join does not use any comparison operator. In this type of join, the attributes should have the same name and domain. In a natural join, there should be at least one common attribute between the two relations.

It performs a selection forming equality on the attributes that appear in both relations and eliminates the duplicate attributes.

Example: consider the following two tables.

C
Num Square
2 4
3 9
D
Num Cube
2 8
3 18 
C โ‹ˆ D
C โ‹ˆ D
Num Square Cube
2 4 8
3 9 18

Outer Join

An Outer Join does not require each record in the two joined tables to have a matching record. In this type of join, the table retains each record even if no other matching record exists. The three types of outer join are:

  • Left Outer Join
  • Right Outer Join
  • Full Outer Join

Left Outer Join (A โŸ• B)

Left Outer Join returns all the rows from the table on the left, even if no matching rows are found in the table on the right. When no matching record is found on the right, null is returned.

Left Outer Join

Consider the following two tables.

A
Num Square
2 4
3 9
4 16
B
Num Cube
2 8
3 18
5 75 
A Left Outer Join B
A โŸ• B
Num Square Cube
2 4 8
3 9 18
4 16 –

Right Outer Join (A โŸ– B)

Right Outer Join returns all the columns from the table on the right, even if no matching rows are found in the table on the left. Where no matches are found on the left, null is returned. A right outer join is the opposite of a left join.

Right Outer Join

A Right Outer Join B
A โŸ– B
Num Cube Square
2 8 4
3 18 9
5 75 –

Full Outer Join (A โŸ— B)

In a Full Outer Join, all tuples from both relations are included in the result, irrespective of the matching condition.

Example:

A Full Outer Join B
A โŸ— B
Num Square Cube
2 4 8
3 9 18
4 16 –
5 – 75

FAQs

An inner join returns only rows that match in both tables. An outer join also keeps unmatched rows from one or both tables, filling the missing side with null.

A theta join can use any comparison operator, such as greater than or less than. An EQUI join is a theta join restricted to the equality operator, so it matches only equal values.

Both match on equality, but a natural join matches automatically on identically named columns and removes the duplicate column. An EQUI join names the condition explicitly and keeps both columns.

Yes. Given the schema and the question, AI can choose the join type and keys, for example a left join to keep customers with no orders. Confirm the result, since a wrong join changes row counts.

Yes. AI can spot that the join key is not unique on one side, producing a many-to-many explosion, and suggest adding a condition or aggregating first so each match is counted once.

Summarize this post with: