21. Connection conditions and link types for Oracle database SQL development
* * welcome to reprint, reprint please indicate the source: blog.csdn.net/notbaron/ar… 六四事件
Connection conditions can be divided into two categories: equal and unequal connections.
There are three different types of connections:
Inner join: A row is returned only if the column in the join contains a value that meets the join criteria. If one of the join criteria columns in a row is null, the row is not returned. The examples we’ve seen so far are inner joins.
Outer join: Returns a row even if a column in a join condition contains a null value.
Self-join: Returns rows that are joined to the same table
1. Unequal connection
Unequal joins use operators other than the equal operator in a join.
SQL>select e.first_name,e.last_name,e.title,e.salary,sg.salary_grade_id fromemployees e,salary_grades sg
where e.salary between sg.low_salary andsg.high_salary order by salary_grade_id;
FIRST_NAME LAST_NAME TITLE SALARY SALARY_GRADE_ID
———- ———- —————————— —————
Fred Hobbs Salesperson 150000 1
Susan Jones Salesperson 500000 2
Ron Johnson Sales Manager 600000 3
James Smith CEO 800000 4
Find the pay grade within the range.
2. The outer join
The outer join returns a row even if the column in the join contains a null value. Oracle-specific outer join operator is the plus sign (+) enclosed in parentheses.
Such as:
SQL> select p.name,pt.name fromproducts p,product_types pt where p.product_type_id=pt.product_type_id(+) orderby p.name;
NAME NAME
—————————————-
2412: The Return Video
Chemistry Book
Classical Music CD
Creative Yell CD
From AnotherPlanet DVD
Modern Science Book
My Front Line
Pop 3 CD
Space Force 9 DVD
Supernova Video
Tank War Video
NAME NAME
—————————————-
Z Files Video
12 rowsselected.
The outer join operator can be placed on either side of the join operator, but it should generally be placed on the opposite side of the null-containing column in the row you want to retrieve.
SQL>select p.name,pt.name from products p,product_types pt wherept.product_type_id(+)=p.product_type_id order by p.name;
NAME NAME
—————————— ———-
2412: The Return Video
Chemistry Book
Classical Music CD
Creative Yell CD
From Another Planet DVD
Modern Science Book
My Front Line
Pop 3 CD
Space Force 9 DVD
Supernova Video
Tank War Video
NAME NAME
—————————— ———-
Z Files Video
12 ows selected.
2.1 Left external connection
In the left outer join, the outer join operator is actually to the right of the equal operator.
2.2 Right outer connection
To perform a right outer join, place the Oracle outer join operator to the left of the equal operator.
2.3 External connection restrictions
An outer join can only use the outer join operator on one end, not both.
You cannot use both the outer join condition and the IN operator.
You cannot use one outer join condition and another that uses the OR operator at the same time.
For details, see OracleDatabase SQL Reference.
3. Since the connection
A self-join is a join to the same table. To perform a self-join, a different table alias must be used to identify each reference to the table in the query.
SQL> select w.first_name || ‘ ‘|| w.last_name || ‘ works for ‘|| m.first_name || ‘ ‘ || m.last_name fromemployees w,employees m
where w.manager_id = m.employee_id order byw.first_name;
W.FIRST_NAME||”||W.LAST_NAME||’WORKSFOR’||M.FIRST_NA
—————————————————–
Fred Hobbs works for Ron Johnson
Ron Johnson works for James Smith
Susan Jones works for Ron Johnson
James Smith is not shown because manager_id is empty. No display.
Use outer join and self – join. Using outer join on top of self join, you can see the record of James Smith.
As follows:
SQL>select w.last_name || ‘ works for ‘|| nvl(m.last_name ,’ theshareholders’) from employeesw,employees m
where w.manager_id = m.employee_id(+) orderby w.last_name;
W.LAST_NAME||’WORKSFOR’||NVL(M.LAST_NA
————————————–
Hobbs works for Johnson
Johnson works for Smith
Jones works for Johnson
Smith works for the shareholders