SQL statement query/connection query/Multi-Table connection Query

1. Simple Query
　　
A simple Transact-SQL query only includes the select list, from clause, and where clause. They indicate the queried columns, the queried tables or views, and the search conditions.
　　
For example, the following statement queries the nick name and email fields in the testtable table with the name "Zhang San.

　　
Select nickname, email
From testtable
Where name = 'zhangsan'

　　
(1) Select a list
　　
Select_list indicates the queried column, which can be a list of column names, asterisks, expressions, and variables (including local variables and global variables.
　　
1. Select all columns
　　
For example, the following statement shows the data of all columns in the testtable table:

　　
Select *
From testtable

2. Select some columns and specify their display order
　　
The data in the query result set is arranged in the same order as the column name in the selected list. For example:

　　
Select nickname, email
From testtable

3. Change the column title
　　
In the selection list, you can re-specify the column title. Definition Format:
　　
Column Title = column name
Column name column title
　　
If the specified column title is not in the standard identifier format, use the quotation mark. For example, the following statement displays the column title in Chinese characters:

　　
Select nickname = nickname, email = Email
From testtable

　　
4. Delete duplicate rows
　　
Use the all or distinct option in the SELECT statement to display all rows that meet the conditions in the table or delete duplicate data rows in the table. The default value is all. When the distinct option is used, only one row is retained for all duplicate data rows in the result set returned by the SELECT statement.
　　
5. Limit the number of returned rows
　　
Use the Top N [percent] Option to limit the number of returned data rows. Top N indicates that N rows are returned, while Top N indicates that the score is one hundred, specify the number of returned rows equal to a few percent of the total number of rows. For example:

　　
Select Top 2 * From testtable select top 20 percent * From testtable

(2) From clause
　　
The from clause specifies the tables or views related to the query of select statements. Up to 256 tables or views can be specified in the from clause. They are separated by commas.
　　
When the from clause specifies multiple tables or views at the same time, if the same column exists in the selected list, you should use the object name to limit the table or view to which these columns belong. For example, if the usertable and citytable tables contain the cityid column at the same time, the following statement format should be used to limit the cityid column when querying the two tables:

　　
Select username, citytable. cityid
From usertable, citytable
Where usertable. cityid = citytable. cityid

You can specify aliases for tables or views in the from clause in the following two formats:

　　
Table name as Alias
Table Name alias

　　
For example, the alias format of the table available in the preceding statement is as follows:

　　
Select username, B. cityid
From usertable A, citytable B
Where a. cityid = B. cityid

Select can not only retrieve data from tables or views, but also query data from the result set returned by other query statements. For example:

　　
Select a. au_fname + A. au_lname
From authors A, titleauthor Ta
(Select title_id, title
From titles
Where ytd_sales> 10000
) As T
Where a. au_id = TA. au_id
And ta. title_id = T. title_id

In this example, the result set returned by select is given an alias T, and then the data is retrieved.

(3) Use the WHERE clause to set query conditions
　　
The where clause sets query conditions to filter out unwanted data rows. For example, the following statement queries data older than 20:

　　
Select *
From usertable
Where age> 20

The where clause can contain various conditional operators:
　　
Comparison operators (size comparison):>, >=, =, <, <=, <>,!> ,! <
Range operator (whether the expression value is in the specified range):... And...
Not... And...
List operator (determines whether the expression is a specified item in the list): In (item 1, item 2 ......)
Not in (item 1, item 2 ......)
Pattern match character (determine whether the value matches the specified character wildcard format): Like, not like
Null Value identifier (whether the expression is null): Is null, not is null
Logical operators (for multi-condition logical connections): Not, And, or
　　
1. Range operator example: age between 10 and 30 is equivalent to age> = 10 and age <= 30
　　
2. List operator example: Country in ('Germany ', 'China ')
　　
3. pattern matching example: it is often used for fuzzy search to determine whether the column value matches the specified string format. It can be used for char, varchar, text, ntext, datetime, smalldatetime, and other types of queries.
　　
You can use the following wildcard characters:
　　
Percent sign %: It can match characters of any type and length. If it is Chinese, use two percent signs (%.
　　
Underline _: matches any character. It is often used to limit the character length of an expression.
　　
Square brackets []: Specifies a character, string, or range. The matching object must be any of them. [^]: The value is [] the same, but the matching object must be any character other than the specified character.
　　
For example:
　　
End with Hing, use like '% Hing'
　　
It must start with a: Like '[a] %'
　　
Restrict the outer class starting with a: Like '[^ A] %'
　　
4. null value judgment operator example where age is null
　　
5. logical operators: the priority is not, and, or
　　
(4) sorting of query results
　　
Use the order by clause to sort the query results by one or more columns. The syntax format of the order by clause is:

　　
Order by {column_name [ASC | DESC]} [,… N]

　　
ASC indicates Ascending Order, which is the default value and desc indicates descending order. Order by cannot be sorted by ntext, text, or image data type. For example:

　　
Select *
From usertable
Order by age DESC, userid ASC

In addition, you can sort data by expressions.

2. Joint Query
　　
The Union operator can combine the query result sets of two or more select statements into one result set for display, that is, the Union query is executed. The syntax format of union is:

　　
Select_statement
Union [all] selectstatement
[Union [all] selectstatement] [… N]

Selectstatement is the SELECT query statement to be combined.
　　
The all option combines all rows into the result set. If this parameter is not specified, only one row is retained for the duplicate row in the Union query result set.
　　
During a joint query, the column title of the query result is the column title of the first query statement. Therefore, to define a column title, it must be defined in the first query statement. To sort the Union query results, you must also use the column name, column title, or column number in the first query statement.
　　
When using the Union operator, ensure that there are the same number of expressions in the selection list of each joint query statement, and each query selection expression should have the same data type, or they can be automatically converted to the same data type. During automatic conversion, the system converts low-precision data types to high-precision data types.
　　
In Union statements that contain multiple queries, the execution sequence is from left to right. Brackets can be used to change the execution sequence. For example:
　　
Query 1 Union (query 2 Union query 3)

Iii. 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 cross join will be 6*8 = 48 rows.

　　
Select Type, pub_name
From titles cross join publishers
Order by type

Database Connection Query

The join result selects data that meets the connection conditions from the combination of two or more tables. If the data cannot meet the connection conditions, it is discarded. This method is usually called

Method: innerjoin ). In the internal connection, the table participating in the connection is equal. The method opposite to the internal connection is called the external join)

