SQL multi-table joint Query

Join is rarely used. I learned this time and forgot two articles!

 

From: http://hcx-2008.javaeye.com/blog/285661

 

Connection Query

You can use the join operator to query multiple tables. Connection is the main feature of the relational database model and a symbol that distinguishes it from other types of database management systems.

In the relational database management system, the relationship between data does not have to be determined when a table is created, and all information about an object is often stored in a table. When retrieving data, you can use the join operation to query information about different entities in multiple tables. Connection operations bring great flexibility to users. They can add new data types at any time. Create new tables for different entities and then query them through connections.

The connection can be established in the from clause or where clause of the SELECT statement. It is similar that it helps to distinguish the connection operation from the search conditions in the WHERE clause when the clause points out the connection. Therefore, this method is recommended 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)]

Join_table indicates the name of the table involved in the join operation. The join operation can be performed on the same table or on multiple tables. The Join Operation on the same table is also called a self-Join Operation.

Join_type indicates the connection type, which can be divided into three types: internal connection, external connection, and cross connection. Inner join uses a comparison operator to compare data in some columns of a table, and lists the data rows in these tables that match the connection conditions. According to the comparison method used, internal connections are classified into equivalent connections, natural connections, and unequal connections.

Outer Join is divided into three types: left Outer Join (left Outer Join or left join), right Outer Join (right Outer Join or right join), and full outer join (full outer join or full join. Different from internal connections, external connections not only list the rows that match the connection conditions, but also list the left table (when the left Outer Join is performed) and the right table (when the right outer join is performed) or all data rows that meet the search criteria in two tables (when the table is fully connected.

Cross join does not have a where clause. It returns the Cartesian product of all data rows in the join table, the number of rows in the result set is equal to the number of rows that meet the query conditions in the first table multiplied by the number of rows that meet the query conditions in the second table.

The ON (join_condition) clause in the Join Operation specifies the join condition, which consists of columns, comparison operators, and logical operators in the connected table.

No matter which connection is used, you cannot directly connect the columns of the text, ntext, and image data types, but you can indirectly connect these 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)

(1) inner connection

The inner join query operation lists the data rows that match the connection condition. It uses the comparison operator to compare the column values of the connected columns. Intranet connections are divided into three types:

1. equijoin: Use the equal sign (=) operator in the connection condition to compare the column values of connected columns. All columns in the connected table, including duplicate columns, are listed in the query results.

2. Unequal join: Use a comparison operator other than the equal operator to compare the column values of the connected columns. These operators include >,>=, <=, <,!> ,! <And <>.

3. Natural join: Use the equal to (=) operator in the connection condition to compare the column values in the connected column. However, it uses the selection list to indicate the columns included in the query result set, delete duplicate columns in the connection table.

For example, the following uses equijoin 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 duplicate columns (city and state) in the authors and publishers tables in the selection list ):

Select a. *, P. pub_id, P. pub_name, P. Country

From authors as a inner join publishers as P

On a. City = P. City

(2) External Connection

Only the rows that meet the query conditions (where search conditions or having conditions) and connection conditions in the returned query result set are returned. When an external connection is used, it returns to the query result set that contains not only rows that meet the connection conditions, but also the left table (when the left outer connection is used) and the right table (when the right outer connection is used) or all data rows in two edge join tables (all Outer Join.

For example, use the left outer link to connect the Forum content to the author information:

Select a. *, B. * From luntan left join usertable as B

On a. Username = B. Username

Next we will use a full outer join to list all the authors in the city table, all the authors in the User table, and their cities:

Select a. *, B .*

From City as a full outer join user as B

On a. Username = B. Username

(3) cross join

A crossover clause without a where clause returns the Cartesian product of all data rows in the two joined tables, the number of rows returned to the result set is equal to the number of rows that meet the query conditions in the first table multiplied by the number of rows that meet the query conditions in the second table.

For example, if there are 6 types of books in the titles table, and there are 8 publishers in the publishers table, the number of records retrieved by the following crossover will be

Rows 6*8 = 48.

Select Type, pub_name

From titles cross join publishers

Order by type

 

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

On the 12th floor, aw511 (point-and-star light) replied to 09:13:42, 2006-08-22, with a score of 0. query Analyzer:

 
-- Create Table Table1, Table2:
Create Table Table1 (ID int, name varchar (10 ))
Create Table Table2 (ID int, score INT)
Insert into Table1 select 1, 'lil'
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 |
-------------------------------------------------

Run the following commands in the query Analyzer:

1. External Connection
1. Concept: including left Outer Join, right Outer Join or complete external join

2. Left join: left join or left Outer Join
(1) The result set of the left Outer Join includes all rows in the left table specified in the left outer clause, not just the rows matched by the join column. If a row in the left table does not match a row in the right table, all the selection list columns in the right table in the row of the associated result set are null ).
(2) SQL statements
Select * From Table1 left join Table2 on table1.id = table2.id
------------- Result -------------
Idnameidscore
------------------------------
1lee190
2zhang2100
4 wangnullnull
------------------------------
Note: all the clauses containing Table 1 return the corresponding fields of Table 2 based on the specified conditions. The non-conforming fields are displayed as null.

3. Right join: Right join or right Outer Join
(1) The right outer join is the reverse join of the left Outer Join. All rows in the right table are returned. If a row in the right table does not match a row in the left table, a null value is returned for the left table.
(2) SQL statements
Select * From Table1 right join Table2 on table1.id = table2.id
------------- Result -------------
Idnameidscore
------------------------------
1lee190
2zhang2100
Nullnull370
------------------------------
Note: all the clauses containing Table 2 return the corresponding fields of Table 1 Based on the specified conditions. The non-conforming fields are displayed as null.

4. Complete External join: Full join or full outer join
(1) The Complete External join returns all rows in the left and right tables. If a row does not match a row in another table, the selection list column of the other table contains a null value. If there are matched rows between tables, the entire result set row contains the data value of the base table.
(2) SQL statements
Select * From Table1 full join Table2 on table1.id = table2.id
------------- Result -------------
Idnameidscore
------------------------------
1lee190
2zhang2100
4 wangnullnull
Nullnull370
------------------------------
Note: returns the sum of left and right connections (see upper left and right connections)

2. Internal Connection
1. Concept: inner join is a join that uses a comparison operator to compare the values of the columns to be joined.

2. Inner join: Join or inner join

3. SQL statements
Select * From Table1 join Table2 on table1.id = table2.id
------------- Result -------------
Idnameidscore
------------------------------
1lee190
2zhang2100
------------------------------
Note: Only the Table1 and Table2 columns that meet the conditions are returned.

4. equivalent (same as the following execution)
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: cross join can only use where, not on)

Iii. Cross-join (complete)

1. Concept: A cross join without a where clause will generate the 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 is equal to the size of the Cartesian result set. (Table1 and Table2 generate 3*3 = 9 records)

 2. Cross join: cross join (without the condition where ...)

3. SQL statements
Select * From Table1 cross join Table2
------------- Result -------------
Idnameidscore
------------------------------
1lee190
2zhang190
4wang190
1lee2100
2zhang2100
4wang2100
1lee370
2zhang370
4wang370
------------------------------
Note: 3*3 = 9 records are returned, that is, Cartesian product.

4. equivalent (same as the following execution)
A: Select * From Table1, Table2