SQL multi-Table federated query

You can implement multiple table queries by using the Join operator. Connection is the main feature of relational database model, and it is also a sign that distinguishes it from other types of database management system.

In the relational database management system, the relationship between the data is not determined, and all the information of an entity is often stored in a single table. When retrieving data, the connection operation queries the information for different entities that reside in multiple tables. The connection operation gives the user a lot of flexibility and they can add new data types at any time. Create a new table for different entities, and then query through the connection.

A connection can be established in the FROM clause or a WHERE clause in a SELECT statement, and it is plausible to distinguish the join operation from the search condition in the WHERE clause when the connection is indicated in the FROM clause. Therefore, it is recommended to use this method in Transact-SQL.

The connection syntax format for the FROM clause defined by the SQL-92 standard is:

From Join_table Join_type join_table

[On (Join_condition)]

Where join_table indicates the name of the table participating in the JOIN operation, the connection can operate on the same table, or on a multi-table operation, the connection to the same table operation is also called self-connected.

Join_type indicates the type of connection, which can be divided into three types: inner, outer, and cross-connected. An inner join (INNER join) uses comparison operators to perform comparison operations on some (some) column data between tables, and lists the data rows in those tables that match the join criteria. According to the comparison method used, the inner connection is divided into equivalent connection, natural connection and unequal connection three kinds.

The outer joins are divided into three types: Left outer join (either OUTER join or right join), and the outer join, or the all-inclusive (full OUTER join or complete join), and the out-of-the-OUTER join. Unlike an inner join, an outer join lists not only the rows that match the join criteria, but all the data rows in the left table (when the left outer join), the right table (when the right outer joins), or two tables (when you are connected on the outside), all of which match the search criteria.

A cross join has no WHERE clause, which returns the Cartesian product of all data rows in the join table, with the number of rows in the result set equal to the number of data rows in the first table that match the query criteria multiplied by the number of rows in the second table that meet the query criteria.

The ON (join_condition) clause in the JOIN operation indicates the join condition, which consists of columns and comparison operators, logical operators, and so on in the connected table.

Either connection cannot directly connect to the text, ntext, and image data type columns, but you can indirectly connect the three types of columns. For example:

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)



(i) Internal connection

The INNER JOIN query operation lists the data rows that match the join criteria, and it uses comparison operators to compare the column values of the concatenated columns. There are three types of internal connections:

1. Equivalent connection: Use the equals sign (=) operator in the join condition to compare the column values of the joined columns, and the query results list all the columns in the joined table, including the repeating columns.

2, unequal connections: Use a comparison operator other than the equals operator in the join condition to compare the column values of the connected columns. These operators include >, >=, <=, <,!>,!<, and <>.

3. Natural connection: Use the Equals (=) operator in the join condition to compare the column values of the connected column, but it uses the selection list to indicate which columns are included in the query result collection and to delete the duplicate columns in the Join table.

example, the following uses the equivalent connection to list authors and publishers in the same city in the authors and Publishers tables:

SELECT *

From authors as a INNER JOIN publishers as P

On a.city=p.city



