Difference between a theta join, equijoin and natural join

sql database relational-database relational-algebra
maclunian picture maclunian · Oct 24, 2011 · Viewed 129.1k times · Source

I'm having trouble understanding relational algebra when it comes to theta joins, equijoins and natural joins. Could someone please help me better understand it? If I use the = sign on a theta join is it exactly the same as just using a natural join?

Answer

outis picture outis · Oct 24, 2011

A theta join allows for arbitrary comparison relationships (such as ≥).

An equijoin is a theta join using the equality operator.

A natural join is an equijoin on attributes that have the same name in each relationship.

Additionally, a natural join removes the duplicate columns involved in the equality comparison so only 1 of each compared column remains; in rough relational algebraic terms: ⋈ = πR,S-as ○ ⋈aR=aS

Related questions

What are the options for storing hierarchical data in a relational database?

Good Overviews Generally speaking, you're making a decision between fast read times (for example, nested set) or fast write times (adjacency list). Usually, you end up with a combination of the options below that best fit your needs. The following …

Read More
MySQL query, MAX() + GROUP BY

Daft SQL question. I have a table like so ('pid' is auto-increment primary col) CREATE TABLE theTable ( `pid` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, `timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, `cost` INT UNSIGNED NOT NULL, `rid` INT NOT NULL, ) Engine=InnoDB; …

Read More
What are the uses for Cross Join?

A cross join performs a cartesian product on the tuples of the two sets. SELECT * FROM Table1 CROSS JOIN Table2 Which circumstances render such an SQL operation particularly useful?

Read More