SQL multi-Table connection query

Table join is to retrieve the required data by correlating multiple tables, so let's take a look at the SQL table connection using the code example below.

In the actual project, there are multiple tables associated with the 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, it is necessary to find the 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 CodeThe code is as follows:
SELECT FId
From T_customer
WHERE fname= ' MIKE '

This SQL statement returns 2, that is, the FID value of a customer named Mike is 2, so you can retrieve a record of Fcustomerid equals 2 in T_order:

Copy CodeThe code is as follows:
SELECT Fnumber,fprice
From T_order
WHERE fcustomerid=2

Let's take a closer look at table joins. There are several different types of table joins, with Cross joins, Inner joins (INNER joins), and outer joins (Outter joins).

(1) Inner connection (INNER join): The INNER join combines two tables, and only obtains data that satisfies the two table join conditions.

Copy CodeThe code is 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 the INNER join is the default connection method.

When using a table connection, it is not limited to connecting only two tables, because there are many cases where many tables need to be contacted. 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 CodeThe code is 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 joins: Cross join all records in the table involved are included in the result set. There are two ways to define a cross-connection, namely, implicit and explicit.

Let's take a look at an implicit example:

Copy CodeThe code is 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 in the following example:

Copy CodeThe code is 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 join (Outter join): The internal connection only obtains the data that satisfies the connection condition, but for the external connection, the main solution is such a scenario. The data that satisfies the condition is retrieved, there is no doubt that the external connection also retrieves another part of the data, which is to populate the data that does not satisfy the condition with NULL. Let's take a look at the classification of the outer joins: Left OUTER join (OUTER join), right outer join (OUTER join), and full outer join (Fullouter join).

I, left OUTER join: The front also says that data that does not satisfy the condition is filled with null. So specifically what needs to be filled with null, for left outer joins, if the data of the left table that satisfies the condition does not match in the right table, the corresponding right table field needs to be filled with null values. That is, the left outer joins the main body is the left table, the right table to match.

Copy CodeThe code is 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 the non-conforming data through the where statement

Copy CodeThe code is 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 external connection (right OUTER join): The left outer join is opposite to the left Lianbu, and the null value will be filled with the field of the table. That is, the right outer joins the body is the right table, the left table to match.

Copy CodeThe code is 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 the left outer join, you can use the Where statement to filter

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

Copy CodeThe code is 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 CodeThe code is 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=======================================================================================

Multiple table Query SQL notation: (The following is a query from two tables < can also be seen from the three table query, show all the fields in V_goods, display the Name field of the Admin2 tables as the add person, show the table admin2 table Name field as the operator)queries for multiple tables can be written in the following three examples of SQLSelect V.*, (select A.name from Admin2 a where a.adminid=v.loadinid) as Aname, (select A.name from Admin2 a where a.adminid= V.operatorid) as uname from V_goods v where 1=1; SELECT v.*,a.name aname,b.name uname from v_goods v,admin2 a,admin2 b WHERE A.adminid=v.loadinid and B.adminid=v.operator Id;
SELECT v.*,a.name aname,b.name uname from V_goods v left joins admin2 A on A.adminid=v.loadinid left joins Admin2 B on B.ad Minid=v.operatorid;

SQL multi-table connection query