mysql查询union_mysql查询两个表，UNION和where子句

迈不过友情╰ 2023-01-11 05:00 89阅读 0赞

![bd96500e110b49cbb3cd949968f18be7.png][]

I have two tables.

I query like this:

SELECT \* FROM (

Select requester\_name,receiver\_name from poem\_authors\_follow\_requests as one

UNION

Select requester\_name,receiver\_name from poem\_authors\_friend\_requests as two

) as u

where (LOWER(requester\_name)=LOWER('user1') or LOWER(receiver\_name)=LOWER('user1'))

I am using UNION because i want to get distinct values for each user if a user exists in the first table and in the second.

For example:

table1

nameofuser

peter

table2

nameofuser

peter

if peter is on either table i should get the name one time because it exists on both tables.

Still i get one row from first table and a second from table number two. What is wrong?

Any help appreciated.

解决方案

There are two problems with your SQL:

(THis is not the question, but should be considered) by using WHERE over the UNION instead of the tables, you create a performance nightmare: MySQL will create a temporary table containing the UNION, then query it over the WHERE. Using a calculation on a field (LOWER(requester\_name)) makes this even worse.

The reason you get two rows is, that UNION DISTINCT will only suppress real duplicates, so the tuple (someuser,peter) and the tuple (someotheruser, peter) will result in duplication.

Edit

To make (someuser, peter) a duplicate of (peter, someuser) you could use:

SELECT

IF(requester\_name='peter', receiver\_name, requester\_name) AS otheruser

FROM

...

UNION

SELECT

IF(requester\_name='peter', receiver\_name, requester\_name) AS otheruser

FROM

...

So you only select someuser which you already know : peter