Database left connection Full Connection Usage Summary _ database other

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, outer, and Cross joins. The INNER join (INNER join) uses the comparison operator to compare operations of some (some) column data between tables and lists 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.

An outer join is divided into three kinds of left outer joins (either the right OUTER join or the Ieft join), the right-hand outer join (the OUTER join, or the OK join), and the full outer join (either a fully OUTER join or a complete 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).

A cross join (CROSS JOIN) does not have a WHERE clause that returns the Cartesian product of all rows of data in the join table, with the number of rows in the result set equal to the number of 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 criteria

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), right table (when the right outer join), or two side tables (full 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 equals 6*8=48 rows.

SELECT Type,pub_name
From titles CROSS JOIN Publishers
ORDER BY Type

Describes the table connection, or rather the inner joins inner connection.
The inner joins only select records that match each other in two tables. So, this leads to sometimes the records we need are not included.
For a better understanding of this concept, we introduce two tables for demonstration. The party table and the Parliamentarians (MSP) of the Scottish Parliament.

Party (Code,name,leader)
Code: Party Codes
Name: Party names
Leader: Party leader

MSP (Name,party,constituency)
Name: member name
Party: Members of the Political parties code
Constituency: Constituency

Before introducing left JOIN, right and full connections, there is an important concept in the database to introduce, that is, null values.

Sometimes a table, or rather some field value, may appear null because the data does not know what the value is or does not exist at all.
A null value is not equivalent to a space in a string, nor is it 0 of a numeric type. Therefore, it is not possible to use =,<> when determining whether a field value is a null value
The judgment character. Must have a dedicated phrase: Is NULL to select a record with a null value field, and in the same vein, you can select records that do not contain null values using is instead null.

For example, the following statement selects a party without a leader. (Don't be surprised that there are such parties in the Scottish Parliament)

SELECT code, name from party
WHERE leader is NULL

Another example: A member was expelled from the party to see who he was. (That is, the member's party is a null value)

SELECT name from MSP
WHERE is NULL

Well, let's get to the bottom of this and see what's called LEFT JOIN, right and full connection.

A LEFT join, which contains all of the records in the left table, or even records in the right table that do not match it.
In the same way, there is a right join of the same reason (the right-hand connection), which contains all the records in the right table or even the table in the left side that does not match it.
Full join, as the name suggests, will be selected for all records in the left and right tables.

In this case, one might ask, what exactly is called: a record that contains all the tables in the left table and even a record in the right table that does not match it.
Ok, let's take a look at an example:

SELECT Msp.name, Party.name
From MSP JOIN Party on Party=code

This is the join we learned in the previous section (note: Also called INNER join), which is meant to list the names of all the members and the political parties.
You can execute the statement personally in Http://sqlzoo.cn/4.htm to see what the result is.

Unfortunately, we found that the results of this query were less than two members: Canavan MSP, Dennis. Why, since these two councillors do not belong to either
and political parties, i.e. their party fields (parties) are null values. So why not belong to any political party to find out? This is because the null value is
Mischief. Because the null value of the party field in the member's table cannot find the corresponding record in the party table, i.e.
From the MSP join Party on Party=code did not connect the record, but filtered it out.
In this phrase, the MSP is on the left side of the join, all called the left table. Party on the right side of the join, all called Right table.

OK, now take a look at this sentence, "Include all the records in the left table and even the table on the right that doesn't match it."
The meaning should be quite clear. By executing the following statement, the two members without a political party could not be missed.

SELECT Msp.name, Party.name
From MSP left JOIN party on Party=code

About the right connection, look at this query to understand:

SELECT Msp.name, Party.name
From MSP right JOIN party on Msp.party=party.code

The results of this enquiry list all members and political parties, including political parties without members, but not members without political parties.

What about a party that has no members, and a member without a political party? Yes, full join.

SELECT Msp.name, Party.name
From MSP full JOIN party on Msp.party=party.code
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.