检查另一个表中是否存在项目

My tables are set up something like this:

我的表格设置如下：

table name: process
fields: name, id_string

table name: value_seach
fields: id_string, value

I want to construct a select statement that will display all of the process names (with it's respective id_string) that do not have an entry in value_search.

我想构造一个select语句，它将显示在value_search中没有条目的所有进程名称（使用它的各自的id_string）。

The id_string in the process table can be null, and still have a name, but those need to be excluded if possible. The id_string in value_search can never be null

进程表中的id_string可以为null，并且仍然具有名称，但如果可能，则需要排除这些名称。 value_search中的id_string永远不能为null

How do I do this?

我该怎么做呢？

4 个解决方案

#1

In general if you want rows that don't exist in another table, then LEFT JOIN the other table and WHERE ... IS NULL to a column on the second table. Also you mentioned that you don't want rows where process.id_string is NULL.

通常，如果您想要在另一个表中不存在的行，则LEFT JOIN另一个表和WHERE ... IS NULL到第二个表上的列。你还提到过你不希望process.id_string为NULL的行。

SELECT p.name, p.id_string
FROM
    process p
    LEFT JOIN value_search v
        ON v.id_string = p.id_string
WHERE
    v.id_string IS NULL
    AND p.id_string IS NOT NULL

This is known as an anti-join.

这被称为反连接。

#2

I believe using Not Exists would be your best option here.

我相信使用Not Exists将是您最好的选择。

SELECT p.name, p.id_string
FROM process p
WHERE 
   NOT p.id_string IS NULL AND
   NOT EXISTS(
          SELECT NULL
          FROM value_search v
          WHERE p.id_string = v.id_string)

#3

The query you want should look something like this. Note that a JOIN will be significantly faster than a subquery in the WHERE clause.

您想要的查询应该是这样的。请注意，JOIN将明显快于WHERE子句中的子查询。

SELECT p.name, p.id_string
FROM process p
LEFT OUTER JOIN value_search v
   ON p.id_string = v.id_string
   AND p.id_string IS NOT NULL
   AND v.id_string IS NULL

An equally valid variant of the query above would be:

上面查询的同样有效的变体是：

SELECT p.name, p.id_string
FROM process p
LEFT OUTER JOIN value_search v
   ON p.id_string = v.id_string
WHERE
   p.id_string IS NOT NULL
   AND v.id_string IS NULL

#4

SELECT 
  name,
  id_string
FROM process
WHERE id_string IS NOT NULL AND id_string NOT IN SELECT id_string FROM value_seach

#1