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

Af: Fiona Brown Fiona Brown
Hours Opdateret

โšก Smart opsummering

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.

  • ๐Ÿ”— Kerneformรฅl: A join merges related tables into a single result set based on a join condition.
  • ๐ŸŽฏ Indre tilslutning: 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.
  • โฌ…๏ธ Venstre ydre samling: Keeps every row from the left table, filling null where the right has no match.
  • โžก๏ธ Hรธjre ydre samling: Keeps every row from the right table, filling null where the left has no match.
  • ๐ŸŒ Fuld ydre forbindelse: Keeps all rows from both tables regardless of match.
Lรฆs mere

Joins in DBMS Left Right

What is a Join in DBMS?

Deltag i 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 udenlandske nรธgler.

Typer af tilslutning

Der er hovedsageligt to typer joinforbindelser i DBMS:

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

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

Bliv Medlem Returpolitik Unmatched rows
Inner (Theta / EQUI / Natural) Rows matching the condition in both tables Faldt
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

Indvendig sammenfรธjning

Indvendig sammenfรธjning 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 slutter sig
  • Naturlig sammenfรธjning
  • EQUI tilslutte sig

Theta Deltag

Theta Deltag 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.

Syntaks:

A โ‹ˆฮธ B

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

Overvej fรธlgende tabeller.

Tabel A Tabel B
kolonne 1 kolonne 2 kolonne 1 kolonne 2
1 1 1 1
1 2 1 3

For eksempel:

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

RELATEREDE ARTIKLER

EQUI Deltag

EQUI Deltag 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 eksempel:

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

Natural Join (โ‹ˆ)

Naturlig Deltag 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.

Eksempel: consider the following two tables.

C
I Firkant
2 4
3 9
D
I Cube
2 8
3 18 
C โ‹ˆ D
C โ‹ˆ D
I Firkant Cube
2 4 8
3 9 18

Ydre tilslutning

An Ydre tilslutning 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:

  • Venstre ydre samling
  • Hรธjre ydre samling
  • Fuld ydre tilslutning

Venstre ydre samling (A โŸ• B)

Venstre ydre samling 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.

Venstre ydre samling

Consider the following two tables.

A
I Firkant
2 4
3 9
4 16
B
I Cube
2 8
3 18
5 75 
A Left Outer Join B
A โŸ• B
I Firkant Cube
2 4 8
3 9 18
4 16 -

Hรธjre ydre samling (A โŸ– B)

Hรธjre ydre samling 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.

Hรธjre ydre samling

A Right Outer Join B
A โŸ– B
I Cube Firkant
2 8 4
3 18 9
5 75 -

Fuld ydre sammenfรธjning (A โŸ— B)

I en Fuld ydre tilslutning, alle tuples fra begge relationer er inkluderet i resultatet, uanset den matchende betingelse.

Eksempel:

A Full Outer Join B
A โŸ— B
I Firkant Cube
2 4 8
3 9 18
4 16 -
5 - 75

Ofte Stillede Spรธrgsmรฅl

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.

Opsummer dette indlรฆg med: