Examples of mysql left-connected self-connection

Mysql left-join self-join example joins two tables into a large table according to a public field. The left join is used to traverse the right table based on the table on the left. 1. Lead left join, self join SELECT * FROMtest_visitt1LEFTJOINtest_visitt2ONt1.uidt2.uid result: 2. Problem

Mysql left-join self-join example joins two tables into a large table according to a public field. The left join is used to traverse the right table based on the table on the left. 1. Lead left join, self join select * FROM test_visit t1 left join test_visit t2 ON t1.uid = t2.uid result: 2. Problem

Example of mysql left-connected self-connection

Join is to splice two tables into a large table according to a public field.

The left join is used to traverse the right table based on the table on the left.

1. Introduction

Left join, self join

SELECT * FROM test_visit t1 LEFT JOIN test_visit t2 ON t1.uid = t2.uid

Result:

2. Example:

User Access record:

Problem: I found out that I had watched Hunan TV but not Beijing TV.

Logic: first, I checked Hunan Satellite TV and Beijing satellite TV through the left connection, and then I checked out the users who saw Hunan Satellite TV but did not find the result just now.

SELECT * FROM test_visit WHERE channel = 'hunan satellite TV 'AND uid not in (select distinct t1.uid FROM test_visit t1 left join test_visit t2 ON t1.uid = t2.uidWHERE t1.channel = 'hunan satellite TV' AND t2.channel =' beijing TV ')

Result:

Do you think this is not the best method?