Shulou(Shulou.com)05/31 Report--

This article mainly introduces the use of Join in SQL examples, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

one。 Basic concept

With regard to the join keyword in the sql statement, which is more commonly used but not easy to understand, the following example gives a simple explanation-creating a table user1,user2:

Table1: create table user2 (id int, user_name varchar (10), 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 (10), 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 Join in the SQL standard

1. Internal connection (inner join or join)

(1)。 Concept: an inner join combines the columns of two tables based on join predicates to produce a new result table

(2)。 Inner connection Venn diagram:

(3) .SQL statement

Select a.id, a.user_name, b.over from user1 an inner join user2 b on a.user_name=b.user_name

Results:

two。 External connection

Outer join includes left outer join, right outer join, or full outer join

a. Left outer connection: left join or left outer join

(1) concept: the result set of the left outer join includes all the rows of the left table specified in the LEFT OUTER clause, not just the rows matched by the join column. If a row of the left table does not match in the right table, all select list columns of the right table in the associated result set row are null.

(2) left outer connection Venn diagram:

(3) sql statement:

Select a.id, a.user_name, b.over from user1 a left join user2 b on a.user_name=b.user_name

Results:

b. Right outer connection: right join or right outer join

(1) the right outer join is the reverse join of the left outer join. All rows of the right table are returned. If a row of the right table does not match in the left table, a null value is returned for the left table.

(2) right outer connection Venn diagram:

(3) sql statement

Select b.user_name, b.over, a.over from user1 a right join user2 b on a.user_name=b.user_name

Results:

c. Full external connection: full join or full outer join

(1) the full outer join returns all rows in the left and right tables. When a row does not match a row in another table, the select list column of the other table contains null values. If there are matching rows between tables, the entire result set row contains the data values of the base table.

(2) right outer connection Venn diagram:

(3) sql statement

Select a.id, a.user_name, b.over from user1 a full join user2 b on a.user_name=b.user_name

Querying full join in mysql will report an error of 1064. Mysql does not support full join query, instead of statement:

Select a. A.over from user1 a right join user2 _ name _ name _

Results:

3. Cartesian connection (cross connection)

1. Concept: a cross join without a WHERE clause produces the Cartesian product of the table involved in the join. The number of rows of the first table multiplied by the number of rows of the second table is equal to the size of the result set of the Cartesian product. (user1 and user2 cross-connect produces 4 "4" 16 records)

two。 Cross-connect: cross join (no conditional on)

3.sql statement:

Select a.user_name,b.user_name, a.over, b.over from user1 a cross join user2 b

two。 Use skills

1. Update tables using join

We use the following statement to update the over field that exists in both the user1 table and the user2 table in the user1 table to 'qtda'.

Update user1 set over='qtds'where user1.user_name in (select b.user_name from user1 an inner join user2 b on a.user_name = b.user_name)

This statement can be executed correctly in both sql server and oracle, but an error is reported in mysql. Mysql does not support updating the tables of subqueries, so we can do so using the following statement.

Update user1 a join (select b.user_name from user1 a join user2 b on a.user_name = b.user_name) b on a.user_name = b.user_name set a.over = 'qtds'

two。 Optimize subqueries using join

The subquery is inefficient, so 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

Using join to optimize subqueries can 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. Optimize aggregation subqueries using join

Introduce a new table: user_kills

Create table user_kills (user_id int, timestr varchar (20), 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 the date of the maximum kills in the user_ kills table for each person in user1, and use the aggregation subquery statement:

Select a.user_name,b.timestr, b.kills from user1 a join 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 to optimize aggregate subqueries (avoid subqueries)

Select a.user_name, b.timestr, b.kills from user1 a join 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 having b.kills = max (c.kills)

Results:

4. Implement packet selection data

It is required to find out the first two days of kills pairs for each person in user1.

First of all, we can find out the two most kills days of a person by using the following statement

Select a.user_name, b.timestr, b.kills from user1 a join 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 out the two days with the most kills for everyone in one statement? Look at the following statement:

WITH tmp AS (select a.user_name, b.timestr, b.kills, ROW_NUMBER () over (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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.