如何通过在mysql中加入来替换union

select service_type from service_type as s union select service_type
from cust_service_type as cs

I want to replace UNION by JOINS. Please let me know the changes that will need to be made.

我想用JOINS替换UNION。请告诉我需要进行的更改。

1 个解决方案

#1

MySQL does not support FULL JOIN so it's impossible to do this in MySQL.

MySQL不支持FULL JOIN,所以在MySQL中不可能这样做。

In other systems, you could use this syntax:

在其他系统中,您可以使用以下语法:

SELECT  DISTINCT COALESCE(service_type, cust_service_type)
FROM    service_type s
FULL JOIN
        cust_service_type cs
ON      cs.service_type = s.service_type

SELECT  DISTINCT service_type
FROM    service_type s
FULL JOIN
        cust_service_type cs
USING   (service_type)

for systems that support JOIN ... USING

对于支持JOIN ... USING的系统

Note that UNION is much more efficient than this solution.

请注意,UNION比此解决方案更有效。

Update:

If you want all fields from both tables, use this:

如果您想要两个表中的所有字段,请使用以下命令:

SELECT  *
FROM    service_type s
UNION ALL
SELECT  *
FROM    cust_service_type cs
WHERE   service_type NOT IN
        (
        SELECT  service_type
        FROM    service_type
        )

#1