SQL join query Summary

I. Join category

Internal Connection(For typical join operations
Or <>
). Internal Connections include equal connections and natural connections.
An internal join is performed by comparing fields with equal values (owned by the joined table ), use the matched rows as the result set (that is, the records that both tables meet the conditions are used as the result set ).

A: Intranet join is the default connection method of sqlserver. You can abbreviated innerjoin as join.

B: do not specify a null value in the connection condition, because the null value and other values are not equal.

External join
An external connection can be a left-to-external connection, a right-to-external connection, or a complete external connection.

External join will return from
At least one table or all rows in the view mentioned in the clause, as long as these rows match any where
Or having
Search criteria. Retrieve all rows in the left table referenced by the left external join and all rows in the right table referenced by the right external join. In a full external join, all rows in the two tables are returned.

(1) Left Outer Join
:Returns all records in the left table that meet the where or having conditions. If no matching record exists in the right table, null is added.

Leftjoin is equivalent to left Outer Join.

(2) Right outer join:Returns all where or
For all records with a condition specified by having, null is added when no matching record exists in the left table.

Right join
Or right Outer Join
The left Outer Join and the right outer join are reversely connected. The order of the left Outer Join and the right Outer Join cannot be changed. Otherwise, the connection direction is changed.

(3) Full join:Returns all records that meet the conditions in the left and right tables (that is, the result set is the union of the left Outer Join and the right Outer Join ).

Full join is equivalent to full outer join.
(4) Cross join:
Returns the Cartesian product of two tables (each row in the left table is combined with all rows in the right table ). (The number of records in the result set is the product of the number of records in two tables ).
Crossjoin

Ii. Restrictions on Connection

(1) You can
Or where
The internal join is specified in the clause.
Specify the external join in the clause.

(2) join conditions and where
And having
A combination of search conditions to control
The row selected in the base table referenced by the clause.

(3) In the from
Specifying a join condition in a clause helps you associate these join conditions with where
Any other search conditions that may be specified in the clause are separated, SQL-92 from
The clause join syntax is as follows:

From
First_table join_type
Second_table [ON (join_condition)]

Join_type
Specify the join type to be executed: Internal join, external join, or cross join. Join_condition
Defines the predicates used to evaluate each pair of joined rows.

(4) columns in the join condition do not have to have the same data type and the same data type, but must be compatible with the data type (that is, they can be implicitly converted ). If the data type is incompatible, explicit conversion is required.

(5) cannot
Ntext,Text
Or
Image
Join tables directly on columns. However, substring can be used.
In
Ntext,Text
Or
Image
Indirect join table on columns

(6) Any table in the inner join meets the conditions
Null records do not return (null values (if any) in the columns of the joined table do not match each other. If one of the columns of the joined table has null values, these null values can only be returned through external connections)

The following example is from
Msdn

Table1 Table2
A B c d
--------------------------
1 One null two
Null three 4 four
4 join4

Set Columns
A
Values and columns in
C
Cannot obtain a join that contains null when comparing values in
Matching results of values:

Select *
From Table1 T1 join Table2 T2
On t1.a = t2.c
Order by t1.a

Instead, only columns are returned.A
And Column
C
The median value is 4.
Of:

A B c d

----------------------------------
4 join4 4 four

(1 row (s) affected)

In addition, it is difficult to distinguish the null values returned from the base table from those returned from the external join. For example, the following select
Statement:

Select *
From Table1 T1 left Outer Join Table2 T2
On t1.a = t2.c
Order by t1.a

The following is the result set:

A B c d

----------------------------------
Null three null
1 One null
4 join4 4 four

Iii. SQL server processes the logical sequence of connections:

The query engine selects the most effective method from a variety of feasible methods to process connections. The actual execution process of various connections uses a variety of optimization methods, but the logic order is:

Application from
Clause.

Application where
The join condition and Search Condition in the clause.

Application having
Clause.

Iv. Example from msdn

 Left Outer Join.Including all items

Useadventureworks;

Go

Selectp. Name, PR. productreviewid

Fromproduction. product P

Leftouter join production. productreview PR

ONP. productid = Pr. productid

Whether or not
Productreview
Table
Productid
Match columns, left Outer Join
All include
Product
All rows in the table. Please note that there is no matching Product Review ID in the result
Product, line
Productreviewid
The column contains a null value.

Right outer join: to include all sales personnel in the result, whether or not they are assigned a region

Useadventureworks;

Go

Selectst. Name as territory, sp. salespersonid

Fromsales. salesterritory St

Rightouter join sales. SALESPERSON sp

Onst. territoryid = sp. territoryid;

The following query returns only the products that do not match the sales order and the sales orders that do not match the product (although all the sales orders in this example have matched products ).

Useadventureworks;
Go
-- The outer keyword following the full keyword is optional.
Select P. Name, sod. salesorderid
From production. product P
Full outer join sales. salesorderdetail sod
On P. productid = sod. productid
Where p. productid is null
Or sod. productid is null
Order by P. Name;