View creation for associated tables in Oracle

these days of work basically did not compare the new technical point, is the repeated changes in demand, modify Bug ... Of course, it's time to think about the importance of demand analysis in a huge project, as well as the importance of the requirements specification. We are in a vague demand, document lack of conditions, repeated changes have become commonplace, today the leadership said this function should be so, we will immediately change to this, the next day the leader said again the original, we can only change back ... Sometimes we also tease ourselves that we are typical for leadership development ...

Today there is no more new technical points to summarize, I reviewed, these days of work, found in the association table when creating the view is not particularly familiar with the connection, so take this opportunity to review:

1 , Inner joins (typical join operations, using comparison operators like = or <>). Includes equality joins and natural joins. Inner joins use comparison operators to match rows in two tables based on the values of the columns that are common to each table.  For example, retrieve all lines of the students and courses table with the same student identification number.

2, outer joins. An outer join can be a left outer join, a right outer join, or a full outer join.

When you specify an outer join in the FROM clause, you can specify it by one of the following sets of keywords:

1) left JOIN or left OUTER join

The result set for a left outer join includes all rows of the left table specified in the Leftouter clause, not just the rows that match the joined columns. If a row in the left table does not have a matching row in the right table, all select list columns in the right table in the associated result set row are null values.

2) Right Join or right OUTER join

A right outer join is a reverse join of a left outer join. All rows of the right table will be returned. If a row in the right table does not have a matching row in the left table, a null value will be returned for left table.

3) Full join or full OUTER join

A full outer join returns all rows from the left and right tables. When a row does not have a matching row in another table, the selection list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.

3. Cross Join

A cross join returns all the rows in the left table, with each row in the left table combined with all the rows in the right table. Cross joins are also called Cartesian product.

The table or view in the FROM clause can be specified in any order by an inner join or a full outer join, but the order of the table or view is important when you specify a table or view with a left or right outer join. For more information about using left or right outward joins to arrange tables, see Using outer joins.

The following is an example of a left connection of three tables written in the disaster prevention system, for reference:

<span style= "FONT-SIZE:24PX;" >create or replace view v_dp_b_limitratesegement asselect S_id,s_linename,s_linecode,s_monitoringunitcode,s_ Monitoringpointcode,s_areacode,s_areaname,s_startkilometer,s_startmeter,s_endkilometer,s_endmeter,c_tagspeed,s _tagspeed,i_sortid,c_ableflag, S_basestationname,kilometer from (select T1.s_id,t1.s_linename,t1.s_linecode,t1 . s_monitoringunitcode,t1.s_monitoringpointcode,s_areacode,s_areaname,s_startkilometer,s_startmeter,s_ Endkilometer,s_endmeter,c_tagspeed,s_tagspeed,t1.i_sortid,t1.c_ableflag, T2.s_basestationname, (' K ' | | t3.i_km | | '+' || T3.i_meter) kilometer from Dp_b_processsegment T1 left joins Dp_b_basestationinfo T2 on t1.s_monitoringunitcode= t2.s _id left joins Dp_b_monitoringpointinfo T3 on t1.s_monitoringpointcode=t3.s_id) T;</span> 

Summary : The above summary is mainly to be able to master the view of the creation method, the content is relatively simple, but also want to give people in need to inspire ...

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

View creation for associated tables in Oracle