Skip to main content

Types of Joins in Oracle/Teradata

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

Popular posts from this blog

Comparing Objects in Informatica

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 ...

Labelling and creating queries in informatica

Informatica components i.e. mappings,session,workflows,transformation are moved from one environment to another environment say from development to SIT can be done in various ways such as --Export the xml's and again import the xml's in the respective environment --Drag and Drop objects from one repository to another repository For the above process to be done you should have required access across environments. One other way is labelling the components and then creating queries in repository manager. Using these queries components are moved across environment's We need to go to the repository manager and select the version tab and select label Then click new label and give a name say "Wf Migration" and click OK. There we  have permissions tab where we can give access to other users to use the same label. Now we need to select the workflow which needs to be promoted to higher environment.  Right click on workflow and select "view dependencies...

Pre and Post Sql,Session properties at session level

Sometimes we may have a requirement where might need to drop indexes/truncate table and then load the data. So in here what we can we do is we can go for pre sql in session properties and there we can write the required the sql statements such as dropping indexes or truncating table and after the loads i.e. session gets completed we can recreate the indexes in post sql command. In mapping also we can do the same but it is advisable to do at session level. We even have option in session such as Presession, Postsession command and Postsession failure command. In session go to components tab to find out these options. Upon requirement we can use the following command to run unix scripts for specific purpose. We have email option option for on success/failure scenarios too. Hope this article helps you in understanding post/pre session commands