Joins

What are Joins ?

Joins may be defined as SQL queries, which are used to combine rows from two or more tables or views based on the relationship between the tables.

Why and When Joins are required ?

In our earlier posts , we have read about the process of Normalization ,to reduce the redundancy. As a result of normalization the database is divided into multiple tables. if we look from ETL prospective for e.g. In Datawarehouse systems , in order to generate report , data is required from multiple tables . This is where joins comes into picture to combine data from different tables.

Types of Joins :

1. Natural Join.
2. Equi Join / Inner Join.
3. Non Equi Join.
4. Cross Join.
5. Self Join.
6. Outer Join

 

Cross Join :

  1. It Joins two or more tables and returns a cartesian product of the joined tables.
  2. Whether records in both tables are matching or not , it will combine each row of first table with each row of the second table.

Self Join :

Outer Join :


 

 joinNatural Join , Inner Join , Equi Join : Same or different ??? 

There are lot of discussions regarding this, Some say they are equal and some say they are different. Actually, there is a very thin line difference between them.

Natural Join :

1. It joins two or more tables and the output is the common rows of both the tables .

2. The Common Column between the two tables is displayed as one single column i.e. appears only once in the        output resultset.

3.Only Equal  ‘= ‘ Operator can be used while comparing the tables in Natural Join.

Inner Join :

Select * from INNER JOIN on = ; e.g. Select * from EMP INNER JOIN DEPT on EMP.Deptno = DEPT.Deptno;
1. It joins two or more tables and the output is the common rows of both the tables.

2. The Common Column between the two tables are displayed as two columns i.e. appears twice in the output resultset.

3.Besides ‘=’ other operators ‘>’ ,'<‘ can also be used while comparing the tables in Inner Join.

Equi Join :

1. It joins two or more tables and the output is the common rows of both the tables.

2. The Common Column between the two tables are displayed as two columns i.e. appears twice in the output resultset.

3. Equi join can be considered as part of Inner join but here only ‘=’ operator can be used while comparing the     tables .


 

 

 

Correlated Sub Queries.
Set Operators
Shikha Katariya

Shikha Katariya

Shikha Katariya ,the Blog author is QA Engineer by profession,Currently serving in MNC, She has more than 4 years of experience in software industry and has worked for domains like Insurance , Core & retail Banking. Always keen to learn new technologies , she has working experience in mainframes,informatica ,and ETL Testing.

You may also like...

Leave a Reply

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