Oracle Study Notes: Joint query of tables

1. Cross join: this gives you the most intuitive impression on the concept of "join", because the result of cross join is the Cartesian Product codeexample: select * f of the two tables.

1. Cross join: this gives you the most intuitive impression on the concept of "join", because the result of cross join is the Cartesian product code example: select * f of the two tables.

1. Cross join: this gives you the most intuitive impression on the concept of "join", because the result of cross join is the Cartesian product of two tables.
Code example:

Select * from T1, T2;

If table 1 has 2 records and table 2 has 3 records, the query result is 2*3 = 6 records.

2. equi union and non-equi union:

Equivalent Union: only the data in table 1 and the data in table 2 that exists in Table 1 are displayed. As the name suggests, the query condition/expression is connected with an equal sign ("=.
Code example:

Select T1.sectionA, T1.sectionB, T2.sectionC from T1, T2

Where T1.sectionA = T2.sectionA (and ...);

Non-equivalent Union: similar to the equi Union query, except that the where clause uses a comparison character except the equal sign to connect. This example is not described here.

3. Internal and External Cooperation

Internal Union: the number of results rows is determined by the number of rows participating in the Union, that is, the number of rows of the internal Union depends on the result of the WHERE clause.
Code example:

Select p. PARTNUM, P. DESCRIPTION, P. PRICE, O. NAME, O. PARTNUM

FROMPART PJOINORDERS OOn orders. PARTNUM = 54;

In the syntax you useJoin on is not specified in the ANSI standard, But the additional syntax of the interpreter we use. YouIt can be used to specify whether it is an internal joint or an external joint. Most interpreters perform similar extensions.Note that this type of union does not have a WHERE clause.

The definition of internal union is inconvenient. I personally understand that the examples of the preceding equi/o Union query are represented by them, and most of the queries used in ordinary times are internal queries.

After learning the appearance query, the internal query results are intuitive and concise in stark contrast to the external query's "adding a snake". Even if we are not using external Union, it must be an internal combination.

In addition, you do not need to be overly entangled in concepts and usage. This is because --

Most SQL products determine which kind of Union should be used in your query. In fact, if you use it in the process (or use it in the Program) <包括存储过程等> ), You do not need to specify the Union type. The interpreter will select the appropriate syntax format for you.

If you explicitly specify the Union type, the interpreter will use the type you specify to replace (implemented by the interpreter itself) the optimized type.

External Union: the number of rows of results is determined by the number of rows participating in the Union. That is to say, the number of rows of the internal Union depends on the result of the WHERE clause, while the external union is the union between tables.
Code example:

Select p. PARTNUM, P. DESCRIPTION, P. PRICE, O. NAME, O. PARTNUM

FROMPART PRIGHT OUTER JOINORDERS OOn orders. PARTNUM = 54;

In the preceding code example, right outer join is used, which causes the SQL statement to return all records in the RIGHT table set. For ORDERS. if PARTNUM <> 54, these records are also displayed, and null values are not supplemented at the corresponding position. .

The same is true for left join, except that the query results are quite different because the number of records in Table 1 and Table 2 is different.

Note: In some interpreters, the + number is used to replace the external Union. + Indicates to display all my content, including unmatched content.
Code example:

Select e. name, e. employee_id, ep. salary, ep. marital_status,

From employee_tbl e, employee_pay_tbl ep

Where e. employee_id = ep. employee_id (+) and e. name like '% MITH'

This statement will be associated with two tables, and all the employee_id marked with + will be displayed, including records that do not meet the conditions.

4. Table self-Union: Because joint queries are often translated as "Connections", the "self-join" query shown in some documents also refers to this concept. It does not specifically mean that table 1 and table 2 both have the same table name. It is used to check the data consistency in the table.
For example, the sectionA field of the two records in Table T1 is of the same value, which may be caused by a data entry error. Using it based on normal data may cause unpredictable disasters.
Code example:

Select f. PARTNUM, F. DESCRIPTION, S. PARTNUM, S. DESCRIPTION

From part f, PART S

Where f. PARTNUM = S. PARTNUM

And f. DESCRIPTION <> S. DESCRIPTION

If the preceding exception data does not exist, the query result should be blank; otherwise, one exception record corresponds to two query results. This can be used to check data consistency.

5. Think of UNION and UNION ALL

Both UNION and union all are used to connect two queries (that is, two select clauses). However, the former returns the results of the two queries and removes their duplicates. The latter merges the query results, however, duplicate records are not removed.

UNION can be associated with the UNION operation in the set operation. It corresponds to INTERSECT, that is, the intersection operation. It returns the common part of the two queries.

6. Additional instructions:
The preceding joint queries only list the key points and do not elaborate on the classification of joint queries. Even because the application of joint queries is rare in actual work, understanding of such concepts can be avoided, but it is dangerous to understand it. The price you pay for ignorance is expensive. This article also makes sense.