Joins
In Oracle
What is Joins in Oracle:
Joins in Oracle
used to combine data from two or more tables based on related column between
them. Tables are joined on columns that have the same data type and data width
in the tables.
Following are the
types of Joins in Oracle
1.
Inner Join:
Inner join returns
only the matching rows from both the tables.
Syntax:
Select Columnname, Columnname
From Table1
Inner Join Table2 ON
Table1.CommonColumnname=Table2.CommonColumnname;
Here Table1 and Table2
are the tables which are to be joined. Column1 and Cloumn2 are the columns
selected to retrieve data. The Common column name is used to join the table1 and table2. The columns should be connected with primary
key and foreign key.
One most important
condition to use inner join is the rows should be matched in both the tables.
For Example,
SQL> select
emp_name, emp_role
2 from
employee
3Inner Join employee_details On
employee.emp_id = employee_details.emp_id;
EMP_NAME EMP_ROLE
--------------------
--------------------
Rohan Sales
Rakesh Marketing
Radha Acounts
Rakesh IT
Here Inner Join is
performed on Employee table and Employee_details table. The emp_name and
Emp_role columns from both the tables are selected to retrieve data. Inner Join
is applied on Employee and Employee_details by using similar column from both
the table i.e. emp_id column. So data for this column from both the table if
matches then only Inner Join return the rows.
In this example
Another Example,
SQL>
select designation, Emp_role
2 from
employee
3Inner Join employee_details on
Employee.emp_id=employee_details.emp_id;
DESIGNATION EMP_ROLE
---------------
--------------------
Clerk Sales
manager Marketing
manager Acounts
manager IT
Another Example,
SQL>
select designation, Emp_role
2 from
employee_details
3
Inner Join employee on Employee_details.emp_id=employee.emp_id;
DESIGNATION EMP_ROLE
---------------
--------------------
Clerk Sales
manager Marketing
manager Acounts
manager IT
Another Example,
SQL>
select emp_role,designation
2 from
employee_details
3
Inner Join employee on Employee_details.emp_id=employee.emp_id;
EMP_ROLE DESIGNATION
--------------------
---------------
Sales Clerk
Marketing manager
Acounts manager
IT manager
SQL> select * from student;
ROLLNO NAME CLASS DI
---------- ---------- ---------- --
1 MAnisha BCAI A
2 Monisa BCAII B
3 Mahesh BCAIII C
4 Manthan BBAIII C
SQL> select * from result;
ROLLNO INT_MARKS EXT_MARKS GRADE RESULT
---------- ---------- ---------- ---------- ----------
1 29 67 A Pass
2 26 65 A Pass
3 23 55 B Pass
4 23 45 C Pass
Following query represent INNER JOIN
SQL> select name,int_marks,ext_marks, grade, result
2 from student
3 inner join result
4 on
5 student.rollno=result.rollno;
NAME INT_MARKS EXT_MARKS GRADE RESULT
---------- ---------- ---------- ---------- ----------
MAnisha 29 67 A Pass
Monisa 26 65 A Pass
Mahesh 23 55 B Pass
Manthan 23 45 C Pass
So her Name is retrieved from Student table and Int_marks, Ext_marks, Grade and result is retrieved from Result table by joing Student and Result tables on common column name is Rollno.
1. 2. Natural Join Or USING Clause:
The
natural join is used to join two tables with all the columns from both the
tables. Natural Joins automatically join the table based on columns with
matching names.
Syntax:
Select * from Table1
Natural Join Table2;
SQL> select * from student;
ROLLNO
NAME CLASS DI
---------- -------------------- ---------- --
1 MAnisha BCAI A
2 Monisa BCAII B
3 Mahesh BCAIII C
4 Manthan BBAIII C
SQL> select * from result;
ROLLNO INT_MARKS
EXT_MARKS GRADE RESULT
---------- ---------- ---------- ---------- ----------
1 29
67 A Pass
2 26
65 A Pass
3 23
55 B
Pass
4 23
45
C Pass
Following query shows Natural Join on Student table
and Result table
SQL> select *
from student
2
natural join result;
ROLLNO
NAME CLASS DI INT_MARKS
EXT_MARKS GRADE RESULT
----------
---------- ---------- -- ---------- ---------- ---------- ----------
1 MAnisha BCAI A 29 67 A Pass
2 Monisa BCAII B 26 65 A Pass
3 Mahesh BCAIII C
23 55 B Pass
4 Manthan BBAIII
C 23 45
C Pass
All columns of
student table and all columns of result table are joined using Natural Join. It
combines all the rows of student table and result table.
Using Clause:
USING Clause is
used to join two tables and combine all the rows from joined tables.
Syntax:
Select * from Table1
Join Table2
Using(Common_columnname);
For Example
SQL> select * from student
2 join result
3 using (Rollno);
ROLLNO
NAME CLASS DI
INT_MARKS EXT_MARKS GRADE RESULT
---------- ---------- ---------- -- ----------
---------- ---------- ----------
1
MAnisha BCAI A 29 67 A Pass
2
Monisa BCAII B
26 65 A Pass
3
Mahesh BCAIII C
23 55 B Pass
4
Manthan BBAIII C
23 45 C Pass
All rows are selected from student and result table by applying Using clause using Rollno the common column name from both the tables.
1.
Left Join / Left
Outer Join:
Left join also
called as Left Outer Join is a join operation used to retrieve all the rows
from Left table i.e. Table1. Or the table Left side of the Join keyword and
matching rows from the right table i.e. Table2 or table at the right side of
the join keyword. If there is no matching found in right table, the null values
are returned for the columns of the right table. We can also use Left join to
retrieve and combine data from two tables while it includes all the rows from
one of these tables.
Left Join returns all the rows from left table i.e.
table1 and matching rows from right table i.e. table2, if matching row does not
found in right table then it returns null.
Syntax:
Select
Columns
From
Left_Table
Left
Join Right_Table
On
Left_Table.column=Right_Table.column;
For Example,
Following is Our data,
SQL> select * from student;
ROLLNO
NAME CLASS DI
---------- ---------- ---------- --
1 MAnisha BCAI A
2 Monisa
BCAII B
3 Mahesh
BCAIII
C
4 Manthan BBAIII C
5 Akash BBAI F
SQL> Select * from result;
ROLLNO INT_MARKS
EXT_MARKS GRADE RESULT
---------- ---------- ---------- ---------- ----------
1 29 67
A Pass
2 26 65
A Pass
3 23 55 B Pass
4 23 45
C Pass
SQL>select
student.rollno, student. name, student.class, result.int_marks, result.ext_marks
2 from
student
3 Left
Join Result
4 On
student.Rollno=Result.Rollno;
ROLLNO NAME CLASS INT_MARKS EXT_MARKS
----------
---------- ---------- ---------- ----------
1 MAnisha BCAI 29 67
2 Monisa BCAII 26 65
3 Mahesh BCAIII 23
55
4 Manthan BBAIII 23
45
5 Akash BBAI
In above query Left join is applied on Student and
Result table. In student total 5 rows are there and all 5 rows are retrieved
but these rows do not match in result table i.e. in right table so it will
return Null for those rows.
Another way,
SQL> select
name,class,div, int_marks,Ext_marks, result
2 from
student
3 left
join result
4 on
5
student.rollno=result.rollno;
NAME CLASS DI INT_MARKS
EXT_MARKS RESULT
----------
---------- -- ---------- ---------- ----------
MAnisha BCAI
A 29 67 Pass
Monisa BCAII
B 26 65 Pass
Mahesh BCAIII
C 23
55 Pass
Manthan BBAIII
C 23 45 Pass
Akash BBAI
F
2.
Right Join / Right
Outer Join:
The
right join or right outer join returns all the rows from right table i.e.
second table and only the matching rows from left table i.e. first table. If no
match found, then null values are returned by the query.
Syntax:
Select Columns
From Left_Table
Right Join Right_Table
On Left_Table.column=Left_Table.Column;
For
Example,
SQL> select * from student;
ROLLNO NAME CLASS DI
----------
---------- ---------- --
1 MAnisha BCAI
A
2 Monisa BCAII
B
3 Mahesh BCAIII
C
4
Manthan BBAIII C
5 Akash BBAI
F
SQL>
Select * from result;
ROLLNO
INT_MARKS EXT_MARKS GRADE RESULT
----------
---------- ---------- ---------- ----------
1 29 67 A Pass
2 26 65 A Pass
3 23 55
B Pass
4 23 45
C Pass
SQL>
select name,class,div, int_marks,Ext_marks, grade,result
2 from
student
3
Right join result
4 on
5
student.rollno=result.rollno;
NAME CLASS DI INT_MARKS EXT_MARKS GRADE RESULT
----------
---------- -- ---------- ---------- ---------- ----------
MAnisha BCAI A 29 67 A Pass
Monisa BCAII B
26 65 A Pass
Mahesh BCAIII C
23 55 B Pass
Manthan BBAIII C
23 45 C Pass
In
above query the Right join is applied on student and result table. The query returns
all the rows from right table i.e. second and only matching rows from the left
table i.e. first table.
Another
Way,
SQL>
select student.rollno,name,class,div, int_marks,Ext_marks, grade,result
2 from
student
3
Right join result
4 on
5
student.rollno=result.rollno;
ROLLNO NAME CLASS DI
INT_MARKS EXT_MARKS GRADE RESULT
----------
---------- ---------- -- ---------- ---------- ---------- ----------
1 MAnisha BCAI A
29 67 A Pass
2 Monisa BCAII B
26 65 A Pass
3 Mahesh BCAIII
C 23 55 B Pass
4 Manthan BBAIII C 23 45 C Pass
If you want to retrieve the column which is common in both the tables then it is selected in select clause using table name and dot operator.
5. Full Join or Full
Outer Join:
The full outer or
full join returns all the rows from both the tables i.e. from the left and from
the right table.
The main
difference between left join, right join and full join are as follows:
Left Join: This query returns all the
rows from left table and only matching rows from right table, if matching does
not found then it returns null.
Right Join: The right join
query returns all the rows from right table and only matching rows from left
table, if matching does not found returns Null
Full Join:
The full outer
join returns all the rows from both the tables i.e. left table and right table.
All rows from both the tables including matching and Non matching. For Non
matching return the null values.
Syntax:
Select columns
From LeftTable
Full Join RightTable
ON
LeftTable.column=RightTable.Column;
For Example;
SQL> select
student.rollno,name, class, int_marks,Ext_marks, Grade
2 from
result
3 Full
Join student
4 on
5
result.rollno=student.rollno;
ROLLNO NAME CLASS INT_MARKS EXT_MARKS GRADE
----------
---------- ---------- ---------- ---------- ----------
1 MAnisha BCAI 29 67 A
2 Monisa BCAII 26 65 A
3 Mahesh BCAIII 23 55 B
4 Manthan BBAIII 23 45 C
5 Sakshi BCAII
6. Cross Join Cartesian
Join:
The Cartesian join returns Cartesian product rows from
both the tables. For Cross Join it doesnot require any condition. It helps to
combine all the rows from two or more tables, where each row from first table
is combined with every row of second table and so on.
Cross join produces a result set that contains the
multiplication of the number of rows in each joined tables.
Syntax:
Select
Columns
From
Table1
Cross
Join Table2;
For example;
SQL> select student.rollno, name,class,
Int_marks,Ext_marks,grade
2 from student
3 Cross join result;
ROLLNO
NAME CLASS INT_MARKS EXT_MARKS GRADE
---------- ---------- ---------- ---------- ----------
----------
1
MAnisha BCAI 29 67 A
2
Monisa BCAII 29 67 A
3
Mahesh BCAIII 29 67 A
4
Manthan BBAIII 29 67 A
1
MAnisha BCAI 26 65 A
2
Monisa BCAII 26 65 A
3
Mahesh BCAIII 26 65 A
4
Manthan BBAIII 26 65 A
1
MAnisha BCAI 23 55 B
2
Monisa BCAII 23 55 B
3
Mahesh BCAIII 23 55 B
ROLLNO
NAME CLASS INT_MARKS EXT_MARKS GRADE
---------- ---------- ---------- ---------- ----------
----------
4 Manthan
BBAIII 23
55 B
1 MAnisha
BCAI 23
45 C
2 Monisa
BCAII 23 45 C
3 Mahesh
BCAIII 23 45 C
4 Manthan
BBAIII 23
45 C
16 rows selected.
In above query in student and result 4 rows are there.
When cross join is applied on both tables, each column of student table i.e.
first table is combined with every column of second table i.e. result table. In
this way 16 rows are displayed on screen.
0 टिप्पण्या
कृपया तुमच्या प्रियजनांना लेख शेअर करा आणि तुमचा अभिप्राय जरूर नोंदवा. 🙏 🙏