SELECT
FIRST.NAME,
SECOND.FIRST_NAME,
SECOND.LAST_NAME
FROM FIRST_TABLE FIRST
LEFT JOIN SECOND_TABLE SECOND
ON (SECOND.FIRST_NAME = FIRST.NAME OR SECOND.LAST_NAME = FIRST.NAME)
你可以试试这个,看看你能得到什么。这在很大程度上取决于 FIRST_NAME 以及 LAST_NAME ,但它避免了 OR .
SELECT
FIRST.NAME,
SECOND.FIRST_NAME,
SECOND.LAST_NAME
FROM FIRST_TABLE FIRST
LEFT JOIN (
SELECT S1.FIRST_NAME as TEST_NAME,
S1.FIRST_NAME,
S1.LAST_NAME
FROM SECOND_TABLE S1
WHERE S1.FIRST_NAME <> S1.LAST_NAME
UNION ALL
SELECT S2.LAST_NAME as TEST_NAME,
S2.FIRST_NAME,
S2.LAST_NAME
FROM SECOND_TABLE S2
) AS SECOND
ON FIRST.NAME = SECOND.TEST_NAME
SELECT FIRST.ID, FIRST.NAME
FROM FIRST_TABLE FIRST
WHERE EXISTS (SELECT 1 FROM SECOND_TABLE SECOND WHERE SECOND.FIRST_NAME = FIRST.NAME) OR
EXISTS (SELECT 1 FROM SECOND_TABLE SECOND WHERE SECOND.LAST_NAME = FIRST.NAME);
2条答案
按热度按时间zpgglvta1#
你可以试试这个,看看你能得到什么。这在很大程度上取决于
FIRST_NAME
以及LAST_NAME
,但它避免了OR
.b1uwtaje2#
这回答了问题的原始版本。
你和我一起生活会更好
exists
:然后,为了提高性能,您需要启用索引
SECOND_TABLE(LAST_NAME)
以及SECOND_TABLE(FIRST_NAME)
.