How to user the Oracle View and Joins (Inner and outer)

How to user the Oracle View and Joins (Inner and outer)

 

Recently, I have been studying about the Oracle Database. In this post, I wrote about the basic concept. In this post, I will handle about View and Joins.

 

1. Pre-requisite

 

I will create 2 tables like below.

create table MYADMIN.users ( name varchar2(15), id number(10) ) tablespace myspace; create table MYADMIN.lessons ( id number(10), lessons varchar2(15), day varchar2(15) ) tablespace myspace; insert into MYADMIN.users (name, id) values ( 'ant', 101 ); insert into MYADMIN.users (name, id) values ( 'bee', 102 ); insert into MYADMIN.users (name, id) values ( 'dog', 104 ); insert into MYADMIN.lessons (id, lessons, day) values ( 101, 'math', 'monday' ); insert into MYADMIN.lessons (id, lessons, day) values ( 102, 'art', 'tuseday' ); insert into MYADMIN.lessons (id, lessons, day) values ( 103, 'music', 'friday' ); insert into MYADMIN.lessons (id, lessons, day) values ( 105, 'exec', 'sunday' );

After run these command, I can get the tables with datas

 

Now, I am ready to start.

 

2. Inner and  Right, Left, Full Joins

 

I will follow this instructions. In this instructions, It has more detail. In this post, I will handle the way how to use this. Joins are one of filters with tables. Therefore, I can extract the matched data from tables.

 

2-1. Inner Join

 

As the figure, It shows the result after Inner Join. I can extract the matched data as intersection. This is the command. 

select name from MYADMIN.users inner join MYADMIN.lessons on MYADMIN.users.id = MYADMIN.lessons.id;

This command means that I can extract intersection from both of "MYADMIN.users" table and "MYADMIN.lessons" table.

 

 

2-2. Left outer Join

 

With this option, Left side of tables are the source. This source start to compare data of the Right side of tables. When condition is matched. It extract matched data from both of table. However, the condition is not matched, the data from the source can be obtained but the data from right side of table inserted as the "<null>"

select MYADMIN.users.name, MYADMIN.lessons.day from MYADMIN.users left outer join MYADMIN.lessons on MYADMIN.users.id = MYADMIN.lessons.id;

 

2-3. Right outer Join

This is reverse concept of the left outer join above. At this time, left side of tables is the source. Therefore, the value could be "<null>" from right side of tables, when condition does not match.

select MYADMIN.users.name, MYADMIN.lessons.day from MYADMIN.users right outer join MYADMIN.lessons on MYADMIN.users.id = MYADMIN.lessons.id;

2-4. Full outer Join

With this option, left side and right side can be source. If the column from left side which does not exist in right side, this is the source. If the column from right side which does not exist in left side, this is also the source.  

select MYADMIN.users.name, MYADMIN.lessons.day from MYADMIN.users full outer join MYADMIN.lessons on MYADMIN.users.id = MYADMIN.lessons.id;

 

3. Views

 

I will follow this instruction for this part. Views is the virtual table which is not existed in real. Sometime, I need to combine or make complex database query. In this case, this views is quite useful. For example, I will create view with the query of full outer join.

# create view create view matched_info as select MYADMIN.users.name, MYADMIN.lessons.day from MYADMIN.users full outer join MYADMIN.lessons on MYADMIN.users.id = MYADMIN.lessons.id;   # select for query select * from matched_info;

I have already told that the view is not real table. Thus, I can not find out the information from "user_tables".

However, I can see the view information from "user_views". In the "TEXT", I can see the query which I used to create.

I think "the views is belonged to the schema (=user), even if it is virtual table". I can give some grant option like below.

grant select on MYADMIN.matched_info to MYUSER;

 

After login again with "MYUSER" account, I can show the data from this view also.

However, I can not show any detail information with "user_views" or "user_table".

Because, it only show the data whch is created by "schema". Therefore, if you want to see, you need to search through grant option like below.

If I want delete (drop) the view, Use this command below

drop view MYADMIN.matched_info;

 

Reference 

 

[ 1 ] https://www.techonthenet.com/oracle/joins.php

[ 2 ] https://createnetech.tistory.com/32

[ 3 ] https://www.techonthenet.com/oracle/views.php