Блог

Главная - Вопросы по программированию - Альтернатива использованию подзапросов в операторах SQL?

Альтернатива использованию подзапросов в операторах SQL?

#sql #query-optimization #subquery

#sql #оптимизация запросов #подзапрос

Вопрос:

У меня есть две таблицы:

 TableA: (a temporary table)
ItemId (int)

TableB:
ItemId (int), ParentID (int)

Я хочу получить все элементы в таблице A, где родительский идентификатор любого из элементов в таблице A не существует в качестве идентификатора ItemId. (т. Е. я хочу получить корень элементов в TableA)

Этот запрос выполняет то, что я хочу:

 SELECT a.ItemID
FROM TableA a
INNER JOIN TableB b ON a.ItemId = b.ItemID
WHERE b.ParentID NOT IN ( SELECT * from TableA )

как и этот:

 SELECT b.ItemID 
FROM TableB b
WHERE b.ItemID IN ( SELECT * FROM TableA)
AND b.ParentID NOT IN ( SELECT * FROM TableA )

Я не удовлетворен ни одним из запросов, особенно из-за использования NOT IN /В. Есть ли способ сделать это без них? Возможно, более чистый способ, который не требует подзапросов?

Примеры данных:

 Table A
-------
2
3
5
6

Table B
--------
1 | NULL
2 | 1
3 | 1
4 | 3
5 | 3
6 | 3

Желаемый результат:

 2
3

Спасибо

Комментарии:

1. Есть ли причина, по которой вы против этих подзапросов? В данном случае они должны выполняться быстрее, чем a LEFT JOIN .

2. Ну, мне определенно не нравится повторять с помощью subqueries…it просто кажется странным это делать. Сказав это, я просто предположил, что они были плохими, потому что … это то, что … я … читал … везде….

3. Возможно, вы можете объяснить, почему это будет быстрее в этом экземпляре, а не в других?

4. Для подзапроса использование IN или EXISTS вычисляет подзапрос один раз и приводит к короткому замыканию. Для LEFT JOIN вы возвращаете весь результирующий набор в JOIN отредактированной таблице, а затем отфильтровываете. IN может быть медленнее, если вы используете определенные значения или набор, но для подзапроса план выполнения обычно такой же, как для EXISTS подзапроса, который замыкается.

5. @Swati — это также короткое замыкание, но NOT IN может быть опасным из-за того, как оно обрабатывает NULL

Ответ №1:

Без подзапросов:

 SELECT ItemID
  FROM TableA
INTERSECT 
SELECT b.ItemID
  FROM TableB AS b
       LEFT OUTER JOIN TableA AS a
          ON b.ParentID = a.ItemID
 WHERE a.ItemID IS NULL;

…но обоснован ли ваш страх перед подзапросами? 🙂 Я бы счел этот эквивалентный запрос более легким для чтения и понимания:

 SELECT ItemID
  FROM TableA
INTERSECT 
SELECT ItemID
  FROM TableB
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM TableA AS a
                    WHERE a.ItemID = TableB.ParentID
                  );

Комментарии:

1. Спасибо 🙂 Я думаю, это иррациональный страх 🙂

2. Единственное, чего я боюсь in (subquery) , — это ограничения количества результатов, которые вы можете получить в in предложении. Фактически, недавно я столкнулся с этой ошибкой, в которой говорилось, что в моем in предложении sql server 2008 r2 Express может быть не более 2100 идентификаторов.

3. @&oku_da_master: звучит так, будто на самом деле вы используете не IN ( <table expression, could be a subquery&&t; ) , а скорее IN (<comma separated list&&t;) , или, возможно IN (<table constructor&&t;) , у каждого анализатора есть свои ограничения!

Ответ №2:

Взгляните на Выберите все строки из одной таблицы, которые не существуют в другой таблице, чтобы увидеть 5 различных способов выполнения такого рода запросов с помощью

НЕ В
НЕ СУЩЕСТВУЕТ
ЛЕВОГО и ПРАВОГО СОЕДИНЕНИЯ
OUTER APPLY (2005 )
ЗА ИСКЛЮЧЕНИЕМ (2005 )

Вот скрипт, который вы можете запустить

 CREATE TABLE #TableA( ItemId int)

INSERT #TableA values(1)
INSERT #TableA values(2)
INSERT #TableA values(3) 
INSERT #TableA values(4)
INSERT #TableA values(5)
INSERT #TableA values(6) 


CREATE TABLE #TableB( ItemId int, ParentID int)
INSERT #TableB values(1,1)
INSERT #TableB values(2,2)
INSERT #TableB values(4,3)
INSERT #TableB values(5,4)

это сделает это для родительского

 SELECT a.ItemID
FROM #TableA a
LEFT JOIN #TableB b ON a.ItemId = b.ParentID
WHERE b.ItemID IS NULL

SELECT a.ItemID
FROM #TableA a
WHERE NOT EXISTS (SELECT 1 FROM #TableB b WHERE a.ItemId = b.ParentID)

Вывод

 ItemID
5
6

Комментарии:

1. Это дает мне nullset. У ВСЕХ элементов есть родительские элементы, поэтому я не уверен, что это когда-нибудь будет null.

2. У меня ничего из этого не работает. Я не верю, что связанный вопрос применим — мне нужны записи, которые существуют в обеих таблицах, НО где родительский идентификатор (в TableB) не является идентификатором элемента в TableA

3. Для меня это недопустимый набор данных. ВСЕ идентификаторы элементов в таблице A также существуют в таблице B в качестве идентификаторов элементов. (Таблица A является подмножеством таблицы B, если хотите). TableB также хранит родительские идентификаторы, все из которых также являются допустимыми идентификаторами элементов.

4. Обновлено, я думаю, я неправильно понял

5. -1 Используя данные операционной системы, ваши запросы не дают желаемого результата операционной системы.

Ответ №3:

Вы можете использовать внешние соединения. Что-то вроде этого:

 SELECT a.ItemID
FROM TableA a
INNER JOIN TableB b ON a.ItemId = b.ItemID
LEFT JOIN TableB parentB on a.ItemID = parentB.ParentID 
WHERE parentB.ParentID IS NULL

Ответ №4:

Похоже, что ваши таблицы A и B хранят древовидную структуру. Я бы интерпретировал таблицу A как «Узлы» (хранящие элементы дерева), а таблицу B как «Ребра» (связывающие узел с его родительским элементом). Вариант внутреннего соединения очень элегантен, поскольку он охватывает все случаи «нет ребра к родительскому элементу», «ребро к PrantID null» и «ребро к несуществующему родительскому элементу» сразу. приветствия