Use of SQL Join

I. Basic CONCEPTS

About the JOIN keyword in SQL statements, which is a more common and less understandable keyword, the following example gives a simple explanation – build table User1,user2:
Table1:create table user2 (id int, user_name varchar, over varchar (10));
Insert into User1 values (1, ' Tangseng ', ' DTGDF ');
Insert into User1 values (2, ' Sunwukong ', ' dzsf ');
Insert into User1 values (1, ' Zhubajie ', ' Jtsz ');
Insert into User1 values (1, ' Shaseng ', ' JSLH ');
Table2:create table user2 (id int, user_name varchar, over varchar (10));
INSERT into user2 values (1, ' Sunwukong ', ' chengfo ');
INSERT into User2 values (2, ' Niumowang ', ' Chengyao ');
INSERT into User2 values (3, ' Jiaomowang ', ' Chengyao ');
INSERT into User2 values (4, ' Pengmowang ', ' Chengyao ');
Types of joins in SQL standard

1. Inner connection (inner join or join)

(1). Concept: An inner join is a combination of two table columns based on a join predicate, resulting in a new result table
(2). Inner Connection Venn diagram:
(3). SQL statements
Select a.ID, A.user_name, b.over from User1 a inner joins User2 B on A.user_name=b.user_name;
Results:

2. External connection

Outer joins include left outer joins, right outer joins, or full outer joins

A. Left OUTER join: either outer join

(1) Concept: The result set of the left outer join includes all rows of the left table specified in the OUTER clause, not just the rows that match the joined columns. If a row in the left table does not have a matching row in the right table, all select list columns in the right table in the associated result set row are null (NULL).
(2) Left outer connection Venn diagram:
(3) SQL statement:
Select a.ID, A.user_name, b.over from User1 a left joins User2 B on A.user
_name=b.user_name;
Results:

B. Right outer join

(1) A right outer join is a reverse join of a left outer join. All rows of the right table will be returned. If a row in the right table does not have a matching row in the left table, a null value will be returned for left table.
(2) Right outer connection Venn diagram:
(3) SQL statements
Select B.user_name, B.over, a.over from User1 a right joins User2 B on A.user_name=b.user_name;
Results:

c. All-out connection: Full join or outer join

(1) A full outer join returns all rows from the left and right tables. When a row does not have a matching row in another table, the selection list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.
(2) Right outer connection Venn diagram:
(3) SQL statements
Select a.ID, A.user_name, b.over from User1 a full join User2 B on a.user_name=b.user_name
In MySQL query full connection will report 1064 error, MySQL does not support full connection query, instead of the statement:
Select A.user_name,a.over,b.over from User1 a left join User2 b in a.user_name = B.user_name UNION ALL Select B.user_name, B.over, a.over from User1 a right joins User2 b on a.user_name = B.user_name;
Results:

3. Cartesian connection (cross connection)

1. Concept: A cross join without a WHERE clause will produce a Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table equals the size of the Cartesian product result set. (User1 and User2 cross-joins generate 4*4=16 Records)
2. Cross-Joins: crosses join (without conditional on)
3.sql statement:
Select A.user_name,b.user_name, A.over, b.over from User1 a cross join User2 B;

Two. Tips for use 1. Update table with Join

We use the following statement to update the over field in the User1 table with the records in the User1 table and User2 table to ' QTDA '.
Update user1 set over= ' Qtds ' where User1.user_name in (select B.user_name from User1 a inner joins user2 b on a.user_name = B.user_name);
This statement in SQL Server, Oracle can be executed correctly, in MySQL but error, MySQL does not support the updating of the subquery table, then we use the following statement can be done.
Update User1 a join (select B.user_name from User1 a joins User2 b on a.user_name = B.user_name) b on a.user_name = B.user_ Name Set a.over = ' Qtds '

2. Using join to refine subqueries

Sub-query efficiency is inefficient, use the following statement to query
Select A.user_name, A.over, (select over from User2 b where a.user_name=b.user_name) as Over2 from User1 A;
Use join to refine subqueries to achieve the same effect
Select A.user_name, A.over, b.over as Over2 from User1 a left join User2
B on a.user_name = B.user_name;

3. Using join to refine the aggregate subquery

Introduce a new table: User_kills
CREATE TABLE User_kills (user_id int, timestr varchar), kills Int (10));
INSERT into user_kills values (2, ' 2015-5-12 ', 20);
INSERT into user_kills values (2, ' 2015-5-15 ', 18);
INSERT into User_kills values (3, ' 2015-5-11 ', 16);
INSERT into User_kills values (3, ' 2015-5-14 ', 13);
INSERT into User_kills values (3, ' 2015-5-16 ', 17);
INSERT into User_kills values (4, ' 2015-5-12 ', 16);
INSERT into User_kills values (4, ' 2015-5-10 ', 13);
Query user1 for each person in the User_kills table kills the largest date, using the aggregate subquery statement:
Select A.user_name,b.timestr, B.kills from User1 a joins User_kills B on a
. id = b.user_id WHERE b.kills = (select MAX (c.kills) from user_kills c where c.user_id = b.user_id);
Use join optimization to aggregate subqueries (avoid subqueries)
Select A.user_name, B.timestr, B.kills from User1 a joins User_kills B on
a.ID = b.user_id Join User_kills c on c.user_id = b.user_id GROUP by a.user_name, B.timestr, b.kills have b.kills = max ( C.kills);
Results:

4. Implementing grouping Selection Data

Ask for the first two days to query out the User1 each person kills to more.
First, we can use the following statement to find out the maximum number of people kills two days;
Select A.user_name, B.timestr, B.kills from User1 a joins User_kills B on
a.ID = b.user_id where a.user_name = ' sunwukong ' ORDER by b.kills desc limit 2;
So how do you find the two most kills in a single statement? Look at the following statement:
With TMP as (select A.user_name, B.timestr, B.kills, Row_number () through (partition by A.user_name ORDER by B.kills) CNT from User1 a join User_kills b on a.id = b.user_id) SELECT * FROM TMP where CNT <= 2;
The above statements are supported in both SQL Server and Oracle, but MySQL does not support the grouping sort function row_number (), which provides an alternative:
Select D.user_name,c.timestr, kills from (select user_id, Timestr, Kills, (select COUNT (*) from User_kills b where b.user_ id = a.user_id and a.kills <= b.kills) as CNT from User_kills a group by user_id, Timestr, kills) C joins User1 D on c.u ser_id = d.id where CNT <= 2;
Results:

Use of SQL Join