SQL query using a brief extract Source: 9CBS [2004-11-02 09:53:14] Author: okook Editor: linjixiong
Join_type indicates that the connection type can be divided into three: internal connections, external connections, and cross-connect. Inner Join Using the comparative operator to perform a comparison operation of the data (some) column data, and list the data lines matching the connection conditions in these tables. According to the comparative method used, the inner connection is divided into equivalent connection, natural connection, and not equal connection. The outer connection is divided into left-way connections (Right Outer Join, Right Outer Join or Right Join, and three full-end connections (Full Outer Join or Full Join). Unlike internal connections, the external connection does not only list rows matching the connection condition, but is listed on the left table (left external connection), the right table (right-external connection) or two tables (all external connection All data lines in line with search criteria are in line.
Cross Join does not have a WHERE clause, it returns the Cartesian of all data lines in the connection table, and the number of data lines in the result set is equal to the number of data lines that meet the query conditions in the first table. The number of data lines in the table is in line with the query conditions.
The on (join_condition) clause in the connection operation indicates the connection condition, which is constructed by the columns and comparison operators, logical operators, and the like in the connected table.
No matter which connection can be directly connected to Text, NText, and Image Data Type columns, it can be indirectly connected to these three columns. E.g:
Select p1.pub_id, p2.pub_id, p1.pr_info from pub_info as p1 inner join pub_info as p2 on datalength (p1.pr_info) = DATALENGTH (P2.PR_INFO)
(1) internal connection
Internal connection query operations list the data lines that match the connection condition, which uses the comparison operator to compare the column values of the columns. The internal connection is divided into three types:
1. Equivalence: Use the column value of the connected column to compare the column value of the connection column in the connection conditions, and the query results are listed in the query results, including the repeat columns.
2, inequality: Use the column values of the columns connected to the listed columns that are connected to other comparison operators other than operators. These operators include>,> =, <=, <,!>,!
3, natural connection: Use the equal to (=) operator to compare the column values of the columns in the connection conditions, but it uses the selection list to point out the columns included in the query result collection, and delete the repeat columns in the connection table.
For example, the following uses the equivalent connection to list authors and publishers in the same city in the Publishers table:
Select * from authors as a inner join public, as a natural connection, remove the repeat column (City and State) in the Authors and Publishers tables in the selection list: SELECT A. *, P. Pub_id, pub_name, p.country from authors as a inner join publicishers asp on a.city = p.city
(2) external connection
When the internal connection, the return of the query result collection is only the line that meets the query condition (WHERE search criteria or Having condition) and the connection condition. When using an external connection, it returns to the query result collection not only contains rows that meet the connection conditions, but also the left table (when connected to the left), the right table (when connected to the right) or two edges (full All data lines in an external connection. The forum content and author information are connected to the left outer connection:
Select a. *, B. * From luntan left join utertable as b on a.username = B.username The following uses all the author in the city table and all the author in the User table, and their city:
Select a. *, B. * From city as a full outer join user as b on a.username = B.USERNAME
(3) Cross-connect cross-connect without WHERE clauses, returns the two tables of the two tables that are connected, returning to the number of data lines in the result collection equal to the data line that meets the query condition in the first table. The number multiplies the number of data lines that meet the query conditions in the second table. In the case, there are 6 types of books in the Titles table, and 8 publishers in the Publishers table, the following cross-connect retrieved record number will wait
On 6 * 8 = 48 lines. SELECT TYPE, PUB_NAME from Titles Cross Join Publishers Order by Type [POST = 0] [/ POST]
