SQL language Query Basics: Join Query union query code _MSSQL

SQL language Query Basics: Connection queries
Multiple table queries can be implemented by connecting operators. Connection is the main feature of relational database model, and it is also a symbol distinguishing from other types of database management system.
In the relational database management system, the relationship between the data in the table is not determined, and all the information of an entity is stored in a table. When retrieving data, queries the information of different entities that are stored in multiple tables through a JOIN operation. Connection operations give users a lot of flexibility, and they can add new data types at any time. Create a new table for different entities, and then query by connection.
A connection can be established in the FROM clause or a WHERE clause of a SELECT statement, and paradoxically, in the FROM clause, it helps to differentiate the join operation from the search conditions in the WHERE clause. Therefore, the use of 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)]
Where join_table indicates the name of the table participating in the JOIN operation, the connection can operate on the same table, or a multiple table operation, and the connection to the same table operation is called a self connection.
Join_type indicates the type of connection, which can be divided into three types:
Inner joins (INNER join): Use comparison operators to compare operations of some (some) column data between tables and list the rows of data in those tables that match the join condition. According to the comparison method used, the inner connection is divided into equivalent connection, natural connection and unequal connection three kinds.
Outer joins: Three kinds that are divided into left outer joins (left-hand OUTER join or ieft join), right outer join (right-hand OUTER join), and full outer join (fully OUTER join). Unlike an inner connection, the outer join lists not only the rows that match the join condition, but all the rows of data that match the search criteria in the left table (when left outer), right (when the right outer join), or two tables (when all outer joins).
Cross-Joins (CROSS join): There is no WHERE clause that returns the Cartesian product of all the rows of data 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 of data in the second table that match the query
The ON (join_condition) clause in a JOIN operation indicates the join condition, which consists of columns and comparison operators, logical operators, and so on in the connected table.
No connection can be directly connected to the text, ntext, and image data type columns, but the three types of columns may be indirectly connected. 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 connections
The INNER JOIN query operation lists the rows of data that match the join criteria, which compares the column values of the connected columns using comparison operators. The inner connection is divided into three kinds:
1. Equivalent connection: Use the equals sign (=) operator in the join condition to compare the column values of the connected columns, whose query results list all the columns in the connected table, including the repeating columns.
2. Unequal connection: The column values of the concatenated columns are compared using comparison operators other than the equals operator in the join condition. These operators include >, >=, <=, <,!>,!<, and <>.
3. Natural connection: Use the Equals (=) operator in the join condition to compare the column values of the connected columns, but it uses a select list to indicate the columns included in the query result collection, and deletes the duplicate columns from the attached table.
For example, the following uses an 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
Also, if you are using a natural connection, delete the repeating columns (city and state) in the authors and Publishers tables in the select 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
(ii) OUTER joins
In the query results collection, only rows that meet the query criteria (Where search conditions or having conditions) and join conditions are returned. When an outer join is used, it returns to the query result collection not only for rows that meet the join criteria, but also for all data rows in the left table (when the left outer join), the right table (when the right outer join), or two connection tables (all outer joins).
Connect Forum content and author information as follows using the left OUTER join:
Select a.*,b.* from Luntan left JOIN usertable as B
On A.username=b.username
The following uses an Out-of-band connection to all authors in the city table and all authors in the user table, as well as the cities in which they reside:
Select a.*,b.*
From city as a full OUTER JOIN user as B
On A.username=b.username
(iii) Cross-linking
A cross join does not take a WHERE clause, which returns the Cartesian product of all rows of data in the two tables that are connected, and the number of rows returned to the result set equals the number of rows in the first table that match the query criteria multiplied by the number of rows in the second table that match the query criteria.
For example, there are 6 categories of books in the titles table, and there are 8 publishers in the publishers table, the number of records retrieved by the following cross joins will wait
On the 6*8=48 line.
Select Type,pub_name
From titles CROSS JOIN Publishers
ORDER BY Type
SQL language Query Basics: Federated queries
The Union operator can combine a collection of query results from two or more SELECT statements into a single result set, that is, to execute a federated query. The syntax format of the Union is:
Select_statement
UNION [All] selectstatement
[UNION [All] selectstatement] [... n]
Where Selectstatement is the SELECT query statement to be joined.
The all option means that all rows are merged into the result collection. When this item is not specified, the duplicate rows in the Federated query result collection are persisted to only one row.
In a federated query, the column headings of the query results are the column headings of the first query statement. Therefore, to define a column heading must be defined in the first query statement. To sort federated query results, you must also use the column name, column heading, or column ordinal in the first query statement.
When using the union operator, you should ensure that there are the same number of expressions in the select list for each federated query statement, and that each query selection expression should have the same data type, or you can automatically convert them to the same data type. In the case of automatic conversion, the system converts a low precision data type to a high precision data type for numeric types.
In a union statement that includes multiple queries, the order of execution is from left to right, and parentheses can be used to change the order of execution. For example:
Query 1 Union (query 2 union query 3)