1 Answer

1. 

   

   Urmila Pal

   • Asp.net Developer with Oracle database connectivity

   • 1 Question
   • 3 Answers
   • 1 Best Answer
   • 36 Points

   View Profile

   Best Answer

   Urmila Pal Asp.net Developer with Oracle database connectivity

   2020-08-26T12:25:52+05:30Added an answer on August 26, 2020 at 12:25 pm

   All the best for your interview Rohan, I hope After reading this answer you will be able to reply to any Joins questions.

   Joins are used to fetch data from multiple tables in your database. That’s because it is not practically possible to store your entire data in one table. It combines rows from multiple tables or views.

   For example, you have to fetch the address of your employee and there are four tables for that:

   1. EMPDetails(pkEmpId number, FName varchar2(20), LName varchar2(20), DOB date, Phone number, Email varchar2(50), supervisorID number)
   2. EmpAddress(fkEmpID number, add1 varchar2(30), add2 varchar2(30), add3 varchar2(30), fkStateid number, fkcountryID number)
   3. MstState(pkStateID number, SName varchar2(30))
   4. MstCountry(pkCountryID number, CountryName varchar2(30))

   Query: 

   Select pkEmpId, FName, LName, DOB, Phone, Email, add1||’ ‘||add2 ||’ ‘||add3 as address, SName , CountryName from EMPDetails e left join EmpAddress EA on EA.fkEmpID = e.pkEmpId left join MstState s on s.pkStateID=EA.fkStateid left join MstCountry C on C.pkCountryID=EA.fkcountryID order by pkEmpId;

   There are 4 Mostly Asked joins in Oracle SQL.

   1. EquiJoin: In this join, an Equality operator is used. The operator will check whether the values of both sides match with each other or not. If not, then it will not fetch that row in the result section.

   Example:  In the mentioned example for any row if EA.fkEmpID is not same as the e.pkEmpId then it will not result in that particular row.

   2. Self Join: If you have to join your table to itself then you must use this join to get the desired output.

   For example, if you have to fetch your employee name along with their supervisor name then you must use this join.

   Select e1.FName||’is Supervisor of’||e2.FName from EMPDetails e1 join EMPDetails e2 on e2.supervisorID= e1. pkEmpId  order by e2.FName;

   3. Inner Join: This join will combine only those rows from multiple tables which are common in all tables according to the join condition. It works the same as the intersection in the sets.

   For example, if an employee has two addresses (current and permanent) then we can specify which address do we want in the join condition and “inner join” keyword will make sure that the employee ID is available in both the tables.

   Select FName, LName, add1||’ ‘||add2 ||’ ‘||add3 as address from EMPDetails e inner join EmpAddress EA on e.pkempid=EA.fkempid and fkstateid=2;

   4. Outer Joins: It fetches all rows from all table used in the query regardless of the join condition. When there is no match for the join condition it automatically put NULL on its place.

   Three types of outer join are there: 

   1. Left outer join: f there are two tables in the join query then it fetches all rows from the table listed on the left side of the query.

            Keyword: Left outer join   

   2. Right outer join: if there are two tables in the join query then it fetches all rows from the table listed on the right side of the query.

               Keyword: right outer join

   3. Full outer join: it fetches all rows from both the tables.

               Keyword: full outer join

   There are semijoins and antijoins too.

   In Semijoins, ‘EXISTS’ operator is used which returns rows from the left side of the query without any duplicacy.

   Select * from mstState where exists(select * from mstState where pkStateid=1);

   In AntiJoins, ‘NOT IN’ operator is used to return rows from the left side of the query which fails to match the value written under not in parenthesis.

   Select * from mstCountry where countryName not in(‘Germany’, ‘Australia’);

   If you find this information useful, please like this answer and share it with your friends too.

   • 4

   • Reply
   • Share
     Share

     • Share on Facebook
     • Share on Twitter
     • Share on LinkedIn
     • Share on WhatsApp