Oracle left connection and right connection

Document directory

• Internal Connection

References: http://space.itpub.net/11134237/viewspace-617951

Data Table connections include:

1. Inner join (natural join): Only rows matching both tables can appear in the result set.

2. external connections: including

(1) left Outer Join (the table on the left is not restricted), and the right table is null

(2) Right Outer Join (no restriction on the table on the right), left table fill null

(3) Full outer join (no restrictions are imposed on both the left and right tables). The left and right tables fill in NULL for vacant rows.

3. Self-join (the connection occurs in a base table)

select a.studentno, a.studentname, b.classname from students a, classes b where a.classid(+) = b.classid;

The query result is as follows:

Studentno studentnam classname

----------------------------------------

1 Class

2 class B of BB
Null null Class 1, class 3

The preceding statements are right join:

That is, the other side of the "(+)" is the connection direction,All records on the right side of the equal sign are displayed, regardless of whether they are matched on the left.. That is to say, in the above example, no matter whether there is no student in a class, the class name will appear in the query structure. That is, "(+)" indicates the value of the column where it is located. If no matching value exists, a null value is added and displayed.

// When a full set is connected to some sets, a null effect is usually displayed after the columns of some sets with (+.

Otherwise:

select a.studentno, a.studentname, b.classname from students a, classes b where a.classid = b.classid(+);

Query results:

Studentno studentname classname

--------------------------------------------------

1 Class

2 class B of BB

3 AA null

This is left join. no matter whether the student has a matching class number in a class, the student's record will be displayed.

select a.studentno, a.studentname, b.classname from students a, classes b where a.classid = b.classid;

This is usually used.Internal ConnectionTo display records that both tables meet the conditions.

In short,

The left join shows all on the left and the right are the same as those on the left.

The right connection shows all and the left are the same as the right

Only matching conditions are displayed for internal connections!

Example:

Left join

select p.*,g.state  from process_card_procedure p,group_task g where p.process_card_procedure_id=g.process_card_procedure_id(+) and  p.process_card_id=431 and p.procedure_no not like '%C%' order by cast(p.procedure_no as int)

Indicates the left join between the process_card_procedure table and the group_task table. The query result is as follows:

The state field in the Table above is in the group_task table. During the connection process, the following three records do not have the state field. It is displayed as null. If no left join occurs,

Internal Connection

select p.*,g.state  from process_card_procedure p,group_task g where p.process_card_procedure_id=g.process_card_procedure_id and  p.process_card_id=431 and p.procedure_no not like '%C%' order by cast(p.procedure_no as int)

The query result is as follows: