MySQL Federated, connection query

First, joint query Union, INTERSECT, EXCEPT

　　The union operator can combine query result collections of two or more two SELECT statements into a single result set, which is the execution of a federated query. The syntax format for union is:
Select_statement
Union [all] selectstatement [union [all] SELECTSTATEMENT][...N]

Where Selectstatement is the SELECT query statement to be federated.
The all option means that all rows are merged into the result collection. when the item is not specified , only one row is retained by the repeating row in the result collection of the union query.
When you federate a query, the column headings of the query result are the column headings of the first query statement . Therefore, to define a column header, you must define it in the first query statement. To sort the results of a union query, you must also use the column name, column header, or column ordinal in the first query statement. When you use the union operator, you are guaranteed to have the same number of expressions in the select list for each union query statement , and each query selection expression should have the same data type, or you can automatically convert them to the same data type. When auto-converting, for numeric types, the system converts low-precision data types to high-precision data types.

In union statements that include multiple queries, the order in which they are executed is from left to right , using parentheses to change the order of execution . For example:
Query 1 Union (query 2 union query 3)

Intersect,except

Intersect is the intersection. Except is the meaning of the difference set

Select user_id from the user intersect select user_id from toy;
is to return the same portion of the two select queries. (This is the user_id of children with toys)

Select user_id from the user except select user_id from toy;
Is the part that returns the first select minus the second select result. (This is the user_id of children without toys)

Second, connection query

You can implement multiple table queries by using the Join operator. Connection is the main feature of relational database model, and it is also a sign that distinguishes it from other types of database management system.

The Union statement in standard SQL is as follows:

Select COLUMN_NAME (s) from table_name1 UNION SELECT column_name (s) from table_name2

There are prerequisites: the contents of each select (table items) must be the same structure. In detail, the number of columns in a linked table must be the same as the column properties of the column. And the column names can be different (the same structure)

First cross-connect: crosses join

Select U.username, t.toyname from the user as U cross join toy as T;
A cross connection is the value one by one for all the first and second tables.
For example: If the first table has 5 values and the second table has 4 values, the result should be 20 records.

INNER JOIN: INNER JOIN equal connection: (equal join)

Select U.username, t.toyname from the user as U inner join toy as T on (can also be used where) u.user_id = t.user_id;
The result is a toy of their own.

Unequal connections: (non-equal join)

Select U.username, t.toyname from user as U inner join toy as T on (where possible) u.user_id <> t.user_id ORDER by U.user Name
The result is that they don't have a toy.

Natural connection: (natural join)

This is used when the names of the columns in the connected two tables are equal.

1	<code class=" hljs sql">selectu.username, t.toyname from user as u natural join toy ast; //和上面的相等连接的结果是一样的。</code>

Outer joins: OUTER JOIN

The left outer join and right outer joins are similar: outer join; Right outer join;

12	<code class= "Hljs sql" > select u.usrename, t.toyname from user u inner join toy T on (cannot use where //This is to query the user name and the name of the toy he owns </code>

Select U.username, t.toyname from the user U left outer joins toy T on u.user_id = t.user_id;
This is based on left table (user), to query all user names and the names of the toys he owns.

Select U.username, t.toyname from the user U right outer joins toy T on u.user_id = t.user_id;
This is based on the right table (toy), to query the owner name of all toys, null for no owner

MySQL Federated, connection query