tra
JOINS
Joins are needed to retrieve data from two tables’ related rows on the basis of some condition which satisfies both the tables. Mandatory conditions to join is that atleast one set of column (s) should be taking values from same domain in each table.
Types of Joins
The Two types of joins are given below.
Inner Join
inner join is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. These may be further divided into three parts.
- Equi Join (satisfies equality condition)
- Non-Equi Join (satisfies non-equality condition)
- Self Join (one or more column assumes the same domain of values) Considers only pairs that satisfy the joining condition
Result is the intersection of the two tables.
.Key Points
- The cross join does not apply any predicate to filter records from the joined
table. Programmers can further filter the results of a cross join by using a WHERE clause.
- An equi-join is a specific type of comparator-based join, that uses
only equality comparisons in the join-predicate.
- A special case, a table (base table, view, or joined table) can JOIN to itself in a self-join.
Outer Join
An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record-even if no other matching record exists.
Considers also the rows from table (S) even if they don’t satisfy the joining condition.
- Right outer join
- Left outer join
- Full outer join
Left Out Join
The result of a left outer join for table A and B always contains all records of the left table (A), even if the join condition does not find any
matching record in the right table (B).
Right Outer Join
A right outer closely resembles a left outer join, except with the treatment of
the tables reversed. Every row from the right table will appear in the joined table at least once. If no matching with left table exists, NULL will appear.
Result set of T1 and T2
| T1-ID | Name | T2-ID | Branch |
| 1 | Ram | 1 | IT |
| NULL | NULL | 3 | cs |
Full Outer Join
A full outer join combines the effect of applying both left and right outer joins. Where records in the FULL OUTER JOIN table do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those records that do match, as single row will be produced in the result set.
Result set T1 and T2 (using table of previous example)
Cross Join (Cartesian Product)
Cross join returns the cartesian product of rows form tables in the join.lt will produce rows which combine each row from the first table with each row from the second table.
Select * FROM T1, T2
Number of rows in result set = (Number of rows in table 1 x Number of rows in table 2)
Result set of T1 and T2 (Using previous tables T1 and T2)
Key Points
- A programmer writes a JOIN predicate to identify the record for joining if the evaluated predicate is true, the combined record is then expected format a record set or temporary table.
- The right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate) .
- A left outer join returns all the values from an inner join plus all values in the table that do not match to the right table.