SQL table Connection query usage (SQL multiple table join query) _mssql

The actual project, there are multiple tables of the association relationship. It is impossible to retrieve all the data in a single table. If there is no table connection, then we need a lot of action. For example, you need to find restrictive conditions from table A to retrieve data from table B. Not only need to be divided into multiple tables to operate, but also not high efficiency. For example in the book:

Copy Code code as follows:

SELECT FId
From T_customer
WHERE fname= ' MIKE '

This SQL statement returns 2, which is the FID value of 2 for the customer whose name is Mike, so that the record of Fcustomerid equals 2 can be retrieved in T_order:

Copy Code code as follows:

SELECT Fnumber,fprice
From T_order
WHERE fcustomerid=2

Let's take a look at the table joins in detail below. There are several different types of table joins, with Cross joins (CROSS join), Inner joins (INNER join), outer joins (Outter join).

(1) INNER join (INNER join): The INNER join combines two tables, and only the data that satisfies the two-table join condition is obtained.

Copy Code code as follows:

SELECT O.fid,o.fnumber,o.fprice,
C.fid,c.fname,c. Fage
From T_order o JOIN t_customer C
On o.fcustomerid= C.fid

Note: In most database systems, the INNER in the INNER join is optional, and INNER join is the default connection method.

You can connect to only two tables when using a table connection, because there are many situations where you need to contact many tables. For example, the T_order table also needs to connect the T_customer and T_ordertype two tables to retrieve the required information, and write the following SQL statement:

Copy Code code as follows:

SELECT O.fid,o.fnumber,o.fprice,
C.fid,c.fname,c. Fage
From T_order o JOIN t_customer C
On o.fcustomerid= C.fid
INNER JOIN T_ordertype
On t_order.ftypeid= T_ordertype.fid

(2) Cross-connect (CROSS join): Cross-connect all records in all involved tables are included in the result set. There are two ways to define a cross join, which is an implicit and explicit connection.

Let's take a look at an implicit example:

Copy Code code as follows:

SELECT T_customer.fid, T_customer.fname, T_customer.fage,
T_order.fid, T_order.fnumber, T_order.fprice
From T_customer, T_order

Using an explicit connection requires the use of a cross join, as shown in the following example:

Copy Code code as follows:

SELECT T_customer.fid, T_customer.fname, T_customer.fage,
T_order.fid, T_order.fnumber, T_order.fprice
From T_customer
CROSS JOIN T_order

(3) Outer joins (Outter join): Internal connections only get data that satisfies the condition of the connection, and for external connections, this is primarily a scenario. The data that satisfies the condition is retrieved, this is not in doubt, the external connection also retrieves another part of the data, that is, the data that does not meet the condition is populated with null. Let's take a look at the classification of the outer joins: Left outer joins (left-hand OUTER join), right outer joins (right-hand OUTER join), and full outer joins (Fullouter join).

I, LEFT OUTER join: In the front, I said, the data that does not meet the condition is populated with null. \ OUTER So exactly what needs to be filled with null, for a left outer join, in which the corresponding right table field needs to be filled with a null value if the data in the left table that satisfies the condition does not match in the right. That is to say, the main body of the left outer join is the left table and the right table to match.

Copy Code code as follows:

SELECT O.fnumber,o.fprice,o.fcustomerid,
C.fname,c.fage
From T_order o
Left OUTER JOIN T_customer c
On O.fcustomerid=c.fid

Note: If you use a LEFT outer join, you can filter data that is not in conformity through the WHERE statement

Copy Code code as follows:

SELECT O.fnumber,o.fprice,o.fcustomerid,
C.fname,c.fage
From T_order o
Left OUTER JOIN T_customer c
On O.fcustomerid=c.fid
WHERE o.fprice>=150

II, right OUTER join: The right outer join is opposite to the left outer Lianbu, and the field that will be populated with null values is the left table. That is to say, the main body of the right external connection is the right table, the left table to match.

Copy Code code as follows:

SELECT O.fnumber,o.fprice,o.fcustomerid,
C.fname,c.fage
From T_order o
Right OUTER JOIN T_customer C
On O.fcustomerid=c.fid

Note: As with left outer joins, you can use the Where statement to filter

III, full external connection (Fullouter join): A full outer join is a collection of left and right outer joins. This includes both the result set of the left outer join and the result set of the right outer join.

Copy Code code as follows:

SELECT O.fnumber,o.fprice,o.fcustomerid,
C.fname,c.fage
From T_order o
Full OUTER JOIN T_customer C
On O.fcustomerid=c.fid

The result is equivalent to:

Copy Code code as follows:

SELECT O.fnumber,o.fprice,o.fcustomerid,
C.fname,c.fage
From T_order o
Left OUTER JOIN T_customer c
On O.fcustomerid=c.fid
UNION
SELECT O.fnumber,o.fprice,o.fcustomerid,
C.fname,c.fage
From T_order o
Right OUTER JOIN T_customer C
On O.fcustomerid=c.fid