Multiple table joins and subqueries for getting started with Oracle databases

Oracle Table Connection

Overview: Sql/oracle Use table joins to query data from multiple tables

Format: Select field list from Table1,table2 where Table1.column1=table2.column2;

Note: When the same name field exists in multiple tables that are connected, you must precede it with a table. As a prefix

If the where join condition is not qualified, there will be an unrealistic or no practical result of the Cartesian set

For example: select Empno, ename, Sal, Emp.deptno, Dname, loc from EMP, dept;--This is the Cartesian set. Get 70 records at this time

Select Empno, ename, Sal, Emp.deptno, Dname, loc from EMP, dept where emp.deptno=dept.deptno;--get 14 records

Type: Table connection before oracle8i (Support SQL92 Standard): Equivalent connection (equijoin), non-equivalent connection (non-equijoin), self-connection (self join)

Outer joins (Outer join): Left outer joins, right outer joins

Oracle9i new Join form (Support SQL99 specification): Cross-join (Cross join), natural join (Natural join), use using or ON clause to establish a connection

Inner joins (Inner join), outer joins (Outer join): Left outer joins, right outer joins, full outer joins

Add: Multiple table joins can use the and operator to increase query conditions, use table alias can simplify the query, use table name (table alias) prefix to improve query efficiency

When you connect n tables, you need to n-1 at least one join condition. such as 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 with Oracle, which has a lot of sample tables, and has more data and complexity than Scott.

The HR account is locked by default and can be unlocked and set to a password in the OEM. The Employees table is an enhanced version of the EMP table in Scott.

There are also departments and locations tables with data that looks more realistic. These three tables are typically used for complex multiple table join queries

Equivalent connection (equijoin)

For example: select Empno, ename, Emp.deptno, dname from EMP, dept where Emp.deptno=dept.deptno;

Non-equivalence connection (Non-equijoin)

Question: How to check the salary level of each employee

For example: select Empno, ename, Sal, grade wage level from Emp,salgrade where Sal between Losal and Hisal;

Outer joins (Outer join)

Overview: The outer join operator is (+). Use an outer join to see a record of a party participating in a connection that does not meet the join condition

The traditional outer joins are divided into two kinds: Left outer connection and right outer connection. In actual development, the use frequency of outer joins is less than the equivalent connection

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;--displays all records that meet the criteria, and records that do not meet the connection criteria in Table2 are also displayed

Table1.column1=table2.column2 (+);--Displays all eligible records, while records that do not meet the join conditions in Table1 are displayed

Add: In the SQL99 specification, the INNER join (Inner join) returns only the data that satisfies the join condition, and the outer join returns rows that do not meet the join condition

Category: Left OUTER join: Two tables returns rows in the left table that do not meet the criteria in addition to the row that satisfies the join condition in the connection process

Right Outer join: Two tables returns rows in the right table that do not meet the criteria in addition to the row that satisfies the join condition in the connection process

Full outer join: Two tables returns rows that do not meet the criteria in the two table, in addition to the row that satisfies the join condition during the connection Outer

For example: Select employee_id, last_name, salary, department_id, department_name

Form employees left JOIN departments using (DEPARTMENT_ID);--Returns 107 rows

Self-connection (self join)

Overview: Self-Connection The essence is to use a table as two tables, but to define a different alias.

Self-connection is rarely used at ordinary times, but sometimes it is only possible to solve some problems with a self connection

Question: How to find the work numbers and names of each employee and his or her boss

For example: Select A.empno, A.ename, A.mgr, b.ename from EMP A, EMP b where a.mgr=b.empno;

SQL99 Connection Syntax

Overview: The connection query syntax specified in the SQL1999 specification. When connecting over two tables, you should specify the join condition between the two tables

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 you feel the new syntax is confusing, when there is no mandatory requirements, you can use the old grammar, there is no difference in efficiency

Cross-connect (Cross join)

Overview: Cross join produces a Cartesian set whose effect is equivalent to not using a WHERE clause to qualify join conditions when two tables are connected

For example: select Empno, ename, dname from EMP a cross join Dept b;--Cross joins actually don't make much sense

Natural connection (Natural join)

Overview: Natural Join establishes a connection based on all of the two tables in the same column. Select all rows from both tables that are equal to the value of the same column

An error occurs if the data type of the same column in the two tables is different. It is not allowed to use a table name or alias as a prefix on the reference column

For example: Select Demno, ename, Sal, Deptno, dname from EMP Natural Jon Dept;

Using clause

Overview: If you do not want to refer to all the same names of connected tables for equivalent connections, the natural connection will not meet the requirements

You can use a using clause to set the column (Reference column) name for an equivalent connection at connection time

It is also not allowed to use table names or aliases as prefixes on reference columns

For example: select Empno, ename, Sal, Deptno, dname form EMP Join dept using (DEPTNO);

ON clause

Overview: If you want to make an equivalent connection to a column that does not have the same name, or you want to set any join condition, you can use the ON clause

For example: select Empno, ename, Sal, Emp.deptno, dname from EMP Join dept on (EMP.DEPTNO=DEPT.DEPTNO);

Subquery (sub query)

Overview: Subqueries are executed before the main query, and the main query uses the results of the subquery. For example, check all employee information that is higher than John Salary

