DBMS, Natural, Theta, Equi joins

Theta join:

Definition:  When we perform a select statement that uses more than two tables and the join condition is based on a binary relational operator (Theta) in the set {<, ≤, =, >, ≥}

SQL query:

Select employee.name, job.name from employee, job where employee.Id > job.Id;

Equi Join:

When we perform a select statement that uses more than two tables and the join condition has an “=” operator.

Equi joins are theta joins where theta is “=”.

SQL query:

Select employee.name, job.name from employee, job where employee.Id = job.Id;

Natural Join:

Natural Join is a type of equi-join. It takes place internally by comparing all the same name columns in both the table.

The output of query with natural join will contain only one column for each pair of same named columns.

When we perform a select statement that uses more than two tables and the join condition does not have any comparison operator.

Natural join clause is supported by Oracle and MySQL.

Syntax:

Select * from table1 natural join table2.