-
Notifications
You must be signed in to change notification settings - Fork 56
/
Day-31_SQL_JOINS(INNER,LEFT,RIGHT).txt
183 lines (139 loc) · 6.15 KB
/
Day-31_SQL_JOINS(INNER,LEFT,RIGHT).txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
"Welcome To Ashok IT"
"Oracle Database"
Topic : Inner Join & Left Join & Right Join
Date : 3/01/2023
(Session - 31)
_____________________________________________________________________________________________________________________________
Important Information
*********************
>> Oracle Class Notes ::: https://github.com/ashokitschool/ORACLE_CLASS_NOTES
>> Class Recording ::: Will be available through Ashok IT Portal
>> Class Related Updates "Join In WhatsApp Group" check with Admin Team.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Today Session : Let us Consider the below two tables for better understanding about Inner Join,Left Join,Right Join
====================================================================================================================
**********************************************************************
ashokit_emp >>>> Primary Key(emp_id), Foreign keys(dept_id,manager_id)
**********************************************************************
emp_id emp_name salary dept_id manager_id
------ -------- ------ ------- ----------
E1 Anil 18000 D7 M1
E2 Avinash 28000 D1 M2
E3 Raju 38000 D2 M3
E4 Raghavendra 48000 D4 M6
E5 Abdul 58000 D6 M5
E6 Gupta 68000 D1 M2
E7 Bhavya 78000 D7 M1
E8 SUNIL 88000 D11 M1
E9 ABHI 98000 D12 M2
***************************************
ashokit_dept >>>>> Primary Key(dept_id)
***************************************
dept_id dept_name
------- --------
D1 IT
D2 HR
D3 FINANCE
D4 ADMIN
D5 NON-ADMIN
D6 MARKETING
D7 SALES
Inner Join
==========
* When we are joining of two tables with join keyword (or)inner join keyword such join we can call it is an "Inner Join (or) Equi Join".
* When we want to join two tables definetly we required common column's between two tables.
* Inner join will always selected matched records from left hand side table and right hand side table.
Q) Display emp details and department details
-- inner join
select ae.emp_id,ae.emp_name,ae.dept_id,ad.dept_id,ad.dept_name
from ashokit_emp ae join ashokit_dept ad
on ae.dept_id=ad.dept_id
order by ae.emp_id;
OUTPUT
======
E1 Anil D7 D7 SALES
E2 Avinash D1 D1 IT
E3 Raju D2 D2 HR
E4 Raghavendra D4 D4 ADMIN
E5 Abdul D6 D6 MARKETING
E6 Gupta D1 D1 IT
E7 Bhavya D7 D7 SALES
Left Join
=========
* When we are joining the two tables using "left join (or) left outer join" word such join called as "Left Join".
* When we are working with Left Join (or) Left Outer Join
1) First it will perform "Inner Join" on two tables i.e.,matched records from both tables(which are matched with join condition)
2) Additional records from left hand side table which are not matched with Join condition.
NOTE
====
In this type of Join always left side table data is important
Q) Display All the employees and its departments information
-- left join
select ae.emp_id,ae.emp_name,ae.dept_id,ad.dept_id,ad.dept_name
from ashokit_emp ae left outer join ashokit_dept ad
on ae.dept_id=ad.dept_id
order by ae.emp_id;
OUTPUT
======
E1 Anil D7 D7 SALES
E2 Avinash D1 D1 IT
E3 Raju D2 D2 HR
E4 Raghavendra D4 D4 ADMIN
E5 Abdul D6 D6 MARKETING
E6 Gupta D1 D1 IT
E7 Bhavya D7 D7 SALES
E8 SUNIL D11 null null ------ unmatched records from right table
E9 ABHI D12 null null ------ unmatched records from right table.
Right Join
==========
* When we are joining the two tables using "Right join (or) Right outer join" word such join called as "Right Join".
* When we are working with RightJoin (or) Right Outer Join
1) First it will perform "Inner Join" on two tables i.e.,matched records from both tables(which are matched with join condition)
2) Additional records from Right hand side table which are not matched with Join condition.
NOTE:
=====
* In this type of Join Right Hand side table data is important
Q) Display Employee and all departments related information
-- right join
select ae.emp_id,ae.emp_name,ae.dept_id,ad.dept_id,ad.dept_name
from ashokit_emp ae right outer join ashokit_dept ad
on ae.dept_id=ad.dept_id
order by ae.emp_id;
OUTPUT
======
E1 Anil D7 D7 SALES
E2 Avinash D1 D1 IT
E3 Raju D2 D2 HR
E4 Raghavendra D4 D4 ADMIN
E5 Abdul D6 D6 MARKETING
E6 Gupta D1 D1 IT
E7 Bhavya D7 D7 SALES
null null null D5 NON-ADMIN ---> Non Matched Records from Left tables we are getting null values
null null null D3 FINANCE from left tables columns
Inner Join >>>>>>>>>>> Matched Records from both tables
Left Join >>>>>>>>>>>>> Inner Join + Additional Records from Left table
Right Join >>>>>>>>>>>> Inner Join + Additional Records from Right table
Q) Display all employees(emp_id,emp_name) and their departments(dept_name) and their managers(manager_name) and their projects(project_name)
-- Query
select ae.emp_id,ae.emp_name,ad.dept_name,am.manager_name,ap.project_name
from
ashokit_emp ae left join ashokit_dept ad on ae.dept_id = ad.dept_id
left join ashokit_managers am on am.manager_id = ae.manager_id
left join ashokit_projects ap on ap.team_member_id=ae.emp_id
order by ap.project_name;
OUTPUT
======
E6 Gupta IT MAHESH BANKING PROJECT
E3 Raju HR SURESH BANKING PROJECT
E1 Anil SALES ASHOK SPRING PROJECT
E2 Avinash IT MAHESH SPRING PROJECT
E5 Abdul MARKETING RAJESH WEBSITE PROJECT
E4 Raghavendra ADMIN NARESH null
E7 Bhavya SALES ASHOK null
E8 SUNIL null ASHOK null
E9 ABHI null MAHESH null
Observation
==========
4 Employees are not working with any projects
2 Employees are not mapped with correct departments(E8,E9) department names are null.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++