Sql как найти записи которых нет

There are basically 4 techniques for this task, all of them standard SQL.

NOT EXISTS

Often fastest in Postgres.

SELECT ip 
FROM   login_log l 
WHERE  NOT EXISTS (
   SELECT  -- SELECT list mostly irrelevant; can just be empty in Postgres
   FROM   ip_location
   WHERE  ip = l.ip
   );

Also consider:

• What is easier to read in EXISTS subqueries?

LEFT JOIN / IS NULL

Sometimes this is fastest. Often shortest. Often results in the same query plan as NOT EXISTS.

SELECT l.ip 
FROM   login_log l 
LEFT   JOIN ip_location i USING (ip)  -- short for: ON i.ip = l.ip
WHERE  i.ip IS NULL;

EXCEPT

Short. Not as easily integrated in more complex queries.

SELECT ip 
FROM   login_log

EXCEPT ALL  -- "ALL" keeps duplicates and makes it faster
SELECT ip
FROM   ip_location;

Note that (per documentation):

duplicates are eliminated unless EXCEPT ALL is used.

Typically, you’ll want the ALL keyword. If you don’t care, still use it because it makes the query faster.

NOT IN

Only good without null values or if you know to handle null properly. I would not use it for this purpose. Also, performance can deteriorate with bigger tables.

SELECT ip 
FROM   login_log
WHERE  ip NOT IN (
   SELECT DISTINCT ip  -- DISTINCT is optional
   FROM   ip_location
   );

NOT IN carries a «trap» for null values on either side:

• Find records where join doesn’t exist

Similar question on dba.SE targeted at MySQL:

• Select rows where value of second column is not present in first column

Ещё можно так:

SET @var = '{2}{3}{5}';
SELECT 
SUBSTRING
(
    @var := REPLACE
    (
        (	
            SELECT @var := REPLACE(@var, concat('{', `item_id`, '}'), '') AS `ids`
            FROM `table` 
            ORDER BY `ids` DESC 
            LIMIT 0,1
        ),
        '}{', 
        ','
    ), 
    2, 
    length(@var) - 2
)

Строку ‘{2}{3}{5}’, скриптом формируете.
Для одной записи запрос вернёт ‘5’, для нескольких, например ‘5,6,7’.
В MySQL у меня работает.

Ключевой момент вопроса должен установить для столбца "ABC","BBB","TTT" исходные данные.

эта таблица будет выглядеть так:

|---+
|val|
|---+
|ABC|
|BBB|
|TTT|

Sqlite не поддерживал функцию sqlite. так что будет немного сложно sqlite ваш список в таблицу.

Вы можете использовать CTE Recursive для создания подобной функции sqlite

1. Вам нужно использовать функцию replace чтобы удалить " двойные кавычки из ваших исходных данных».
2. В CTE есть две колонки
   • val столбце val переносятся данные вашего списка
   • колонке rest чтобы запомнить текущую строку splite

Вы получите таблицу из CTE как это.

|---+
|val|
|---+
|ABC|
|BBB|
|TTT|

Затем вы можете сравнить данные с table1.

Не в

WITH RECURSIVE split(val, rest) AS (
    SELECT '',  replace('"ABC","BBB","TTT"','"','') || ',' 
    UNION ALL
    SELECT  
         substr(rest, 0, instr(rest, ',')),
         substr(rest, instr(rest, ',')+1)
    FROM split
    WHERE rest <> '')
SELECT * from (
  SELECT val
  FROM split 
  WHERE val <> ''
) t where t.val not IN (
  select t1.code 
  from table1 t1
)

sqlfiddle: https://sqliteonline.com/#fiddle-5adeba5dfcc2fks5jgd7ernq

Результат Outut:

+---+
|val|
+---+
|BBB|
|TTT|

Если вы хотите показать его в строке, используйте функцию GROUP_CONCAT.

WITH RECURSIVE split(val, rest) AS (
    SELECT '',  replace('"ABC","BBB","TTT"','"','') || ',' 
    UNION ALL
    SELECT  
         substr(rest, 0, instr(rest, ',')),
         substr(rest, instr(rest, ',')+1)
    FROM split
    WHERE rest <> '')
SELECT GROUP_CONCAT(val,',') val from (
  SELECT val
  FROM split 
  WHERE val <> ''
) t where t.val not IN (
  select t1.code 
  from table1 t1
)

