SQL SERVER Multi-table operations internal connections, outer joins

MSSQL database is divided into natural connection, inner connection, outer connection

1, natural connection and inner connection are basically the same. The difference is that the natural connection "=" on both sides of the column property values must be the same, the internal connection can be different, as long as the reading of the same data.

Natural connection eg. Select worker. Employee number, depart. Department from Worker,depart where worker. Department Number = depart. Department Number

2, internal connection. The INNER JOIN keyword is generally used to denote an inner connection, and inner is not required to be writable. In addition to the JOIN keyword, you must also use the on or using keyword

The INNER JOIN query operation lists the data rows that match the link criteria, and it uses comparison operators to compare the column values on both sides of the connection.

eg. Select worker. Employee number, depart. Department from worker JOIN depart on worker. Department Number = depart. Department Number ( can be different column name, just compare values )

3, outer connection. Also divided into left, linked, fully connected

Left join Connect. Left OUTER join On the left side of the main table, right from the table. Each row of data in the main table matches each row of data from the table, if a row can be found, the matching result is returned, no rows found, the main table returns data directly, and the table is filled with null

Right connection. Right OUTER joins on. is similar to the left query.

eg.

Table A (A1,B1,C1) b (A2,B2)
A1 B1 C1 A2 B2
01 Mathematics 95 01 Sheets Three
02 Language 90 02 John Doe

03 English 80 04 Harry

Select a.*,b.* from A
Left outer join B on (A.A1=B.A2)
The result is:
A1 B1 C1 A2 B2
01 Mathematics 95 01 Sheets Three
02 Language 90 02 John Doe
03 English NULL NULL

Select a.*,b.* from A
Right outer join B on (A.A1=B.A2)
The result is:
A1 B1 C1 A2 B2
01 Mathematics 95 01 Sheets Three
02 Language 90 02 John Doe
Null NULL NULL 04 Harry

SQL SERVER Multi-table operations internal connections, outer joins