SQL connection, Merge, subquery

Connection: Connection is divided into internal connection, outer connection, cross connection

Both inner and outer connections are based on the Cartesian product to make some modifications.

Merge query: Two similar results can be combined together with a union.

Mysql> Select Id,time from exam to    union    select Id,time from recuit;+-----+------------+| ID  | ti Me       |+-----+------------+|   1 | 2016-08-30 | |   2 | 2016-09-10 | |   8 | NULL       | |   9 | 2016-08-31 | | 100 | 2014-08-06 | |   1 | 2016-08-31 | |   2 | 2016-08-25 | |   3 | 2016-08-31 |+-----+------------+8 rows in Set (0.14 sec)

Sub-query:

When a subquery is a single-line column: You can use a record of a subquery as one of the elements in the Where condition.

Mysql> Select ID from Recuit,    where id>    (select ID from exam where name= "Tengxun"); +----+| ID | +----+|  2 | |  3 | |  9 |+----+3 rows in Set (0.12 sec) mysql> (select ID from exam where name= "Tengxun"); +----+| ID |+----+|  1 |+----+1 row in Set (0.00 sec)

When the return value of a subquery is multiple rows and columns:

Mysql> SELECT * from Recuit,    where (id,time)    >     (select Id,time from exam W Here id=1); +----+------------+-----------------+---------+--------+| ID | Time       | process         | name    | result |+----+------------+-----------------+---------+--------+|  1 | 2016-08-31 | Wait for result | Baidu   |      1 | |  2 | 2016-08-25 | haha            | tengxun |      1 | |  3 | 2016-08-31 | Jiayou          | Wangyi  |      1 | |  9 | 2016-08-31 | Happy           | Wangyi  |      1 |+----+------------+-----------------+---------+--------+4 rows in Set (0.02 sec)

Query with keyword in, when the condition of the main query is a subquery query results, it can be judged by keyword in. Instead, you can use not in.

Select *  from table_name where inch (Select from table_name);

Query with the keyword any:

=any: The function is the same as in.

>any: Returns a record that is larger than the smallest in a subquery. As long as it is larger than one of the subqueries.

<any: Returns a record that is smaller than the largest in a subquery. As long as less than one on the line.

Query with the keyword all:

>all: Bigger than the biggest.

<all: smaller than the smallest.

With keyword exists:

exist does not actually return any records, but returns true and False, which returns true if there is at least one record in the subquery. Otherwise, it is false.

SQL connection, Merge, subquery