SQL table joins

Give yourself literacy every day and become more knowledgeable.

To continue with the programmer's SQL code, let's take a look at the table connection related content. The knowledge of the table connection is very widely used in the actual project development.

The so-called table connection is to retrieve the required data by correlating multiple tables. 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:

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:

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.

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:

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:

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:

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