Equi joins in SQL: Joining refers to formation of pair of rows by matching contents of a related column. If the match is exact in a join then it is knows as an equi join. Concept of joining is used to make cross table relationships between data items.
SELECT NAME, ADDRESS, PHONE, SALARY FROM EMPLOYEES, SALARY_INFO WHERE EMP = EMPL_N
An equi join can be used to form a parent and child relationship by use of primary and foreign keys:
1) A join on matching columns creates many to many relationships.
2) In a join if primary key is matched to foreign key, then the parent and child relationship would be one to many.
3) If matching column in at least one of the tables has unique values for all rows of table, then also it will result in a one to many relationship.
Non Equi Joins in SQL: A join operation can be done by use of other operators other than equality is known as a non equi join.
SELECT NAME, ADDRESS, PHONE, SALARY FROM EMPLOYEES, SALARY_INFO WHERE SALARY > AVG_SALARY
Self join: A self join refers to a relationship of a table with itself. For a self join SQL uses the concept of table alias. A table alias is an imaginary table created to join a table to itself.
For Example: SELECT EMPL.NAME, HR.NAME FROM EMPLOYEES EMPL, EMPLOYEES HR WHERE EMPL.HUMANRES = HR.EMPL_N
The above query generates employee names with their respective HR.