Understanding T-SQL: Join statements

1. join queries include the following types:

Inner join/Outer Join/full join/cross join
The following describes the usage of these join statements.

 

2. About Data Tables

The premise of this discussion is based on the following two data tables
●Northwind. Employees

Employeeid lastname firstname city country reportsto
----------------------------------------------------------------------------------
1 davolio Nancy Seattle USA 2
2 fuller Andrew Tacoma USA null
3 leverling Janet Kirkland USA 2
4 peaco CK Margaret Redmond USA 2
5 Buchanan Steven London UK 2
6 suyama Michael London UK 5
7 King Robert London UK 5
8 Callahan Laura Seattle USA 2
9 dodsworth Anne London UK 5

The preceding employee information includes information such as ID, name, surname, city, country, and leader ID (reportsto ).

 

● Northwind. Products

Productid productname categoryid unitprice
-----------------------------------------------------------------------------------
1. Chai 1 18.00
2 Chang 1 19.00
3 aniseed syrup 2 10.00
4 chef Anton's Cajun seasoning 2 22.00
5 chef Anton's gumbo mix 2 21.35
6 Grandma's boysenberry Spread 2 25.00
7 Uncle Bob's organic dried pears 7 30.00
8 Northwoods cranberry sauce 2 40.00
9 mishi Kobe niku 6 97.00
10 ikura 8 31.00
11 queso Cabrales 4 21.00
12 queso Manchego La Pastora 4 4 38.00
13 konbu 8 6.00

The item table contains information such as ID, item name, type ID, and unit price.

 

● Northwind. Categories

Categoryid categoryname description
-----------------------------------------------------------------
1 beverages soft drinks, coffees, teas, beers, and Ales
2 condiments sweet and savory sauces, relishes, spreads, and seasonings
3 confections desserts, candies, and sweet breads
4 dairy products cheeses
5 grains/cereals breads, crackers, pasta, and cereal
6 meat/poultry Prepared meats
7 produce dried fruit and bean curd
8 seafood seaweed and fish

The preceding table types include the category ID, category name, and description.

 

3. Inner join

Inner join is the most common join type. Records are matched together based on one or more public fields. Inner join only returns records matching the joined fields.
for example, select * from products inner join categories on products. categoryid = categories. categoryid
the preceding statement only returns the number of records in which the category ID in the item table matches the ID in the category table. Such a statement is equivalent to:
select * from products, categories where products. categoryid = categories. categoryid [in this form, you are familiar with it]
Inner join is used for exclusion. Any row does not match in two tables and is destined to be removed from the result set. (I think it is equivalent to taking the intersection of the two sets. The condition for this intersection is the limit after the on)

note that not only can two tables be joined, but a table can be joined with itself. Let's take the employee table as an example:
employee ID employee name parent ID parent name
---------- --------- ---------- ----------- -------- -----------
easy operation through inner join:

 SelectE. employeeid, E. lastname, E. firstname, R. employeeid, R. lastname, R. firstname
FromEmployees EInner JoinEmployees ROnE. employeeid=R. employeeid

The simpler writing method can be as follows:

Select E. employeeid, E. lastname, E. firstname, R. employeeid, R. lastname, R. firstname
From Employees E, employees R Where E. reportsto = R. employeeid

 

4. Outer Join

Outer Join includes left Outer Join and right Outer Join. In fact, it can be abbreviated as left join and right join.
What are the differences between the two join statements and inner join statements?
Let's look at the example of employees:

Select E. employeeid, E. lastname, E. firstname, R. employeeid, R. lastname, R. firstname
From Employees E Inner   Join Employees R One. reportsto = R. employeeid

Employeeid lastname firstname
----------------------------------------------------------------------------------
1 davolio Nancy 2 fuller Andrew
3 leverling Janet 2 fuller Andrew
4 peaco CK Margaret 2 fuller Andrew
5 Buchanan Steven 2 fuller Andrew
6 suyama Michael 5 Buchanan Steven
7 King Robert 5 Buchanan Steven
8 Callahan Laura 2 fuller Andrew
9 dodsworth Anne 5 Buchanan Steven

 

Select E. employeeid, E. lastname, E. firstname, R. employeeid, R. lastname, R. firstname
From Employees E Left   Outer   Join Employees R On E. reportsto = R. employeeid

Employeeid lastname firstname
----------------------------------------------------------------------------------
1 davolio Nancy 2 fuller Andrew
2 fuller Andrew null
3 leverling Janet 2 fuller Andrew
4 peaco CK Margaret 2 fuller Andrew
5 Buchanan Steven 2 fuller Andrew
6 suyama Michael 5 Buchanan Steven
7 King Robert 5 Buchanan Steven
8 Callahan Laura 2 fuller Andrew
9 dodsworth Anne 5 Buchanan Steven

Select E. employeeid, E. lastname, E. firstname, R. employeeid, R. lastname, R. firstname
From Employees E Right   Outer   Join Employees R On E. reportsto = R. employeeid

Employeeid lastname firstname
----------------------------------------------------------------------------------
Null null 1 davolio Nancy
1 davolio Nancy 2 fuller Andrew
3 leverling Janet 2 fuller Andrew
4 peaco CK Margaret 2 fuller Andrew
5 Buchanan Steven 2 fuller Andrew
8 Callahan Laura 2 fuller Andrew
Null null 3 leverling Janet
Null null 4 peaco CK Margaret
6 suyama Michael 5 Buchanan Steven
7 King Robert 5 Buchanan Steven
9 dodsworth Anne 5 Buchanan Steven
Null null 6 suyama Michael
Null null 7 King Robert
Null null 8 Callahan Laura
Null null 9 dodsworth Anne

 

Have you seen the above differences?

Left join and right join must understand and distinguish between left and right tables. A can be seen as the left table, and B can be seen as the right table.
Left join is based on the left table ., all records in the left table (a) are displayed, while the right table (B) only displays records that meet the search criteria (in this example:. aid = B. bid ). all records in Table B are null.
The results of right join and left join are exactly the opposite. This time, the right table (B) is used as the basis. If table A is insufficient, null is used for filling.

 

5. Full join

Full join is equivalent to connecting left and right to tell SQL Server to contain all rows on both sides of the left and right, which is equivalent to the union operation in the set.

6. Cross join

Unlike other join operations, the on operator does not exist. It joins each record in the table on one side of the join operation with all the records in the table on the other side, the result is the Cartesian product of all records in the two tables.