Shulou(Shulou.com)06/01 Report--

This article mainly introduces the use of oracle connection query, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Join query is the most important query in relational database, including inner join, outer join and cross-join and so on. Multiple table queries can be implemented through the join operator.

One Equijoins equivalent connection

An equivalent join is a special case of a conditional join when the join operator is a "=" sign. (relative to non-equivalent connections, such as: between … And condition). It is to select those tuples whose values of the attributes of An and B are equal from the generalized Cartesian product of the relation R and S.

SQL > select e.employee_id, e.last_name, 2 e.department_id, d.department_id, 3 d.location_id 4 from employees e, departments d 5 where e.department_id = d.department_id EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID- 198 OConnell 50 50 1500 199 Grant 50 50 1500 200 Whalen 10 10 1700 201 Hartstein 20 20 1800. Two Self joins self-connection

Self join is a frequently used join method in SQL statements. By using self-join, one mirror of one table can be treated as another table, thus some special data can be obtained.

SELECT e1.last_name | | 'works for' | | e2.last_name "Employees and Their Managers" FROM employees e1, employees e2 WHERE e1.manager_id = e2.employee_id AND e1.last_name LIKE'R% 'ORDER BY e1.lastroomnameInvestyees and Their Managers-- Rajs works for MourgosRaphaely works for KingRogers works for KauflingRussell works for King three Cartesian Products Cartesian product

If two tables join queries without join conditions, oracle returns their Cartesian product. That is, oracle returns the combination of each row in one table and each row in another table (15 X 4).

SCOTT@orcl > select count (1) from emp; COUNT (1)-15SCOTT@orcl > select count (1) from dept; COUNT (1)-4SCOTT@orcl > select count (1) from emp, dept; COUNT (1)-60 four-Inner Joins connection

An inner join, also known as a simple join, is an association of two or more tables and returns only those rows that meet the join criteria.

Select e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM employees e JOIN departments dON e.department_id = d.departmentprincipid; five Outer joins external connections

An extension of a simple connection. It is divided into left external connection (Left outer joins), right external connection (Right outer joins), full external connection (Full outer joins) and Partitioned Outer Joins (out-of-partition connection for data warehouse).

SELECT d.department_id, e.last_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id, e.last_name

Another way to write it:

SELECT d.department_id, e.last_name FROM departments d, employees e WHERE d.department_id = e.department_id (+) ORDER BY d.department_id, e.last_name

The first method is officially recommended by Oracle.

Thank you for reading this article carefully. I hope the article "what is the use of oracle connection query" shared by the editor will be helpful to everyone? at the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.