. In an external connection, tables that participate in the connection have the master-slave relationship. each row of data in the master table matches the data columns in the slave table. Data that meet the connection conditions is directly returned to the result set.

For those columns that do not meet the connection conditions, the column will be filled with null values and then returned to the result set (for bit-type columns, the BIT data type does not allow null values, so

Will be filled with 0 values and then returned to the results ).

External connections are divided into left Outer Join and right outer join. Differentiate external connections in the direction of the primary table.

The table on the left is called the left external join, and the master table on the right is called the right external join.

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

The connection operation queries the information of different entities stored 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 to pointing out in the from clause that the connection operation is helpful for searching in the WHERE clause.

The conditions are separated. 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 perform data in one or more columns between tables.

And list the data rows that match the connection conditions in these tables. According to the comparison method used, internal connections are classified into equivalent connections, natural connections, and unequal

Three types of connections.

Outer Join is divided into left Outer Join or left join, right Outer Join or right join, and full outer join or

Full join. Different from internal connections, external connections not only list rows that match the connection conditions, but also list the left table (when the left Outer Join is performed), 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 that in the first table.

The number of rows in the query condition multiplied by the number of rows in the second table that meet the query condition.

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. However, you can indirectly connect these columns.

Database multi-Table connection query details

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.

(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.

Outer Join can be left Outer Join, right outer join, or complete external join.
When an external join is specified in the from clause, it can be specified by one of the following sets of keywords: left join or left Outer Join; right join or right outer join; full join or full outer join.

(1) left Outer Join: 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 selection list columns in the right table in the row of the associated result set are null.

(2) Right outer join: Right outer join is a reverse join of 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.

(3) Complete External join: 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.

Rows are returned only when at least one row in the same two tables meets the join conditions. The inner join removes rows that do not match any row in the other table. The outer join will return all rows of at least one table or view mentioned in the from clause, as long as these rows meet any where or having search conditions. Searches all rows in the left table referenced by the left Outer Join and all rows in the right table referenced by the right outer join. All rows of the two tables in the complete external join will be returned.

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