Mysql union and connection Query

Mysql union and connection Query
1. The UNION, INTERSECT, and except t union operators can combine the query results of two or more Select statements into one result set for display, that is, the UNION query is executed. The syntax format of UNION is: select_statement UNION [ALL] selectstatement [UNION [ALL] selectstatement] [… N] Where 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 is the intersection. distinct 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 a child without toys) 2. You can use the join query 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. In standard SQL, the UNION statement is as follows: SELECT column_name (s) FROM table_name1UNIONSELECT column_name (s) FROM table_name2 where the premise is that the content of each SELECT statement (Table item) must be of 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: cross joinselect u. username, t. toyname from user as u cross join toy as t; // The cross join operation maps 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: 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 the toys they own. Unequal connection: (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 a natural link between the toys that they do not have: (natural join) This is used only when the column names in the two joined tables are equal. <Code class = "hljs SQL"> select u. username, t. toyname from user as u natural join toy as t; // the same result as the above equal join. </Code> outer join: The left outer join and the right outer join are similar: left outer join; right outer join; <code class = "hljs SQL"> select u. usrename, t. toyname from user u inner join toy t on (where cannot be used here) u. user_id = t. user_id; // This is the query 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 name of the owner of all toys. For NULL