Skip to main content

Types of Joins in Oracle/Teradata

By Anonymous
In Data warehousing, irrespective of schema (snow flake schema or star schema) we are using, we should join dimension and fact tables to analyze the business.
Below are the frequently used joins:
Inner join
Left outer Join
Right outer Join
Cross join

Inner Join:
Inner join will give you the matching rows from both the tables. If the join condition is not matching then zero records will return. We should use ON keyword to give join condition.
Example:

Table1:
ID
Name
1
Krishna
2
Anirudh
4
Ashok
Table2:
ID
Location
1
Bangalore
3
Chennai
4
Chennai


We can join above two tables using inner join based on key column ID.
SELECT T1.ID, T1.Name, T2.Location
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.ID = T2.ID   
If we are using inner join, it will give us matching rows from both the table. Here in this example, we have 2 matching rows i.e. ID 1 and 4. Below will be the result set for the above example query with inner join.
Result set:
ID
Name
Location
1
Krishna
Bangalore
4
Ashok
Chennai




Left outer Join:
Left outer join will give you the all rows from first table and matching rows from second table. If the join condition is not matching then NULL will return for the fields which are from second table. We should use ON keyword to give join condition.

Example:
   Table1:
ID
Name
1
Krishna
2
Anirudh
4
Ashok
Table2:
ID
Location
1
Bangalore
3
Chennai
4
Chennai


 
We can join above two tables using left outer join based on key column ID.
SELECT T1.ID, T1.Name, T2.Location
FROM Table1 T1
LEFT OUTER JOIN Table2 T2
ON T1.ID = T2.ID

If we are using inner join, it will give us all rows from first table and matching rows from second table. Below will be the result set for the above example query with left outer join.
Result set:
ID
Name
Location
1
Krishna
Bangalore
2
Anirudh
(NULL)
4
Ashok
Chennai


It will return NULL for Location field where ID = 2 because we do not have matching ID in second table Table2.


Right outer Join:
Right outer join will give you the all rows from second table and matching rows from first table. If the join condition is not matching then NULL will return for the fields which are from first table. We should use ON keyword to give join condition.

Example:
Table1:
ID
Name
1
Krishna
2
Anirudh
4
Ashok
Table2:
ID
Location
1
Bangalore
3
Chennai
4
Chennai


We can join above two tables using Right outer join based on key column ID.
SELECT T1.ID, T1.Name, T2.Location
FROM Table1 T1
RIGHT OUTER JOIN Table2 T2
ON T1.ID = T2.ID

If we are using inner join, it will give us all rows from second table and matching rows from first table. Below will be the result set for the above example query with Right outer join.
Result set:
ID
Name
Location
1
Krishna
Bangalore
2
(NULL)
Chennai
4
Ashok
Chennai


It will return NULL for name filed where ID = 2 because we do not have matching ID in first table Table2.

Cross Join:

If we use Cross join, every row from first table will join with every row from second table. It will give
1 - Many join result. For cross join, no need to mention joins condition.

Example:
Table1:
Year
2012
2013
2014
Table2:
Month
Jan
Feb
March


If we use cross join for this example, we will get 9 rows as result (3*3).
SELECT T1.Year, T2.Month
FROM Table1 T1
Cross Join Table2 T2

The result set will be like below.

Result Set:
Year
Month
2012
Jan
2012
Feb
2012
March
2013
Jan
2013
Feb
2013
March
2014
Jan
2014
Feb
2014
March


Comments

Post a Comment

Popular posts from this blog

Comparing Objects in Informatica

By
We might face a scenario where there may be difference between PRODUCTION v/s SIT version of code or any environment or between different folders in same environment. In here we go for comparison of objects we can compare between mappings,sessions,workflows In Designer it would be present under "Mappings" tab we can find "Compare" option. In workflow manger under "Tasks & Workfows" tab we can find "Compare" option for tasks and workflows comparison respectively. However the easiest and probably the best practice would be by doing using Repository Manager.In Repository Manager under "Edit" tab we can find "Compare" option. The advantage of using Repository manager it compares all the objects at one go i.e. workflow,session and mapping. Hence reducing the effort of individually checking the mapping and session separately. Once we select the folder and corresponding workflow we Can click compare for checking out ...
11 comments
Read more

Finding Duplicate records and Deleting Duplicate records in TERADATA

By Anonymous
Requirement: Finding duplicates and removing duplicate records by retaining original record in TERADATA Suppose I am working in an office and My boss told me to enter the details of a person who entered in to office. I have below table structure. Create Table DUP_EXAMPLE ( PERSON_NAME VARCHAR2(50), PERSON_AGE INTEGER, ADDRS VARCHAR2(150), PURPOSE VARCHAR2(250), ENTERED_DATE DATE ) If a person enters more than once then I have to insert his details more than once. First time, I inserted below records. INSERT INTO DUP_EXAMPLE VALUES('Krishna reddy','25','BANGALORE','GENERAL',TO_DATE('01-JAN-2014','DD-MON-YYYY')) INSERT INTO DUP_EXAMPLE VALUES('Anirudh Allika','25','HYDERABAD','GENERAL',TO_DATE('01-JAN-2014','DD-MON-YYYY')) INSERT INTO DUP_EXAMPLE VALUES('Ashok Vunnam','25','CHENNAI','INTERVIEW',TO_DATE('01-JAN-2014',...
2 comments
Read more

Updating Target Table in Informatica

By Anonymous
Generally, in every project we will see at least one requirement to update target based on some conditions. Case1: When we have primary key defined on table in Database Case2:   When we do not have primary key defined on table in Database Case3: When we want to update target based on non-primary key field. Let’s discuss it in detail. Case1: When we have primary key defined on table in Database After creating table in DB, import table as target along with primary key constraints. After done with importing, we can check the primary key details by editing target in target definition. In this case, we can use update strategy to update target table. Suppose, we have a table called RETURN_CD and primary key ROW_WID defined on that table. Now, we can update target table using update strategy based on primary key. In mapping, we should connect primary key port and the ports which we want to update in target table. Please check the below screenshot. And ...
Post a Comment
Read more