mysql - 如何查看两列的组合是否在另一个表中？

Question

我有两个表:Transit 和 Connect。

Transit
TransitType    TransitRoute    TransitPrice
--------------------------------------------

Connect
SiteName       TransitType     TransitRoute
--------------------------------------------

我想检查 Transit 表中的 TransitType 和 TransitRoute 组合是否在 Connect 表中。

我当前的命令是这样的:

    TransitRoute IN 
    (SELECT connect.TransitRoute from connect WHERE connect.SiteName = sname)

我还尝试了以下命令，但收到错误，指出操作数应包含 1 列。

    SELECT * FROM transit WHERE ((transitRoute, transitType) IN (SELECT
    (connect.TransitRoute, connect.TransitType) from connect WHERE connect.SiteName = sname) 

Answer 1

使用JOIN

SELECT t.*
FROM Transit AS t
JOIN Connect AS c ON t.TransitType = c.TransitType AND t.TransitRoute = c.TransitRoute

由于您匹配的列具有相同的名称，您还可以使用 USING 缩写:

SELECT t.*
FROM Transit AS t
JOIN Connect USING (TransitType, TransitRoute)