Oracle Database entry-level table connections and subqueries

Oracle table connection

Overview: SQL/Oracle queries data from multiple tables using Table connections

Format: select field list from table1, table2 where table1.column1 = table2.column2;

Note: When a field with the same name exists in multiple connected tables, you must prefix the field with "table .".

If the where join condition is not specified, the result of the Cartesian set is unrealistic or meaningless.

Example: select empno, ename, sal, emp. deptno, dname, loc from emp, dept; -- the Cartesian set is obtained. At this time, 70 records are obtained.

Select empno, ename, sal, emp. deptno, dname, loc from emp, dept where emp. deptno = dept. deptno; -- 14 records are obtained.

Type: table join before Oracle8i (SQL92 standard supported): Equijoin, Non-Equijoin, AND Self join)

Outer join: left Outer join and right Outer join

New Connection format introduced by Oracle9i (SQL99 specifications supported): Cross join, Natural join, and Using or On Clause

Inner join and Outer join: left Outer join, right Outer join, and all Outer join

Supplement: You can use the AND operator to add query conditions for multi-Table connections. Using table aliases can simplify queries. Using table names (Table aliases) prefixes can improve query efficiency.

To connect n tables, at least n-1 join conditions are required. For example, select a. ename, a. deptno, B. dname from emp a, dept B where a. deptno = B. deptno;

Sample: The hr account is also a sample account provided by Oracle. There are many sample tables in it, and the data volume and complexity of these tables are stronger than scott.

The hr account is locked by default. You can unlock it and set a password in the OEM. The employees table is the enhanced version of the emp table in scott.

In addition, the data in the orders and locations tables looks more authentic. These three tables are usually used for complex multi-table join queries.

Equijoin)

Example: select empno, ename, emp. deptno, dname from emp, dept where emp. deptno = dept. deptno;

Non-Equijoin)

Question: How can I find the salary grade of each employee?

Example: select empno, ename, sal, grade from emp, salgrade where sal between losal and hisal;

Outer join)

Overview: The Outer Join operator is (+ ). When using an external connection, you can see that one of the parties involved in the connection does not meet the connection conditions.

Traditional outer connections are divided into left outer connections and right outer connections. In actual development, external connections are used less frequently than equivalent connections.

Syntax: select field list from table1, table2 where table1.column1 (+) = table2.column2;

Select field list from table1, table2 where table1.column1 = table2.column2 (+ );

Note: table1.column1 (+) = table2.column2; -- display all records that meet the conditions, and the records that do not meet the connection conditions in table2 will also be displayed.

Table1.column1 = table2.column2 (+); -- display all records that meet the conditions, and the records that do not meet the connection conditions in table1 are also displayed

Supplement: In the SQL99 specification, Inner Join only returns data that meets the connection conditions, while outer Join returns rows that do not meet the connection conditions.

Category: Left Outer Join (Left Outer Join): In addition to returning rows that meet the connection conditions, the two tables return rows that do not meet the conditions in the Left table.

Right Outer Join: In addition to returning rows that meet the connection conditions, the two tables return rows that do not meet the conditions in the Right table.

Full Outer Join: in addition to the rows that meet the connection conditions, the two tables return rows that do not meet the conditions during the connection.

Example: select employee_id, last_name, salary, department_id, department_name

Form employees left join orders using (department_id); -- returns row 107

Self join)

Overview: the essence of Self-join is to use a table as two tables, but to define different aliases.

We seldom use self-connection at ordinary times, but sometimes we can solve some problems only by using self-connection.

Question: How can I find the employee ID and name of each employee and his/her boss?

Example: select a. empno, a. ename, a. mgr, B. ename from emp a, emp B where a. mgr = B. empno;

SQL99 connection syntax

Overview: Connection query syntax specified in SQL1999. When connecting two or more tables, specify the connection conditions between the two adjacent tables in sequence.

Syntax: select Field List

From table1

[Cross join table2] |

[Natural join table2] |

[Join table2 using (field name)] |

[Join table2 on (table1.column _ name = table2.column _ name)] |

[(Left | right | full outer) join table2 on (table1.column _ name = table2.column _ name)];

[Cross join table3] |

[Natural join table3] |

[Join table3 using (field name)] |

[Join table3 on (table2.column _ name = table3.column _ name)] |

[(Left | right | full outer) join table3 on (table2.column _ name = table3.column _ name)]...;

Note: If the new syntax is confusing, you can use the old syntax when there is no mandatory requirement. There is no efficiency difference between the two.

Cross join)

Overview: Cross join produces a Cartesian set. The effect is equivalent to that when two tables are connected, the WHERE clause is not used to limit the join conditions.

For example, select empno, ename, dname from emp a cross join dept B; -- cross join does not actually make much sense.

Natural join)

Overview: Natural join creates a connection based on all the same names in the two tables. Select the values of the same column from the two tables and all rows with the same name.

If the data type of the same column in the two tables is different, an error occurs. Table names or aliases cannot be used as prefixes in reference columns.

Example: select demno, ename, sal, deptno, dname from emp natural jon dept;

Using clause

Overview: if you do not want to perform equivalent join by referring to all the same names in the connected table, the natural join cannot meet the requirements.

You can use the USING clause to set the name of the column (reference column) used for equijoin during connection.

Table names or aliases cannot be used as prefixes in reference columns.

Example: select empno, ename, sal, deptno, dname form emp join dept using (deptno );

On clause

Overview: If you want to perform equijoin by referring to columns with different names, or want to set any connection conditions, you can use the ON clause.

Example: select empno, ename, sal, emp. deptno, dname from emp join dept on (emp. deptno = dept. deptno );

