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

Target Load Type - Normal or Bulk in Session Properties

By
We can see the Target load type ( Normal or Bulk) property in session under Mapping tab and we will go for Bulk to improve the performance of session to load large amount of data. SQL loader utility will be used for Bulk load and it will not create any database logs(redolog and undolog), it directly writes to data file.Transaction can not be rolled back as we don't have database logs.However,Bulk loading is very as compared to Normal loading. In target if you are using Primary Key or Primary Index or any constraints you can't use Bulk mode. We can see this property in the below snap shot.
Post a Comment
Read more