Subqueries are divided into Single-line subqueries (returning one row of results) and multiple rows of subqueries (returning multiple rows of results) two broad categories

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: Queries based on unknown values should consider using subqueries. Subqueries must be enclosed in parentheses

It is recommended that the subquery be placed to 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 Single-line record comparison operators for Single-line subqueries. For multiline subqueries, you can only use multiline record comparison operators.

Null value: If the subquery does not return any rows, the main query will not return any results

For example, select * from where sal> (select Sal from EMP where empno=8888);--no results will be returned

Multiple values: If a subquery returns multiple rows of results, it is a multiline subquery, and it is not allowed to use Single-line record comparison operators at this time

For example, select * from emp where sal> (select AVG (SAL) from Group by Deptno);--illegal

TOPN query (TOPN analysis)

Overview: Gets a record of the top n after sorting by some rule. TOPN query in Oracle is usually implemented by subqueries

In fact, subqueries can be considered to have found 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;

For example: SELECT * FROM (SELECT * FROM emp desc) where RowNum <=5;

Pseudo-Column RowNum

Overview: The Select query results implicitly add a field rownum, or pseudo columns. RowNum is very flexible, but it's also easy to make mistakes.

The rownum pseudo column is not a real column in a datasheet or a subquery's virtual table, it is just a pseudo column in the query result

It marks the number of results that match the query criteria, the rownum value of the first record is 1, and the rownum value of the second record is 2.

As you can see, the first line of records that matches the query criteria is 1, and the second row that matches the query criteria is numbered 2.

Example one: SELECT * from EMP where rownum>=5;--its execution result is no return value

The first record in the data table is taken out first in the result set, or the first record is marked with the number 1

After the judge learned that 1 is less than 5, does not meet the conditions. It then filters out the record and then determines if the next record is eligible.

Then he took out the next record, and the next record was rownum from 1. And rownum is always starting from 1, the results can be imagined

The next record is numbered 1. In this case, the number of records will never meet the conditions greater than or equal to 5

In other words, in the context of this SQL statement, RowNum will never be greater than or equal to 5. So we can't expect to use rownum for interval sorting.

That is to say rownum>=5 and RowNum <=10 will never be established. This is called TOPN analysis.

Example two: SELECT * from EMP where rownum<=5 order by Sal Desc;

The sentence is executed without the expected results. Although 5 records are also available, it is not the first 5 values after a descending order of wages

It returns the first 5 rows in the EMP table, except when it is displayed in descending order of wages

When executed, the Where condition is filtered first, and the first 5 records in the original table are filtered. Then sort and output the first 5 records of the table

Obviously it's not what we want. What we want is to sort, order, and then get the first 5 lines of information.

However, if written as a select * from emp desc where rownum<=5;, it does not conform to the select syntax and can be faulted

So we can only use subquery to combine rownum pseudo columns in a statement to implement TOPN query.

Pagination: In Javaweb programming, you often encounter paging problems. Sometimes you need to display a range of records on a page

For example, show 21st to 30th record. In this case, the simple TOPN query is clearly not enough to meet the requirements

This allows the pseudo column in the subquery to become a real column, or to make it a real column that can be used for comparison operations.

Statement I: Select RowNum, a.* from (SELECT * from emp ORDER BY Sal Desc) A;

Here, if you write a.* as *, you will get a missing expression error. The subquery is not a real table, so you can only rely on its alias

The result is a display of all the records in the original EMP, plus a list of rownum records with consecutive numbers from 1 to 14.

The rownum is still virtual and still cannot perform the interval sort of where rownum>=5 and rownum<=10

Because rownum is actually equal to the pseudo-column number of each row in the 14-line record of this query

Starting from the first line is always equal to 1, if you do not meet the conditions greater than or equal to 5, the first line of records will be filtered out

The next line of records in the pseudo-column number or starting from 1, it will appear in the "Example one" results, so at this time can not be interval sorting

Two: At this time can give RowNum an alias, such as Myno. And then just the entire statement as a subquery

That is, select * FROM (select RowNum Myno, a.* from (SELECT * to emp ORDER by Sal Desc);

The entire bracket is surrounded by a subquery again. This subquery gets the n+1 record, where the first record is the Myno field

The myno is a result of the actual existence. If the handle query is a real table, there should be a n+1 field in the table.

are real fields, one field is called Myno, and the other is all the fields in the original EMP.

Then, in the statement, the query criteria where myno>=5 and myno<=10 are written behind the statement. The whole sentence is like a sentence in a "template"

Then execute the whole statement and get the expected result, that is, the employee information between the 5th and 10th names in the EMP.

Template: SELECT * FROM (select RowNum Myno, a.* from (SELECT * to emp ORDER BY sal Desc) a) where myno>=5 and myno<=10;

This is the SQL statement that uses TOPN queries in Oracle to implement pagination display, or you can use the current syntax format as a template to remember

Subqueries can be considered as a table, assuming that the table is physically present, with a n+1 field in it, one of which is called Myno

If you change the condition to where rownum>=5 and rownum<=10, the result is that no rows are selected or Nothing is selected

Because this rownum refers not to the result of the return in the subquery rownum, but to a pseudo column in the main query