сравнение кортежей в SQL Server с троичной логической логикой

У меня есть вопрос относительно троичной логической логики, которая влияет на то, как реализовать сравнение между полиморфными сущностями в нашем поставщике LINQ.

В SQL, если вы присоединитесь к таблице Region с помощью внешнего ключа Country :

SELECT * From Region r1, Region r2
WHERE r1.Country == r2.Country

(примечание: результаты идентичны независимо от того, используете ли вы синтаксис JOIN или WHERE)

Он вернет значения, в которых условие истинно, а не тогда, когда условие ложно или неизвестно (поскольку некоторые ключи равны нулю). Итак, если мы отрицаем условие:

SELECT * From Region r1, Region r2
WHERE r1.Country != r2.Country

Мы получаем значения, где условие истинно (теперь разные ключи), и мы пропустим те, которые имеют одинаковые ключи, или те, которые имеют какое-то нулевое значение, потому что условие снова возвращает неизвестное. Даже если мы напишем так:

SELECT * From Region r1, Region r2
WHERE not(r1.Country == r2.Country)

Неизвестное будет распространяться, поэтому для этого простого условия никогда не появятся нули. Все идет нормально.

Теперь давайте представим, что в Регионе может быть Страна (для небольших европейских стран) или Штат (для США, России, Китая...). Если у региона есть штат, у него будет значение Country null, и наоборот.

Как мы можем объединить пару [Страна, Штат], чтобы она имела те же свойства, что и раньше?:

SELECT * From Region r1, Region r2
WHERE r1.Country == r2.Country OR r1.State == r2.State

Это выражение вернет значение true, если оно соединяется, в противном случае — неизвестное. Мы хотели бы, чтобы он возвращал unknown только в том случае, если все поля равны нулю, иначе, если мы отрицаем:

SELECT * From Region r1, Region r2
WHERE not(r1.Country == r2.Country OR r1.State == r2.State)

Он не возвращает строк!. Если мы попробуем более запутанное выражение:

SELECT * From Region r1, Region r2
WHERE (r1.Country == r2.Country AND r1.Country IS NOT NULL AND r2.Country IS NOT NULL)  
   OR (r1.State   == r2.State   AND r1.State   IS NOT NULL AND r2.State   IS NOT NULL)

Затем он вернет true, если пара совпадает, false в противном случае и никогда ничего. Затем, если мы отрицаем, он вернет значения, в которых все строки равны нулю, что ведет себя иначе, чем в первом примере.

Итак, какое выражение для сравнения этой пары будет вести себя как равенство SQL?

  • Истина при совпадении
  • False, если не соответствует
  • Неизвестно, когда какой-либо операнд имеет значение null.

null sql-server tuples linq boolean-logic
person Olmo    schedule 22.04.2011    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Создайте вычисляемый столбец:

Location AS COALESCE(Country, State)

, проиндексируйте его и сравните, как обычно:

SELECT  *
FROM    Region r1
JOIN    Region r2
ON      r2.Location = r1.Location

Location будет NULL, если оба Country и State равны NULL.

Обновление:

Если Country и State можно сравнивать, создайте дополнительный столбец, показывающий, какой из них используется:

LocationType AS CASE WHEN Country IS NOT NULL THEN 1 WHEN State IS NOT NULL THEN 2 END,
LocationId AS COALESCE(Country, State)

, проиндексируйте их и используйте для сравнения:

SELECT  *
FROM    Region r1
JOIN    Region r2
ON      r2.LocationType = r1.LocationType
        AND r2.LocationID = r1.LocationID
person Quassnoi    schedule 22.04.2011
comment
Если я это сделаю, то я смешаю регионы в Австрии (страна с идентификатором = 1) с регионами в Аризоне (штат с идентификатором = 1). Как я мог сделать их другими? - person Olmo; 22.04.2011
comment
Это обязательно сработает, большое спасибо. Я не буду идти по вашему пути, так как это общее решение, и я мог бы сравнивать любой набор сущностей из любого набора сущностей, и я не хочу загромождать модель вычисляемыми полями и индексами для каждого возможного сравнения. Вы в любом случае заслуживаете голоса. - person Olmo; 22.04.2011
comment
@Olmo: вы можете заменить вычисляемые поля их определениями прямо в запросе: ON CASE WHEN r1.… END = CASE WHEN r2.… END AND COALESCE(r1.…) = COALESCE(r2.…), хотя это невозможно проверить. Обратите внимание, что с решением OR количество сравнений будет расти квадратично. - person Quassnoi; 22.04.2011
comment
На самом деле вы меня убеждаете. Я думаю, что число сравнений не растет квадратично, но возможность анализа является преимуществом. Также я думал, что рассчитанные значения зависят от каждого возможного сравнения, но на самом деле они стабильны, поскольку у меня есть глобальное сопоставление «таблица-идентификатор». Я пойду твоей дорогой. Большое спасибо. - person Olmo; 22.04.2011

arrow_upward
1
arrow_downward

Можете ли вы попробовать это:

SELECT *
FROM Region r1
  JOIN Region r2
    ON ( r1.Country = r2.Country 
         AND r1.State IS NULL 
         AND r2.State IS NULL
       )
    OR ( r1.State = r2.State 
         AND r1.Country IS NULL 
         AND r2.Country IS NULL
       )
person ypercubeᵀᴹ    schedule 22.04.2011
comment
Спасибо. Я хотел добавить, что структура ваших таблиц нуждается в некоторой нормализации, чтобы избежать NULL. Тогда ваши запросы не будут нуждаться в этом сложном условии, которое может работать медленно для больших таблиц. Что, конечно же, вы можете никогда не увидеть, сколько стран может быть в мире? Тем не менее, лучше учитывать это в начале, а не позже, когда вы можете добавить субрегионы, города и деревни в свою схему, а затем вдруг осознать, что этот запрос является узким местом. - person ypercubeᵀᴹ; 22.04.2011
comment
Я потратил время, чтобы обобщить ваше решение, когда вы присоединяетесь к кортежу [Страна, Штат] с помощью простого ключа [Страна]. Когда вы сравниваете трио [Country, State, SpaceColony] с другим трио или с кортежем и т. д., мы решили это, но я также рассматриваю другое решение, поскольку его проще понять и могут быть индексы, если необходимый. Каково ваше мнение? - person Olmo; 22.04.2011
comment
Я думаю, вам следует опубликовать это как отдельный вопрос со структурой ваших таблиц (поля, первичные и внешние ключи), поскольку сейчас мы можем только догадываться о структуре. Совет Quassnoi (объединение Country и State в одном поле и добавление LocationType кажется лучшим, но отображение других связанных таблиц поможет понять проблемы и предложить возможную реструктуризацию/нормализацию. - person ypercubeᵀᴹ; 22.04.2011
comment
Я создаю не приложение, а фреймворк, и мне нужно найти наилучшее общее решение. Например, можно было бы сравнить домашнее животное человека [Собака, Кошка, Птица] с животными на ферме [Собака, Лошадь, Свинья, Курица]. - person Olmo; 22.04.2011