Describe Joins in PLSQL

Join is the process of combining data from two or more tables using matching columns. This relational computing feature consolidates multiple data tables for use in a single report. The SQL JOIN statement is used to combine the data contained in two relational database tables based upon a common attribute.

Equi-Join: A join statement that uses an equivalency operation (i.e: colA = colB). The converse of an equijoin is a nonequijoin operation. In the Equi-Join two (or more) tables are linked via a common domain attribute. This is the most common form of joining used in relational data manipulation.

An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns.

Outer Join: The Outer-Join is the opposite of an Equi-Join. It searches for records that exist outside of the specified Join condition. The (+) symbol is the Outer Join operator which causes the join condition to be inverted.

The outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join condition. Such rows are not returned by a simple join. To write a query that performs an outer join of tables A and B and returns all rows from A, apply the outer join operator (+) to all columns of B in the join condition. For all      rows in A that have no matching rows in B,

Self Join: A join in which a table is joined with itself. Sometimes we need to join a table to itself in order to search for the correct data.

A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that are used to qualify column names in the join condition.

                Ex :-

            SELECT e1.ename||’ works for ’||e2.ename

            ”Employees and their Managers”

            FROM emp e1, emp e2 WHERE e1.mgr = e2.empno

Non equijoin: A join statement that does not use an equality operation (i.e: colA <> colB). The converse of a non equijoin is an equijoin.