Oracle study Note 5

Our previous queries are all based on one table. How can we query data from two or more tables? This note is used to learn multi-table queries.

Basic syntax:

Select * | (specific column name, column name 1, column name 2)

From table 1 alias 1, table alias 2

Where conditions

Order by COLUMN name (asc, desc)

Equivalent connection:A connection query that uses the '=' operator as the connection condition is called an equivalent connection. A connection query that uses a comparison character other than '=' as the connection condition is called an unequal connection.

Internal Connection:Merge two tables or rows that match multiple tables. If the rows in one table cannot find the matched rows in the other table, delete the rows. The result set does not contain rows that do not match the other table.

External Connection:In addition to returning rows that meet the connection conditions, the two tables also return rows that do not meet the conditions in the left (or right) table.

Left (or right) Outer Join:If no matching row exists, the corresponding column in The result table is NULL ). The WHERE clause condition for outer join is similar to an internal join, but the column of the table that does not match the row in the join condition must be followed by an outer join operator, that is, the plus sign (+) enclosed in parentheses ).

Cross join:Join conditions are not used for join queries, and cartesian products are easily generated.

Self-connection:Connection query is performed on a table or view, that is, the table is connected to itself for query.

 

The above gives a general definition of each connection, and we will explain it through an example below!

Internal Connection:

Connect two or more tables. Return the data that meets the connection conditions. Delete records that do not meet the connection conditions and match columns with null values.

For example, you can use a multi-Table query statement to perform operations on both the emp and dept tables to query the employee name and department name.

SQL> select ename ,dname from emp , dept where emp.deptno = dept.deptno ;ENAME      DNAME---------- --------------SMITH      RESEARCHALLEN      SALESWARD       SALESJONES      RESEARCHMARTIN     SALESBLAKE      SALESCLARK      ACCOUNTINGSCOTT      RESEARCHKING       ACCOUNTINGTURNER     SALESADAMS      RESEARCHJAMES      SALESFORD       RESEARCHMILLER     ACCOUNTING 14 rows selected

 

The '=' operator is used in the preceding query statement, which is an equivalent join. If the names of different tables are different, you do not need to add the table prefix to the column name; if the names of tables are the same

The table name must be prefixed to distinguish the same column names in different tables. Sometimes the table name may be long. We can create an alias for the table to simplify operations and improve execution efficiency.

SQL> select ename, dname from emp e, deptd where e. deptno = d. deptno;

The query result of the preceding statement is the same as that of the preceding statement, which is not described here.

 

The following uses the between... on comparison operator as the connection condition to implement unequal connections.

Before waiting for a connection, let's take a look at a new table named salgrade under soctt.

SQL> select * from salgrade ;      GRADE      LOSAL      HISAL---------- ---------- ----------         1        700       1200         2       1201       1400         3       1401       2000         4       2001       3000         5       3001       9999

 

For example, query the wage levels of employees whose department number is 10 in the employee table.

SQL>select ename ,e.deptno , grade ,sal   2 from emp e ,dept  d , salgrade s   3 where e.deptno = d.deptno and sal between losal and hisal  and d.deptno = 10 ;ENAME      DEPTNO      GRADE       SAL---------- ------ ---------- ---------MILLER         10          2   1300.00CLARK          10          4   2450.00KING           10          5   5000.00

 

In the above results, between... on... is used as the link condition to divide the wage level. In the join of n tables, at least n-1 join conditions are used. Use and to connect multiple connection conditions.

 

External Connection:

External Connection query is an extension of internal connections. The results returned by the inner connection meet the connection conditions. The inner connection is returned Based on the inner connection.

Delete data. External connections include left outer connections, right outer connections, and all outer connections. Left Outer Join. If the first table does not meet the connection conditions, add

Rows; right Outer Join, added back to rows in the second table that do not meet the connection conditions. All external connections are added back to the rows deleted from the two tables.

 

For example, query the employee name, Department name, and department number.

SQL> select  ename , dname ,d.deptno  2  from emp e ,dept d   3  where e.deptno  =  d.deptno  ; ENAME      DNAME          DEPTNO---------- -------------- ------SMITH      RESEARCH           20ALLEN      SALES              30WARD       SALES              30JONES      RESEARCH           20MARTIN     SALES              30BLAKE      SALES              30CLARK      ACCOUNTING         10SCOTT      RESEARCH           20KING       ACCOUNTING         10TURNER     SALES              30ADAMS      RESEARCH           20JAMES      SALES              30FORD       RESEARCH           20MILLER     ACCOUNTING         10 14 rows selected

