SQL Joins- Tutorial using Oracle
Create a student table
SQL> create table student(id number, name varchar2(10),address varchar2(10),gender varchar2(10),sid number, class_repid number);
Output
Table created.
Insert records into the student table
SQL> insert into student values(&id,'&name','&address','&gender',&sid,&classrep_id);
Enter value for id: 422
Enter value for name: ram
Enter value for address: mumbai
Enter value for gender: male
Enter value for sid: 112
Enter value for classrep_id: 422
old 1: insert into student values(&id,'&name','&address','&gender',&sid,&classrep_id)
new 1: insert into student values(422,'ram','mumbai','male',112,422)
1 row created.
SQL> /
Enter value for id: 423
Enter value for name: tom
Enter value for address: pune
Enter value for gender: male
Enter value for sid: 113
Enter value for classrep_id: 22
old 1: insert into student values(&id,'&name','&address','&gender',&sid,&classrep_id)
new 1: insert into student values(423,'tom','pune','male',113,22)
1 row created.
SQL> /
Enter value for id: 425
Enter value for name: harini
Enter value for address: uk
Enter value for gender: female
Enter value for sid: ''
Enter value for classrep_id: 422
old 1: insert into student values(&id,'&name','&address','&gender',&sid,&classrep_id)
new 1: insert into student values(425,'harini','uk','female','',422)
1 row created.
SQL> /
Enter value for id: 427
Enter value for name: reema
Enter value for address: pune
Enter value for gender: female
Enter value for sid: ''
Enter value for classrep_id: 422
old 1: insert into student values(&id,'&name','&address','&gender',&sid,&classrep_id)
new 1: insert into student values(427,'reema','pune','female','',422)
1 row created.
Viewing the records in the student table
SQL> select * from student;
ID NAME ADDRESS GENDER SID CLASS_REPID
---------- ---------- ---------- ---------- ---------- -----------
422 ram mumbai male 112 422
423 tom pune male 113 22
425 harini uk female 422
427 reema pune female 422
SQL> /
ID NAME ADDRESS GENDER SID CLASS_REPID
---------- ---------- ---------- ---------- ---------- -----------
422 ram mumbai male 112 422
423 tom pune male 113 22
425 harini uk female 422
427 reema pune female 422
SQL> insert into student values(&id,'&name','&address','&gender',&sid,&classrep_id);
Enter value for id: 426
Enter value for name: chris
Enter value for address: us
Enter value for gender: male
Enter value for sid: ''
Enter value for classrep_id: 422
old 1: insert into student values(&id,'&name','&address','&gender',&sid,&classrep_id)
new 1: insert into student values(426,'chris','us','male','',422)
1 row created.
SQL> /
Enter value for id: 421
Enter value for name: satish
Enter value for address: vellore
Enter value for gender: male
Enter value for sid: 111
Enter value for classrep_id: 422
old 1: insert into student values(&id,'&name','&address','&gender',&sid,&classrep_id)
new 1: insert into student values(421,'satish','vellore','male',111,422)
1 row created.
View the Student table
SQL> select * from student;
Output
ID NAME ADDRESS GENDER SID CLASS_REPID
---------- ---------- ---------- ---------- ---------- -----------
422 ram mumbai male 112 422
423 tom pune male 113 22
425 harini uk female 422
427 reema pune female 422
426 chris us male 422
421 satish vellore male 111 422
6 rows selected.
Creating the school table
SQL> create table school(sid number, sname varchar2(10),slocation varchar2(10));
Output
Table created.
Inserting records in the School table
SQL> insert into school values (&sid,'&sname','&slocation');
Enter value for sid: 112
Enter value for sname: sense
Enter value for slocation: tt
old 1: insert into school values (&sid,'&sname','&slocation')
new 1: insert into school values (112,'sense','tt')
1 row created.
SQL> /
Enter value for sid: 113
Enter value for sname: sbst
Enter value for slocation: smv
old 1: insert into school values (&sid,'&sname','&slocation')
new 1: insert into school values (113,'sbst','smv')
1 row created.
SQL> 114
SP2-0226: Invalid line number
SQL> /
Enter value for sid: 114
Enter value for sname: select
Enter value for slocation: tt
old 1: insert into school values (&sid,'&sname','&slocation')
new 1: insert into school values (114,'select','tt')
1 row created.
SQL> /
Enter value for sid: 115
Enter value for sname: smec
Enter value for slocation: mb
old 1: insert into school values (&sid,'&sname','&slocation')
new 1: insert into school values (115,'smec','mb')
1 row created.
SQL> /
Enter value for sid: 111
Enter value for sname: scope
Enter value for slocation: sjt
old 1: insert into school values (&sid,'&sname','&slocation')
new 1: insert into school values (111,'scope','sjt')
1 row created.
Viewing the records in the school table
SQL> select * from school;
Output
SID SNAME SLOCATION
---------- ---------- ----------
112 sense tt
113 sbst smv
114 select tt
115 smec mb
111 scope sjt
Joining student and school table using Equi Join
SQL> select s.name,sch.sname from student s,school sch where s.sid = sch.sid;
Output
NAME SNAME
---------- ----------
ram sense
tom sbst
satish scope
Self Join Example
SQL> select s1.name,s2.name from student s1,student s2 where s1.class_repid = s2.id;
Output
NAME NAME
---------- ----------
satish ram
chris ram
reema ram
harini ram
ram ram
Non Equi Join Example - Employee and Salary details table
SQL> select * from employee;
ID NAME DESIGNATION SALARY DEP_NO
---------- -------------------- -------------------- ---------- ----------
111 satish sharma Associate 10000 421
112 ram Associate 20000 421
113 tom Assistant 5000 422
114 jeff Assistant 2000 422
115 mohan professor 50000 422
116 venkat professor 70000 422
6 rows selected.
SQL> select * from salary_details;
GRADE LOW_SALARY HIGH_SALARY
---------- ---------- -----------
1 1000 5000
2 5001 30000
3 30001 500000
Computing the salary grade using non equi join
SQL> select e.name,e.salary,g.grade from employee e,salary_details g where e.salary between g.low_salary and g.high_salary;
Output
NAME SALARY GRADE
-------------------- ---------- ----------
jeff 2000 1
tom 5000 1
satish sharma 10000 2
ram 20000 2
mohan 50000 3
venkat 70000 3
Natural Join between two tables - student and school table
SQL> select * from student natural join school;
Output
SID ID NAME ADDRESS GENDER CLASS_REPID SNAME
---------- ---------- ---------- ---------- ---------- ----------- ----------
SLOCATION
----------
112 422 ram mumbai male 422 sense
tt
113 423 tom pune male 22 sbst
smv
111 421 satish vellore male 422 scope
sjt
Cross join between two tables - test1 and test2
Output
SQL> select * from test1;
ID NAME
---------- ----------
111 sam
112 ram
SQL> select * from test2;
DID DNAME
---------- ----------
123 scope
124 sbst
SQL> select * from test1 cross join test2;
ID NAME DID DNAME
---------- ---------- ---------- ----------
111 sam 123 scope
111 sam 124 sbst
112 ram 123 scope
112 ram 124 sbst
Left outer join between student and school table
SQL> select student.name,school.sid,school.sname from student left outer join school on student.sid=school.sid;
Output
NAME SID SNAME
---------- ---------- ----------
ram 112 sense
tom 113 sbst
satish 111 scope
chris
reema
harini
Left outer join another syntax
SQL> select student.name,school.sid,school.sname from student,school where student.sid=school.sid(+);
Output
NAME SID SNAME
---------- ---------- ----------
ram 112 sense
tom 113 sbst
satish 111 scope
chris
reema
harini
Right Outer Join between student and school table
SQL> select student.name,school.sid,school.sname from student,school where student.sid(+)=school.sid;
Output
NAME SID SNAME
---------- ---------- ----------
ram 112 sense
tom 113 sbst
satish 111 scope
114 select
115 smec
Another Syntax for right outer join
SQL> select student.name,school.sid,school.sname from student right outer join school on student.sid=school.sid;
Output
NAME SID SNAME
---------- ---------- ----------
ram 112 sense
tom 113 sbst
satish 111 scope
114 select
115 smec
Full outer join between student and school table
SQL> select student.name,school.sid,school.sname from student left outer join school on student.sid=school.sid
2 union
3 select student.name,school.sid,school.sname from student right outer join school on student.sid=school.sid;
Output
NAME SID SNAME
---------- ---------- ----------
chris
harini
ram 112 sense
reema
satish 111 scope
tom 113 sbst
114 select
115 smec
8 rows selected.