Article From:https://www.cnblogs.com/zzq123/p/9967790.html

1      Connection query (multi-table query)

1.1Cross connect

Syntax:

from  Table 1 [cross] join table 2; // visible cross-join is just no on condition.

crossThe word can also be omitted or replaced by the word inner.

Example: /* Cross-connect (Table 1 Number * Table 2 Number)*/

SELECT * FROM emp JOIN dept;

SELECT * FROM emp;

SELECT * FROM dept;

There is a false data EMP in which each record matches the data in Dept.

In fact, crosslinking is the result of joining two tables without setting any conditions.

1.2         Internal connection

Syntax:

from  Table 1 [inner] join table 2 on table 1. Field 1 = Table 2. Field 2;

Meaning: Find out (filter) rows whose values of field 1 in Table 1 in the cross-linked result table are equal to those of field 2 in table 2.

When the field names in two tables are the same, the table names are preceded by the table names.

1.3 Left [outer] connection

Form:

from  Table 1 left [outer] join table 2 on join conditions.

Explain:

1，Here, left is the key word.

2，The connection condition is the same as the inner connection.

3，The meaning is: on the basis of the results of the inner join, and with all the data in the left table that does not meet the join criteria, the corresponding position of the field in the right table is automatically filled with the value of “null”.

1.4Right join

The right connection is the opposite of the left connection:

Form:

from  Table 1 Right [outer] join table 2 on join conditions.

Explain:

1      Here, right is the key word.

2，The connection condition is the same as the inner connection.

3，The meaning is: on the basis of the results of the inner join, and with all the data in the right table that does not meet the join criteria, the corresponding position of the field in the left table should be automatically filled with the value of “null”.

1.5         All external connections

Form:

from  Table 1 Full [outer] join table 2 on join conditions;

Explain:

1，Meaning: In fact, it is the union of left and right joins (eliminating duplicates), that is, the result of inner joins, plus all the rows that do not satisfy the conditions in the left table (right corresponding to complement null), plus all the rows that do not satisfy the conditions in the right table (left corresponding to complement null).

2，mysqlIn fact, we do not know the full [foreign] connection grammar, that is, MySQL itself does not support the full connection grammar.

3，This concept exists in other databases and can be understood.

1.6 join query

Raw data:

1：Find out the category name of Sony’s 4G mobile phone:

1.6  Subquery

Subquery is to treat the result of one query as the condition of another query.

1Find out the category name of Sony’s 4G mobile phone:

2Find out all the products that belong to mobile phone digital

### Using in subqueries

inThe basic grammatical forms are:

where  Operator in (value 1, value 2,…)

Then in sub-query is:

where  Operator in (column subquery);

Meaning:

Represents that the operand (field value) is equal to any of the subqueries, even if the condition is satisfied.

Find out all products that belong to mobile phone digital or brain office: