-
Notifications
You must be signed in to change notification settings - Fork 9
/
join1.txt
80 lines (61 loc) · 3.69 KB
/
join1.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
inner join or Equi-join
-----------------------------------
table student ----studid,name,address
table course------cid,studid,cname
----------------------------------------------------------------
create table hydstudents(studid int,name varchar2(30),address varchar2(30));
insert into hydstudents values(105,'peter','hydrabad');
select * from hydstudents;
create table hydcourse(cid int,studid int,cname varchar2(30));
insert into hydcourse values(1002,104,'DBMS');
select * from hydcourse;
----------------------------------------------------------------------------------------------
SELECT hydcourse.cid,hydcourse.cname, hydstudents.studid,hydstudents.Name, hydstudents.address FROM hydstudents
INNER JOIN hydcourse ON hydcourse.studID=hydstudents.studID;
-------------------------------------------------------------------------------------------------------
SELECT b.cid,b.cname, a.studid,a.Name, a.address
FROM hydstudents a
INNER JOIN hydcourse b ON b.studID=a.studID;
-----------------------------------------------------------------------------------------------------------------
select a.studid,a.name,a.address,b.cid,b.cname from hydstudents a,hydcourse b where a.studid=b.studid;
------------------------------------------------------------------------------------------------------
select hydstudents.studid,hydstudents.name,hydstudents.address,hydcourse.cid,hydcourse.cname from hydstudents ,hydcourse where hydstudents.studid=hydcourse.studid;
----------------------------------------------------------------------------------------------
select a.studid,a.name,a.address,b.cid,b.cname from hydstudents a,hydcourse b where a.studid=b.studid(+);
------------------------------------------------------------------------------------------------------
SELECT a.Name,a.studid,a.address,b.cid,b.cname
FROM hydstudents a
LEFT JOIN hydcourse b ON a.studid = b.studid;
ORDER BY a.CName;
------------------------------------------------------------------------------------------------------------
RIGHT OUTER JOIN
select a.studid,a.name,a.address,b.cid,b.cname from hydstudents a,hydcourse b where a.studid(+)=b.studid;
--------------------------------------------------------------------------------------------------------------
SELECT a.Name,a.studid,a.address,b.cid,b.cname
FROM hydstudents a
RIGHT JOIN hydcourse b ON a.studid = b.studid;
-------------------------------------------------------------------------------------------------------------
Types of Joins:-
---------------------
(INNER) JOIN: Returns records that have matching values in both tables.
LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table.
RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table.
FULL (OUTER) JOIN: Return all records when there is a match in either left or right table.
--------------------------------------------------------
SELECT a.Name,a.studid,a.address,b.cid,b.cname
FROM hydstudents a
FULL JOIN hydcourse b ON a.studid = b.studid;
--------------------------------------------------------
(emp)empno,name,sal
(job)job,low_sal,high_sal
---------------------------------------
NON-EQUI JOIN
---------------------
CREATE TABLE HYDEMP(empno int,name varchar2(30),salary float);
insert into hydemp values(105,'barun',11500);
select * from hydemp;
create table hydjob(job varchar2(30),lowsal float,highsal float);
insert into hydjob values('sr manager',11000,12000);
select * from hydjob;
select e.name,e.salary,j.job from hydemp e, hydjob j where e.salary between j.lowsal and j.highsal;
------------------------------------------------------------------------------