Summary of database left-connected and right-connected full-connection usage

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

Table join is introduced, more specifically inner joins.
Only two records matching each other are selected for the internal join. Therefore, sometimes the records we need are not included.
To better understand this concept, we will introduce two tables for demonstration. Party and msp in the Scottish Parliament ).

Party (Code, Name, Leader)
Code: Political Party Code
Name: Name of a political party
Leader: political party Leader

Msp (Name, Party, Constituency)
Name: Name of the member
Party: Member's political Party code
Constituency: Constituency

Before introducing left join, right join, and full join, there is an important concept in the database, that is, NULL ).

Sometimes, in a table, some field values may be null because the data does not know the value or does not exist at all.
The null value is not equal to the space in the string or the value of the number type 0. Therefore, = cannot be used to determine whether a field value is null. <>
Identifier. A special phrase IS required: is null is used to select records with NULL fields. Similarly, records that do NOT contain NULL values can be selected using is not null.

For example, the following statement selects a political party without a leader. (Don't be surprised. The Scottish Parliament does have such a political party)

SELECT code, name FROM party
WHERE leader IS NULL

Another example: a Member is expelled from the party to see who he is. (That is, the political party of the member is null)

SELECT name FROM msp
WHERE party IS NULL

Let's get down to the point and see what left connection, right connection, and full connection are.

A left join contains all records in the left table, even records that do not match in the right table.
Similarly, there is also a right join (right join) that contains all records in the right table or even records that do not match it in the left table.
Full join, as its name implies, selects all records from the left and right tables.

At this point, someone may ask, what is it: contains all records in the left table, or even records that do not match it in the right table.
OK. Let's look at an instance:

SELECT msp. name, party. name
FROM msp JOIN party ON party = code

This is the Join we learned in the previous section (Note: This is also called inner join). The intention of this statement is to list the names of all members and their political parties.
You can execute this statement in http://sqlzoo.cn/4.htm to see what the result is.

Unfortunately, we found that the query results were missing two members: Canavan MSP and Dennis. Why, because these two members do not belong to any
And political parties, that is, their political Party field (Party) is null. So why can't we find it if it doesn't belong to any political party? This is because the null value is
Blame. Because the null value of the political Party field in the member table cannot be found in the political Party table for matching, that is
FROM msp JOIN party ON party = code does not connect the record, but filters it out.
In this phrase, msp is on the left of Join, and all are called left tables. Party is on the right side of Join. All Tables are called right tables.

OK. Now let's look at this sentence again. "It contains all the records in the left table or even the records in the right table that do not match it ",
It should be clear. Execute the following statement, and the two members without political parties will not be able to miss it.

SELECT msp. name, party. name
FROM msp left join party ON party = code

For the right join, you can see the query:

SELECT msp. name, party. name
FROM msp right join party ON msp. party = party. code

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

So what should we do if we want to include political parties without members and those without political parties? Right, full join ).

SELECT msp. name, party. name
FROM msp full join party ON msp. party = party. code
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.