Subquery)

Overview: A subquery is executed once before the primary query. The primary query uses the subquery results. For example, query information about all employees who are higher than Michael Jacob's salary

Subqueries are classified into two categories: single-row subqueries (return results of one row) and multi-row subqueries (return results of multiple rows ).

Syntax: select field list form table where expression operator (select field list from table );

For example, select * from emp where sal> (select sal from emp where empno = 7654 );

Note: subqueries should be considered for queries based on unknown values. The subquery must be included in brackets.

We recommend that you place the subquery on the right of the comparison operator to enhance readability. Do not use the order by clause in subqueries unless Top-N analysis is performed.

You can use the single row record comparison operator for single row subqueries. For multi-row subqueries, you can only use the multi-row record comparison operator.

Null Value: If the subquery does not return any rows, the primary query does not return any results.

For example, select * from where sal> (select sal from emp where empno = 8888); -- no results are returned.

Multi-value: If a subquery returns multiple rows of results, a multi-row subquery is not allowed.

For example, select * from emp where sal> (select avg (sal) from group by deptno); -- invalid

TopN query (TopN Analysis)

Overview: obtain the first n records sorted by certain rules. In Oracle, subqueries are usually used to implement Top N queries.

In fact, a subquery can be considered as a temporary table or a temporary view without a name.

Syntax: select field list from (select field list from table order by sort field) where rownum <= n;

Example: select * from (select * from emp order by sal desc) where rownum <= 5;

Pseudo-column rownum

Summary: In the SELECT query result, a field rownum, that is, pseudo column, is implicitly added. Rownum is flexible to use, but error-prone.

The rownum pseudo column is not a real column in a virtual table in a data table or subquery. It is only a pseudo column in the query result.

It indicates the number of results that meet the query conditions. The rownum value of the first record is 1, and the rownum value of the second record is 2.

It can be understood that the record number of the first row that meets the query condition is 1, and the record number of the second row that meets the query condition is 2

Example 1: select * from emp where rownum> = 5; -- the result after execution is that no return value is returned.

Extract the first record in the result set, or the first record in the data table, and mark the number of the first record as 1.

After judgment, we know that 1 is less than 5 and does not meet the conditions. Then, the system filters out the record and checks whether the next record meets the condition.

Then the next record is retrieved, and the rownum of the next record starts from 1. Rownum always starts from 1, and the results can be imagined.

The number of the next record is still 1. In this case, the number of the record will never meet the condition that the number is greater than or equal to 5.

That is to say, in the environment of this SQL statement, rownum will never be greater than or equal to 5. Therefore, we cannot expect rownum to be used for Interval sorting.

That is to say, rownum> = 5 and rownum <= 10 will never be true. This is the so-called Top N analysis.

Example 2: select * from emp where rownum <= 5 order by sal desc;

The expected results are not displayed after the sentence is executed. Although five records are also obtained, the first five values are not sorted in descending order of wages.

It returns the first five rows of records in the emp table, but displays the results in descending order of wages.

During execution, the where condition is filtered to obtain the first five records in the original table. Then sort and output the first five records of the table.

Obviously, this is not what we want. We want to sort the data first, sort the data, and then obtain the information of the first five rows.

However, if you write select * from emp order by sal desc where rownum <= 5;, it does not conform to the select syntax and will cause an error.

Therefore, you can use the rownum pseudo column in a statement as a subquery to implement the TopN query.

Paging: in Java Web programming, paging display is often encountered. Sometimes you need to display a range record on a page

For example, 21st to 30th records are displayed. In this case, the TopN query alone obviously cannot meet the requirements.

In this case, the pseudo columns in the subquery can be converted into a real column or a real column that can be compared.

Statement 1: select rownum, a. * from (select * from emp order by sal desc);

If a. * is written as *, a missing expression error occurs. The subquery is not a real table, so it depends on its alias.

Its running result is to display all records in the original emp, and there is also an additional row of ROWNUM records, the column value is a continuous number from 1 to 14

In this case, the rownum is still virtual, and the where rownum> = 5 and rownum <= 10 intervals cannot be executed.

Because rownum is actually equal to the pseudo column number of each record in 14 rows in this query.

From the beginning of the first line, the record is always equal to 1. If the condition is not equal to or greater than 5, the record in the first line will be filtered out.

The pseudo column number of the next record starts from 1, and the result in "Example 1" will appear again. Therefore, the interval sorting cannot be performed at this time.

Description 2: You can create an alias for rownum, such as myno. Then, use the entire statement as a subquery.

That is, select * from (select rownum myno, a. * from (select * from emp order by sal desc) );

The parentheses enclose the subquery. This subquery will get n + 1 record, where the first record is the myno Field

In this case, myno is an actual result. If the subquery is treated as a real table, the table should have n + 1 fields.

All are actually existing fields. One field is called myno, and the others are all fields in the original emp.

Then, write the query condition where myno> = 5 and myno <= 10 in the statement behind the statement. The entire sentence is like a sentence in the template.

Then execute the entire statement to obtain the expected result, that is, the employee information in the emp ranking between 5th and 10th

Template: select * from (select rownum myno, a. * from (select * from emp order by sal desc) a) where myno> = 5 and myno <= 10;

This is the SQL statement that uses the TopN query in Oracle to achieve display by page. You can also remember the current syntax format as a template.

A subquery can be treated as a table. Assume that the table exists physically and contains n + 1 actually existing field. One of the fields is myno.

If you change the condition to where rownum> = 5 and rownum <= 10, the execution result is: no row is selected or no content is selected.

This rownum is not the result rownum returned by the subquery, but a pseudo column in this primary query.