A equi join display redundant column of data in the result set, where two or more tables are compared for equality.
SELECT * FROM sales s JOIN titles t ON s.title_id=t.title_id JOIN publishers p ON t.pub_id=p.pub_id
Returns the above columns in the result set.
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.
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.
SELECTt1.title, t2.title, t1.price FROM titles t1 JOIN titles t2ONt1.price=t2.price WHERE t1.price=2.99