Join in SQL Server includes Inner Join and Outer Join
Inner Join Operation
A record in two tables is combined as long as there is a value that is consistent among the common fields.
grammar
From table1 inner join table2 on table1.field1 compopr Table2.field2
Inner Join operations can be divided into the following parts:
Some describe the name of Table1, Table2 records the combined table. Field1, Field2 The name of the field being coupled. If they are not composed of numbers, these fields must be the same data type and contain similar data, but they do not have the same name. CompoPR Any relationship comparative operation operator: "=," <, "">, "<=," "> =," or "<>"
Description
INNER JOIN operations can be used in the FROM clause. This is the most common connection type. As long as there is a synon value in the common fields of these two tables, the internal join will combine records in the two tables.
Example: Select * from TB1 Inner Join TB2 on tb1.id = TB2.ID and tb1.type = 'fruit'
Left Join and Right Join Operations
When used for the FROM clause, the source table record is combined.
grammar
From table 1 [left | Right] JOIN Table 2 ON Table 1. Field 1 CompoPR Table 2. Field 2
Left Join and Right Join operations can be divided into the following sections:
Some describe the name of Table1, Table2 records the combined table. Field1, Field2 The name of the field being coupled. And these fields must have the same data type and data containing the same type, but they don't need the same name. CompoPR Any relationship comparative operation operator: "=," <, "">, "<=," "> =," or "<>"
Description
Create a left external join with Left Join operation. The external connection of the left will contain all the records in the two tables starting from the first (left), even in the second (right side) table, there is no synergistic record.
Use the Right Join operation to create the right external join. The right external join will contain all records in the two tables starting from the second (right), even in the first (left) table, there is no matching record.
Example: Select * form tb1 left join tb2 on tb1.id = tb2.id
Description: Query all records in TB1, and queries TB1.ID = TB2.ID records (this is left and external connection, the right external connection is reverse)
note
To create a query that only contains the same records as the data in the join field, use the Inner Join.
You can write a nested LEFT JOIN or a Right Join in Inner Join, but you cannot write nested inner Join in an Left Join or a Right Join. Please refer to the INNER JOIN topic for discussions using nested, from which to know how to write nested joints in other connections. You can link multiple ON clauses. For more information, see the discussion of the clause links in the INNER JOIN topic.
If you try to join a field containing MEMO or OLECT data, it will cause errors. That is, the data type must match, the data type does not match how comparative conditions.
