Whenever we need to retrieve data from two or more tables we can use Joins.The output of join is matching records from both the tables.
In Teradata database we have Optimizer to determine which type of join strategy to be used and type of indexes to be used.
In General, Most common types of joins are the following.
Inner join (Self join)
Outer Join (Left outer join, Right outer join, Full outer join)
Cross join (Cartesian product join)
inner join retrieves matching records from both tables.
ReplyDeleteleft join retrieves matching rows both tables and non matching rows from left table.
tight join retrieves matching rows both tables and non matching rows from right table.
Full join retrieves matching rows from both tables and non matching rows from left table then right table.we can achieve this by intersect of left join and right join.
join strategies:
merge join(occurs on equality condition)
nested join(unique index get one single row based on quality condition then it will join with other table rows)
product join,
Cartesian product join and hash join.