SQL -检查雇员FirstName和/或LastName是否存在

Question

对于下面的表，检查表1中的列是否存在于表2中-

Table_1:

tbody> <<tr>

FULL_NAME
水晶,水晶
卡门,TEST2
XYZ, ABC
BLA, VVV

您可以使用exists与instr:

select t1.full_name, exists (select 1 from table_2 t2 where instr(t1.full_name, t2.name) > 0) status from table_1 t1;

输出:

水晶,水晶1卡门,TEST21XYZ, ABC0BLA, VVV1

--Suported on MSSQL, MSACCESS, Oracle, MySQL, PostgreeSQL
SELECT t1.FULL_NAME,
CASE
WHEN Temp.CONTAINING IS NULL THEN 'FALSE'
WHEN Temp.CONTAINING IS NOT NULL THEN 'TRUE'
END AS 'STATUS'
FROM Table_1 t1
LEFT JOIN 
(SELECT NAME, 
(SELECT FULL_NAME
FROM Table_1
WHERE FULL_NAME LIKE CONCAT('%',NAME,'%')) as 'CONTAINING'
FROM Table_2) Temp ON
t1.FULL_NAME = Temp.CONTAINING