Joins in Oracle

  • Join is a query that is used to combine rows from two or more tables

Types of Join :

  1. Inner Join (Normal Join)
    • Default join is inner join. (Inner keyword is not madatory)
    • Only common matching data in both the table will be displayed
  2. Left Join aka Left Outer Join
    • All the values from the left table will always be displayed
  3. Right Join aka Right Outer Join
    • All the values from the right table will always be displayed
  4. Outer Join
    • All the contents of both the table will be displayed
    • At least 1 entry of all the rows of both the tables are displayed for both the tables
  5. Self Join
    • Joining the same table with itself
  6. Cross Join
    • If two tables in a join query have no join condition, then Oracle Database returns their Cartesian product. 
    • All the rows of one table is mapped with all the rows of another table
    • For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows.
  7. Anit Join (not exist)
  8. Semi Join (exist)

Example :

reate table temp1_202103071158(
name varchar2(10)
);

create table temp2_202103071158(
name varchar2(10)
);

insert into temp1_202103071158 values('Tyson');
insert into temp1_202103071158 values('Justin');
insert into temp1_202103071158 values('Martin');


insert into temp2_202103071158 values('Jake');
insert into temp2_202103071158 values('Blake');
insert into temp2_202103071158 values('Duke');
insert into temp2_202103071158 values('Tyson');

--truncate table temp1_202103071158;
--truncate table temp2_202103071158;

select * from temp1_202103071158 t1 inner join temp2_202103071158 t2 on (t1.name=t2.name);

select * from temp1_202103071158 t1 left join temp2_202103071158 t2 on (t1.name=t2.name);

select * from temp1_202103071158 t1 right join temp2_202103071158 t2 on (t1.name=t2.name);

select * from temp1_202103071158 t1 full outer join temp2_202103071158 t2 on (t1.name=t2.name);

select * from temp1_202103071158,temp2_202103071158; --cross join

select * from temp1_202103071158 t1 where exists (select 1 from temp2_202103071158 t2 where t1.name=t2.name);--Semi join

select * from temp2_202103071158 t2 where not exists (select 1 from temp1_202103071158 t1 where t1.name=t2.name);--Anit Join

Leave a Comment