Handbook of Computer Science(cs) and IT

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.

  1. Right outer join
  2. Left outer join
  3. 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.

Pages: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95

Leave a Reply

Your email address will not be published. Required fields are marked *