In relational database systems, information is stored in multiple tables. You joins multiple tables to get information. Standart SQL, has defined join to combine multiple set based on desired attributes.
I simply demonstrate a complete example to show how to joins take place in Oracle.
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> drop table student;
Table dropped
SQL> drop table project;
Table dropped
SQL> drop table course;
Table dropped
SQL> create table student(student_id number, taken_project_id number, given_course_id number, last_name varchar2(32) );
Table created
SQL> create table project(project_id number, project_description varchar2(32));
Table created
SQL> create table course(course_id number, course_description varchar2(32));
Table created
SQL> INSERT INTO course values(1, 'Database Management Sys');
1 row inserted
SQL> INSERT INTO course values(2, 'Logic');
1 row inserted
SQL> INSERT INTO course values(3, 'Circuit Theory');
1 row inserted
SQL> INSERT INTO project values(100, 'Reservation System');
1 row inserted
SQL> INSERT INTO project values(101, 'Online Booking');
1 row inserted
SQL> INSERT INTO project values(102, 'Face Recog. with brute force');
1 row inserted
SQL> INSERT INTO project values(103, 'On Fly Form Generator');
1 row inserted
SQL> INSERT INTO student values(60001, null, null, 'Alonso');
1 row inserted
SQL> INSERT INTO student values(60002, 102, 1, 'Richar');
1 row inserted
SQL> INSERT INTO student values(60001, 100, 2, 'Tekbir');
1 row inserted
SQL> INSERT INTO student values(60001, null, 1, 'Sergio');
1 row inserted
SQL> INSERT INTO student values(60001, 101, null, 'Alexov');
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT * FROM student;
STUDENT_ID TAKEN_PROJECT_ID GIVEN_COURSE_ID LAST_NAME
---------- ---------------- --------------- --------------------------------
60001 Alonso
60002 102 1 Richar
60001 100 2 Tekbir
60001 1 Sergio
60001 101 Alexov
SQL> SELECT * FROM project;
PROJECT_ID PROJECT_DESCRIPTION
---------- --------------------------------
100 Reservation System
101 Online Booking
102 Face Recog. with brute force
103 On Fly Form Generator
SQL> SELECT * FROM course;
COURSE_ID COURSE_DESCRIPTION
---------- --------------------------------
1 Database Management Sys
2 Logic
3 Circuit Theory
SQL> SELECT st.last_name, pr.project_description FROM student st, project pr WHERE st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Richar Face Recog. with brute force
Tekbir Reservation System
Alexov Online Booking
SQL> SELECT st.last_name, pr.project_description FROM student st INNER JOIN project pr ON st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Richar Face Recog. with brute force
Tekbir Reservation System
Alexov Online Booking
SQL> SELECT st.last_name, pr.project_description FROM student st, project pr WHERE st.taken_project_id = pr.project_id(+);
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Tekbir Reservation System
Alexov Online Booking
Richar Face Recog. with brute force
Sergio
Alonso
SQL> SELECT st.last_name, pr.project_description FROM student st LEFT OUTER JOIN project pr ON st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Tekbir Reservation System
Alexov Online Booking
Richar Face Recog. with brute force
Sergio
Alonso
SQL> SELECT st.last_name, pr.project_description FROM student st, project pr WHERE st.taken_project_id(+) = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Richar Face Recog. with brute force
Tekbir Reservation System
Alexov Online Booking
On Fly Form Generator
SQL> SELECT st.last_name, pr.project_description FROM student st RIGHT OUTER JOIN project pr ON st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Richar Face Recog. with brute force
Tekbir Reservation System
Alexov Online Booking
On Fly Form Generator
SQL> --SELECT st.last_name, pr.project_description FROM student st, project pr WHERE st.taken_project_id(+) = pr.project_id(+);--ERROR
SQL> SELECT st.last_name, pr.project_description FROM student st FULL OUTER JOIN project pr ON st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Tekbir Reservation System
Alexov Online Booking
Richar Face Recog. with brute force
Sergio
Alonso
On Fly Form Generator
6 rows selected
SQL> SELECT st.last_name, pr.project_description, co.course_description FROM student st, project pr, course co WHERE st.taken_project_id = pr.project_id AND st.given_course_id = co.course_id;
LAST_NAME PROJECT_DESCRIPTION COURSE_DESCRIPTION
-------------------------------- -------------------------------- --------------------------------
Richar Face Recog. with brute force Database Management Sys
Tekbir Reservation System Logic
SQL> SELECT st.last_name, pr.project_description, co.course_description FROM student st INNER JOIN project pr ON st.taken_project_id = pr.project_id INNER JOIN course co ON st.given_course_id = co.course_id;
LAST_NAME PROJECT_DESCRIPTION COURSE_DESCRIPTION
-------------------------------- -------------------------------- --------------------------------
Tekbir Reservation System Logic
Richar Face Recog. with brute force Database Management Sys
SQL> SELECT st.last_name, pr.project_description, co.course_description FROM student st FULL OUTER JOIN project pr ON st.taken_project_id = pr.project_id FULL OUTER JOIN course co ON st.given_course_id = co.course_id;
LAST_NAME PROJECT_DESCRIPTION COURSE_DESCRIPTION
-------------------------------- -------------------------------- --------------------------------
Sergio Database Management Sys
Richar Face Recog. with brute force Database Management Sys
Tekbir Reservation System Logic
On Fly Form Generator
Alonso
Alexov Online Booking
Circuit Theory
7 rows selected
SQL>
No comments:
Post a Comment