Shulou(Shulou.com)06/01 Report--

Today, I would like to share with you the relevant knowledge points about how SQL deletes duplicate e-mailboxes. The content is detailed and the logic is clear. I believe most people still know too much about this, so share this article for your reference. I hope you can get something after reading this article. Let's take a look at it.

Overview of SQL topic

Title:

Write a SQL query to delete all duplicate mailboxes in the Person table, leaving only the one with the smallest Id. LeetCode original title connection

Https://leetcode-cn.com/problems/delete-duplicate-emails/

Person table

+-+ +

| | Id | Email |

+-+ +

| | 1 | john@example.com |

| | 2 | bob@example.com |

| | 3 | john@example.com |

+-+ +

Id is the primary key of this table.

Problem-solving ideas

Connect this table to itself through a self-join, through an e-mail column.

Then we need to find a larger ID with the same e-mail address in other records. So we find out the large data through ID comparison and add this as a condition to the WHERE clause.

Because we already have the record to delete, then we use the delete statement to implement it.

Method realization

According to the above ideas, we can easily write a solution to the problem.

DELETE pson1FROM Person pson1,Person pson2WHERE pson1.Email = pson2.Email AND pson1.Id > pson2.Id code test

Conduct code testing

Consistent with the expected results, the test was successful, using 166ms

Summary of knowledge points

⭐ internal connection and external connection ⭐

Xiao Meng takes the little friends through the inner connection and the outer connection in a very simple way.

Table 1 classa

Table 2 classb

1. Internal connection inner join (join is internal connection by default)

The intersection of Table 1 and Table 2 is demonstrated by the above two tables

Select classa.id as aid,classb.id as bid from classa inner join classb on classa.id = classb.id

The result of the query is the intersection of classa and classb

two。 Left outside connection left join

The result set retains all rows of the left table, but the right table contains only rows that match the left table. The corresponding null value of the null behavior in the table on the right.

Select classa.id as aid,classb.id as bid from classa left join classb on classa.id = classb.id

3. Right external connection right join

The result set retains all rows of the right table, but the left table contains only rows that match the right table. The corresponding null value of the null behavior in the left table.

Select classa.id as aid,classb.id as bid from classa right join classb on classa.id = classb.id

4. Full external connection full join

All rows of the two tables are displayed in the result table.

Select classa.id as aid,classb.id as bid from classa full join classb on classa.id = classb.id

Attention, friends!

MySQL does not support full jointing! MySQL does not support full jointing! MySQL does not support full jointing!

Say important things three times! So how do you achieve the same effect as full join? This is achieved by using union, which implements the SQL statement as follows

Select classa.id as aid,classb.id as bid from classa left join classb on classa.id = classb.idunionselect classa.id as aid,classb.id as bid from classa right join classb on classa.id = classb.id

These are all the contents of the article "how to delete duplicate email addresses by SQL". Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

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.