If you use a natural connection, delete the authors and Publishers tables in the select list (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



(ii) external connection

In the query results collection, only the rows that meet the query criteria (WHERE search condition or having condition) and join conditions are returned. In the case of an outer join, it returns to the query result collection not only the rows that meet the join criteria, but also all data rows in the left table (when left outer joins), the right table (when the right outer joins), or two edge tables (full outer joins).

Use the left OUTER join to connect the forum content and author information as follows:

SELECT a.*,b.* from Luntan left JOIN usertable as B

On A.username=b.username



All the authors in the city table and all the authors in the user table, and the cities in which they are located, are used with the full outer join:

SELECT a.*,b.*

From city as a full OUTER JOIN user as B

On A.username=b.username



(iii) cross-linking

The cross join does not take a WHERE clause, it returns the Cartesian product of all data rows of the two connected tables, and the number of rows returned to the result set equals the number of data rows in the first table that meet the query criteria multiplied by the number of data rows in the second table that meet the query criteria.

example, there are 6 categories of books in the titles table, while the publishers table has 8 publishers, the number of records retrieved by the following cross-connect will be

On the 6*8=48 line.

SELECT Type,pub_name

From the titles cross JOIN publishers

ORDER by Type

Transferred from: http://topic.csdn.net/t/20060809/09/4936637.html

12 Floor aw511 (dot Star Light) reply at 2006-08-22 09:13:42 score 0--Query Analyzer execution:

--Build Table Table1,table2:
CREATE TABLE table1 (ID int,name varchar (10))
CREATE TABLE table2 (ID int,score int)
INSERT INTO table1 Select 1, ' Lee '
INSERT INTO Table1 Select 2, ' Zhang '
INSERT INTO table1 Select 4, ' Wang '
Insert INTO table2 Select 1,90
Insert INTO table2 Select 2,100
Insert INTO table2 select 3,70
such as table
-------------------------------------------------
table1|table2|
-------------------------------------------------
idname|idscore|
1lee|190|
2zhang|2100|
4wang|370|
-------------------------------------------------

The following are performed in Query Analyzer

One, outer connection
1. Concept: Includes a LEFT outer join, a right outer join, or a full outer join

2. Left-side connection: outer JOIN
(1) The result set of the left outer join includes all rows of the left table specified in the OUTER clause, not just the rows that match the joined columns. If a row in the left table does not have a matching row in the right table, all select list columns in the right table in the associated result set row are null (NULL).
(2) SQL statements
SELECT * FROM table1 LEFT join table2 on Table1.id=table2.id
-------------Results-------------
Idnameidscore
------------------------------
1lee190
2zhang2100
4wangNULLNULL
------------------------------
NOTE: All clauses that contain table1, return table2 corresponding fields according to specified criteria, non-conforming null display

3. Right Join
(1) A right outer join is a reverse join of a left outer join. All rows of the right table will be returned. If a row in the right table does not have a matching row in the left table, a null value will be returned for left table.
(2) SQL statements
SELECT * FROM table1 right join table2 on Table1.id=table2.id
-------------Results-------------
Idnameidscore
------------------------------
1lee190
2zhang2100
NULLNULL370
------------------------------
NOTE: All clauses that contain table2, return table1 corresponding fields according to specified criteria, non-conforming null display

4. Complete outer join: Full JOIN or outer join
(1) A full outer join returns all rows from the left and right tables. When a row does not have a matching row in another table, the selection list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.
(2) SQL statements
SELECT * FROM table1 full join table2 on Table1.id=table2.id
-------------Results-------------
Idnameidscore
------------------------------
1lee190
2zhang2100
4wangNULLNULL
NULLNULL370
------------------------------
Note: Returns the left and right connected and (see top

second, internal connection
1. Concept: An inner join is a join that compares the values of the columns to be joined by comparison operators

2. Internal connection: Join or INNER join

3.sql statements
SELECT * FROM table1 join table2 on Table1.id=table2.id
-------------Results-------------
Idnameidscore
------------------------------
1lee190
2zhang2100
------------------------------
Note: only table1 and table2 columns that match the criteria are returned

4. Equivalent (same as the following execution effect)
A:select a.*,b.* from table1 a,table2 b where a.id=b.id
B:select * FROM table1 Cross join Table2 where table1.id=table2.id (note: The Add condition after cross join can only be used where, cannot be used)

Three, cross-connect (full)

1. Concept: A cross join without a WHERE clause will produce a Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table equals the size of the Cartesian product result set. (Table1 and table2 cross-joins generate 3*3=9 Records)

2. Cross-Joins: crosses join (without conditions where ...)

3.sql statements
SELECT * FROM table1 cross join Table2
-------------Results-------------
Idnameidscore
------------------------------
1lee190
2zhang190
4wang190
1lee2100
2zhang2100
4wang2100
1lee370
2zhang370
4wang370
------------------------------
Note: Returns the 3*3=9 record, which is the Cartesian product

4. Equivalent (same as the following execution effect)
A:select * from Table1,table2

SQL multi-Table federated query