检查另一个表中是否不存在某项 [英] Checking whether an item does not exist in another table

问题描述

我的表设置如下:

table name: process
fields: name, id_string

table name: value_seach
fields: id_string, value

我想构造一个select语句，该语句将显示在value_search中没有条目的所有进程名称(带有相应的id_string).

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.

进程表中的id_string可以为 null ，并且仍然具有名称，但是如果可能的话，必须将其排除. value_search中的id_string不能为 null

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

我该怎么做?

推荐答案

通常，如果要在另一个表中不存在行，则请LEFT JOIN另一个表，并且WHERE ... IS NULL到列中的列第二张桌子.您还提到过，您不希望process.id_string为NULL的行.

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.

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

这被称为反联接.

这篇关于检查另一个表中是否不存在某项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！