两个表比较和列表php

Question

有两个不同的表。我想找到带有"%USD"的表达式。在这两个表中相同名称的列不匹配并在屏幕上列出它们。

<?php
include("db.php");
$queb = $conn->query("select distinct symbol from tableA WHERE symbol LIKE '%USD'"); 
while ($resb = $queb->fetch_assoc()){
$symbolb = $resb['symbol'];
}
?>

查询

<?php
include("db.php");
$quec = $conn->query("select distinct symbol from tableB WHERE symbol LIKE '%USD'"); 
while ($resc = $quec->fetch_assoc()){
$symbolc = $resc['symbol'];
}
?>

如何列出不在两个表之间的表达式?

Answer 1

我可能没有完全理解你的问题。如果您想列出在tableA中以'USD'开头但不在tableB中的符号，以及在tableB中以USD开头但不在tableA中的符号，那么以下查询应该返回这些符号:

select distinct symbol from tableA
where symbol LIKE '%USD' and symbol not in (select distinct symbol from tableB)
union
select distinct symbol from tableB
where symbol LIKE '%USD' and symbol not in (select distinct symbol from tableA)

也可以使用外连接:

select distinct symbol from tableA left join tableB on tableA.symbol = tableB.symbol
where tableA.symbol like ('%USD') and tableB.symbol is null
union
select distinct symbol from tableB left join tableA on tableB.symbol = tableA.symbol
where tableB.symbol like ('%USD') and tableA.symbol is null

无论哪种情况，如果在symbol列上有索引，性能都会得到提高。