Mysql union, connection query, Mysql union, connection

Mysql union, connection query, Mysql union, connection

I. UNION query, INTERSECT, EXCEPT T

　　UNIONYou can combine the query result sets of two or more Select statements into one result set for display. The syntax format of UNION is:
Select_statement
UNION [ALL] selectstatement [UNION [ALL] selectstatement] [… N]

Selectstatement is the Select query statement to be combined.
The ALL option combines ALL rows into the result set. If this parameter is not specified, only one row is retained for the duplicate row in the Union query result set.
During a joint query, the column title of the query result is the column title of the first query statement. Therefore, to define a column title, it must be defined in the first query statement. To sort the Union query results, you must also use the column name, column title, or column number in the first query statement. When using the UNION operator, ensure that there are the same number of expressions in the selection list of each joint query statement, and each query selection expression should have the same data type, or they can be automatically converted to the same data type. During automatic conversion, the system converts low-precision data types to high-precision data types.

In UNION statements that contain multiple queries, the execution sequence is from left to right. Brackets can be used to change the execution sequence. For example:
Query 1 UNION (query 2 UNION query 3)

INTERSECT, EXCEPT T

INTERSECT indicates the intersection. EXCEPT t indicates the difference set.

Select user_id from user intersect select user_id from toy;
// Returns the same part of the two select queries (here is the user_id of the child with toys)
Select user_id from user before t select user_id from toy;
// Returns the first select minus the second select result. (Here is the user_id of the child without toys)

Ii. Connection Query

You can use the join operator to query multiple tables. Connection is the main feature of the relational database model and a symbol that distinguishes it from other types of database management systems.

The UNION statement in standard SQL is as follows:

　　　　　　SELECT column_name(s) FROM table_name1　　　　　　UNION　　　　　　SELECT column_name(s) FROM table_name2

The premise is that the content (table items) of each SELECT statement must be in the same structure. In details, the number of columns in the linked table must be the same and the column attributes must be the same. The column names can be different (with the same structure)

First, cross join

Select u. username, t. toyname from user as u cross join toy as t;
// Cross join is to match the values of all the first and second tables one by one.
// For example, if the first table has five values and the second table has four values, this result should have 20 records.

Inner join: equal join: (equal join)

Select u. username, t. toyname from user as u inner join toy as t on (you can also use where) u. user_id = t. user_id;
// The result is their own toys.

Unequal join: (non-equal join)

Select u. username, t. toyname from user as u inner join toy as t on (you can also use where) u. user_id <> t. user_id order by u. username;
// The result is that the toys are not found.

Natural join: (natural join)

This is only available when the column names in the two joined tables are equal.

1	<code class=" hljs sql">select u.username, t.toyname from user as u natural join toy as T; // the result of the equal connection above is the same. </Code>

Outer join: outer join

Left outer join and right outer join are similar: left outer join; right outer join;

12	<code class=" hljs sql">select u.usrename, t.toyname from user u inner join toy t on (Not available herewhere) u.user_id = t.user_id;// Query the user name and the name of the toy he owns. </code>

Select u. username, t. toyname from user u left outer join toy t on u. user_id = t. user_id;
// This is based on the left table (user) to query the names of all users and their toys.
Select u. username, t. toyname from user u right outer join toy t on u. user_id = t. user_id;
// This is based on the right table (toy) to query the names of owners of all toys. If no owner is NULL