sql2005 in connection with the outer join cross join queries combined query (merged query)

2010-07-07  来源:本站原创  分类:Database  人气:156 

(A) in connection

Query operation in connection with the connection conditions set out rows that match, it uses the comparison operators compare the value of being connected out of the column. Connection points within the three types:
1, equivalent to connect: connection conditions in the use of equal sign (=) comparison operator to be connected out of the column value, the query results are listed in the table to be connected all the columns, including one of the duplicate columns.

2, ranging from connection: the connection conditions of use of inter operator other than equal to comparison operator to be connected more value out of the column. These operators include >,>=,<=,<,!>,!< and <>.

3, the natural connection: the connection conditions of the use of equal (=) comparison operator to be connected out of the column value, but it uses the select list that included in the query result set column, and delete the link in the table to repeat the column.
Cases, use the following link lists the authors and publishers the equivalent table in the same city and the publishers of:
SELECT *
FROM authors AS a INNER JOIN publishers AS p
ON a.city = p.city

Another example is the use of natural connection, in the select list of authors and publishers tables to delete duplicate columns (city and state):
SELECT a. *, p.pub_id, p.pub_name, p.country
FROM authors AS a INNER JOIN publishers AS p

ON a.city = p.city

Multiple inner join, the execution order from left to right, for example:

SELECT T1.NAME, T2.AGE, T3.SEX FROM

TABLE1 AS T1 INNER JOIN TABLE2 AS T2

ON T1.ID = T2.ID

INNER JOIN TABLE3 AS T3

ON T1.ID = T3.ID

(B) of the outer link

Within the connection, return query results in the collection is in query only (WHERE or HAVING search conditions for the conditions) and the connection conditions of the line. The use of external connection, it returns to the query result set in terms of not only contains the line connecting the line, but also the left table (left outer connection), the right table (right outer join), or two, taking the form (all external connection) all the data rows.

Use the left outer join, such as the following contents and author information in the forum link:
SELECT a. *, b. * FROM luntan LEFT OUTER JOIN usertable as b
ON a.username = b.username

All external connections will use the following table for all of city and all of user tables and their host cities:
SELECT a. *, b. *
FROM city as a FULL OUTER JOIN user as b
ON a.username = b.username

C) cross-connect

Cross-connect without a WHERE clause, it returns two tables are connected to the Cartesian product of all the data rows and return to the results of data collection the number of rows is equal to the first table of data that meets the query number of rows multiplied by the second table of data that meets the query number of rows.
Cases, titles table, 6 books, and publishers in the table have eight publishers, the following cross-connect the number of records retrieved will be equal to 6 * 8 = 48 lines.
SELECT type, pub_name
FROM titles CROSS JOIN publishers
ORDER BY type
UNION operator can be two or more SELECT statements on a set of query results into a single result set shows that the implementation of the joint inquiry. UNION syntax format:
select_statement

UNION [ALL]

selectstatement

[UNION [ALL]

selectstatement

Which is to be combined selectstatement SELECT query.
ALL option that all the rows into the result set. Do not specify the time, was jointly set the duplicate query results will only be retained for a

Line.

Joint query, query results column headings for the first query of the column headings. Therefore, to define the column headings in the first query must be defined. To sort query results on the joint, they must use the first query the column names, column headings or column number.
When using the UNION operator, should ensure that the choice of each joint query the list have the same number of expressions, and each query selection expression should have the same data type, or can automatically convert them to the same data type. Automatic conversion, the value class

Type, the system will be less accurate data type is converted to high-precision data types.

For example: SELECT A1, A2, A3 FROM A

UNION

SELECT B1, B2, B3 FROM B

Where (A1, B1), (A2, B2), (A3, B3) must have the same data type, or can automatically convert them to the same data type

Including the number of queries in the UNION statement, the execution order is from left to right, use parentheses to change the order of execution. For example:
Query 1 UNION (Query 2 UNION query 3)

相关文章