DATABASE & SQL/PLSQL

Equi Join

A equi join display redundant column of data in the result set, where two or more tables are compared for equality.

Example:

SELECT *
FROM  sales s  JOIN  titles t
ON  s.title_id=t.title_id
JOIN  publishers p
ON  t.pub_id=p.pub_id

output

Returns the above columns in the result set.

Natural Join

A join that restrict the redundant column from the result set is known as natural join

SELECTt.title, p.pub_name
FROM titles  t  JOINpublishers p
ONt.pub_id=p.pub_id

Returns the all title and pub_name from respective tables.

Self Join

A join is said to self join when one row in a table correlates with the other rows in the same table. An alias name can differentiate the two copy of the table. All join operators except the outer join operators can be used in a self join.

Example

SELECTt1.title, t2.title, t1.price
FROM titles t1  JOIN titles t2ONt1.price=t2.price
WHERE t1.price=2.99

output