Результат Outut:

BBB, TTT

sqlfiddle: https://sqliteonline.com/#fiddle-5adecb92fcc36ks5jgda15yq

Примечание. Это неразумно для SELECT * from [my list] where table1.code not in [my list], потому что этому запросу негде найти table1 чтобы вы не смогли получить столбец table1.code

Вы можете использовать not exists или JOIN чтобы оправдать ожидания.

sqlfiddle: https://sqliteonline.com/#fiddle-5adeba5dfcc2fks5jgd7ernq

Пересказ статьи Brent Ozar. How to Find Missing Rows in a Table

Когда кто-то говорит: «Найдите все строки, которые был удалены», — это много проще сделать, когда таблица имеет столбец identity (id). Давайте возьмем таблицу Users в базе данных Stack Overflow:

Имеются id: -1, 1, 2, 3, 4, 5…, но нет 6 или 7. (Или 0). Если кто-нибудь попросит вас найти все id, которые были удалены или пропущены, как это сделать?

Использование GENERATE_SERIES в SQL Server 2022 и новее

Новая функция GENERATE_SERIES делает то, о чем говорит ее название: генерирует последовательность чисел. Мы можем выполнить соединение таблицы Users с этой последовательностью, чтобы найти все значения последовательности, для которых нет совпадающих строк в таблице Users:

DECLARE @FirstId INT, @LastId INT;

SELECT @FirstId = MIN(Id),

	@LastId = MAX(Id)

	FROM dbo.Users;

SELECT gs.value

FROM GENERATE_SERIES(@FirstId, @LastId, 1) gs

LEFT OUTER JOIN dbo.Users u ON gs.value = u.Id

WHERE u.Id IS NULL;

Поначалу LEFT OUTER JOIN может вам показаться несколько неестественным, но работает отлично:

Что такое, спросите вы? Почему GENERATE_SERIES подчеркнуто красным? Ну, SQL Server Management Studio не обновилась синтаксисом T-SQL, который был в последнем релизе.

К счастью, Microsoft разделила установки приложений для SSMS и самого ядра SQL Server именно по этой причине — долгое время выпуска SSMS не позволяло команде разработчиков ядра ускорить выпуск, поэтому они поместили менее часто обновляемую SSMS в свой собственный установщик.

(Я правильно понял? Простите меня, если это не так.)

Использование таблицы чисел в более старых версиях

Если вы еще не работаете на SQL Server 2022, то можете создать собственную таблицу чисел, используя следующие примеры. Только убедитесь, что ваша таблица чисел содержит по крайней мере столько строк, сколько номеров id вы ищете. Вот пример с таблицей на 100000000 строк:

DROP TABLE IF EXISTS dbo.Numbers;

CREATE TABLE dbo.Numbers (Number INT PRIMARY KEY CLUSTERED);  

INSERT INTO dbo.Numbers(Number)

SELECT TOP 10000000 row_number() over(order by t1.number) as N

FROM master..spt_values t1 

    CROSS JOIN master..spt_values t2

    CROSS JOIN master..spt_values t3

GO

Затем мы будем использовать это в том же ключе, что и GENERATE_SERIES:

DECLARE @FirstId INT, @LastId INT;

SELECT @FirstId = MIN(Id),

	@LastId = MAX(Id)

	FROM dbo.Users;

SELECT n.Number

FROM dbo.Numbers n

LEFT OUTER JOIN dbo.Users u ON n.Number = u.Id

WHERE u.Id IS NULL

  AND n.Number > @FirstId

  AND n.Number < @LastId

ORDER BY n.Number;

Это дает похожие результаты, но не идентичные:

В чем отличие? Да, этот метод не включает 0! Когда я наполнял мою таблицу чисел, то строил список только положительных целых. Единственной наиболее часто встречающейся мне ошибкой при использовании таблиц чисел является то, что они не охватывают всех необходимых нам чисел. Убедитесь, что они содержат как самое меньшее, так и самое большее из требуемых вам значений — проблема, которой мы не имеем с GENERATE_SERIES, поскольку там мы просто указываем начальное и конечное значения, а SQL Server делает все остальное.

Я не хочу погружаться глубже в другие способы решения этой проблемы, Itzik Ben-Gan введет вас в курс дела. Что до меня, мне нравится решать эту проблему быстро и легко с помощью GENERATE_SERIES. Кроме того, я ленив.