Observe the above results and find that the department numbered 40 is missing. Modify the preceding SQL statement.

SQL> select ename , dname , d.deptno  2  from emp e , dept d  3  where e.deptno(+) = d.deptno ; ENAME      DNAME          DEPTNO---------- -------------- ------CLARK      ACCOUNTING         10KING       ACCOUNTING         10MILLER     ACCOUNTING         10JONES      RESEARCH           20FORD       RESEARCH           20ADAMS      RESEARCH           20SMITH      RESEARCH           20SCOTT      RESEARCH           20WARD       SALES              30TURNER     SALES              30ALLEN      SALES              30JAMES      SALES              30BLAKE      SALES              30MARTIN     SALES              30           OPERATIONS         40 15 rows selected

In this case, the Department Information numbered 40 is displayed. We found that there were no employees in Department 40. The right connection is used above.

(+) On the left of "=" is the right join. In this case, the record in the second table in the from statement is returned.

(+) On the right of = is left join. In this case, the records in the first table in the from statement are returned.

For example, who is the manager of every employee in the employee table?

SQL statement:

Select e. ename, m. ename mname
From emp e, emp m
Where e. mgr = m. empno (+)

Result:

ENAME      MNAME---------- ----------SMITH      FORDALLEN      BLAKEWARD       BLAKEJONES      KINGMARTIN     BLAKEBLAKE      KINGCLARK      KINGSCOTT      JONESKING       TURNER     BLAKEADAMS      SCOTTJAMES      BLAKEFORD       JONESMILLER     CLARK 14 rows selected

The left join is used above. Although the value of king does not meet the connection conditions, it can still be displayed in the result.

Self-connection:

The above query uses the self-connection method. Self-join and self-join are similar to the query methods of the two tables.

 

SQL: 1999 supports SQL.

 

Cross join:

Cross join produces cartesian products.

SQL statement:

Select * from emp cross join dept

No query conditions are provided. Use cross join to connect two tables. The query results meet the Cartesian product, that is, each record in both tables must be connected together.

Natural connection:

Natural join: automatically matches associated fields. The equivalent join is created based on the columns with the same name in the two tables. However, if the two tables have the same column name and different data types, a connection error occurs.

SQL statement:

SQL> select * from emp natural join dept;

The employee table and employee table are automatically connected according to deptno. Query results and statements select * from emp e, dept d

Where d. deptno = e. deptno; same. However, if the two tables have multiple identical column names, the query result is that multiple column names are connected.

The result of the combined action. In this case, remove natural and use using (column name) to specify the column to which the table is connected. You can also use on to specify the connection conditions. SQL> select * from emp e join dept d on (e. deptno = d. deptno); the results are the same.

Use the on clause to create the multi-table join syntax.

SELECT column name 1, column name 2, column name 3

FROM table1e

JOIN table2 d

ON d. Column name = e. Column name

JOIN table3 l

ON d. Column name = l. Column name;

Syntax for right Outer Join, left Outer Join, and full outer join:

SELECT column name 1, column name 2, column name 3

FROM table1 e

Roght outer join table2 d

ON (e. Column name = d. Column name)

Example:

SQL> select ename, dname
2 from emp e right outer join dept d
3 on (e. deptno = d. deptno );

 ENAME      DNAME---------- --------------CLARK      ACCOUNTINGKING       ACCOUNTINGMILLER     ACCOUNTINGJONES      RESEARCHFORD       RESEARCHADAMS      RESEARCHSMITH      RESEARCHSCOTT      RESEARCHWARD       SALESTURNER     SALESALLEN      SALESJAMES      SALESBLAKE      SALESMARTIN     SALES           OPERATIONS 15 rows selected

 

SELECT column name 1, column name 2, column name 3

FROM table1 e

Left outer join table2 d

ON (e. Column name = d. Column name );

 

SELECT column name 1, column name 2, column name 3

FROM table1 e

Full outer join table2 d

ON (e. Column name = d. Column name );