Наличие условий в операторе WHERE в SQL-запросах

ProgramBox

Наличие условий в операторе WHERE в SQL-запросах

Post author:admin
Запись опубликована:10 января, 2022
Post category:Вопросы по программированию

#mysql #sql #database

Вопрос:

У меня есть два запроса, возвращающих один и тот же набор столбцов.

 SELECT A.FIRST_NAME, A.MIDDLE_NAME, A.Age, B.Address
FROM table1 A INNER JOIN table2 B on A.ID = B.ID 
WHERE A.FIRST_NAME IN ('123')

SELECT A.FIRST_NAME, A.MIDDLE_NAME, A.Age, B.Address
FROM table1 A INNER JOIN table2 B on A.ID = B.ID
WHERE B.Address IN ('123')

Если пользователь вводит first_name, то должен быть выбран результат первого запроса, если пользователь вводит Адрес, то должен быть выбран результат второго запроса.

Я попытался использовать (A. FIRST_NAME = ‘123’ или B. Address=»), когда пользователь называет имя, и (A. FIRST_NAME = «или B. Address = «123»), когда пользователь называет второе имя, но это заставляет запрос выполняться вечно.

 SELECT A.FIRST_NAME, A.MIDDLE_NAME, A.Age, B.Address
FROM table1 A INNER JOIN table2 B on A.ID = B.ID
WHERE A.FIRST_NAME IN ('') OR B.Address IN ('123')

Я хотел бы получить предложения от моих коллег-экспертов.

1. Я вижу здесь некоторые потенциальные проблемы с дизайном. Почему имя и отчество хранятся в совершенно разных таблицах?

2. Привет @TimBiegeleisen, Это просто пример, который я создал. Сценарий реального мира отличается, но концепция та же.

3. Привет @TimBiegeleisen, Изменил запрос со значимыми именами. Спасибо.

4. Привет @JSVJ, когда ты говоришь «беги вечно», ты хочешь сказать, что это очень медленно? Вы создали какие-либо индексы для этих таблиц?

Ответ №1:

Типичным подходом было бы следующее:

 SELECT a.first_name, a.middle_name, a.age, b.address
FROM table1 a
INNER JOIN table2 b on a.id = b.id 
WHERE (a.first_name = @first_name OR @first_name IS NULL)
  AND (a.address = @address OR @address IS NULL);

Это работает там, где

указывается только первое имя
указан только адрес
оба задаются (в этом случае возвращаются только строки, соответствующие обоим условиям).
ни одна из них не задана (в этом случае возвращаются все строки).

СУБД должна будет найти план выполнения, который отлично работает при любых условиях. Это может быть сканирование полных таблиц, которое не обязательно медленное, но и не будет молниеносным.

Это отличается от UNION запроса:

 SELECT a.first_name, a.middle_name, a.age, b.address
FROM table1 a
INNER JOIN table2 b on a.id = b.id 
WHERE a.first_name = @first_name
UNION
SELECT a.first_name, a.middle_name, a.age, b.address
FROM table1 a
INNER JOIN table2 b on a.id = b.id 
WHERE a.address = @address;

Это работает там, где

указывается только первое имя
указан только адрес
оба даны (в этом случае возвращаются все строки, соответствующие хотя бы одному условию)
ни один из них не указан (в этом случае строка не возвращается)

Для этого запроса СУБД может использовать один индекс first_name и другой address (при условии, что вы создали эти индексы), и запрос будет очень быстрым.

В конце концов, этот UNION запрос-всего лишь еще один способ простого использования WHERE предложения WHERE a.first_name = @first_name OR a.address = @address . Но разделяя запрос, вы даете оптимизатору СУБД подсказку, что он может рассматривать это как два отдельных запроса, для которых он может использовать разные индексы. При идеальном оптимизаторе эта подсказка не понадобилась бы, и СУБД увидела бы это сама. Я почти уверен, что текущий оптимизатор MySQL нуждается в этой подсказке.)

Ответ №2:

Из запроса с вашего вопроса, опубликованного ниже:

SELECT A.FIRST_NAME, A.MIDDLE_NAME, A.Age, B.Address FROM table1 A INNER JOIN table2 B on A.ID = B.ID WHERE A.FIRST_NAME IN ('') OR B.Address IN ('123')

Это приравнивается к WHERE A.FIRST_NAME IS NOT NULL OR B.Address IN ('123') . Важно учитывать порядок в предложениях where и последствия этого порядка (индексы по столбцам являются основным фактором и порядком столбцов в индексе)[ПРАВИТЬ].

Запрос в кавычках фактически вернет все записи, в которых имена не являются нулевыми, и запись, в которой адрес = ‘123’.

Я бы предложил использовать оператор if для выполнения правильного запроса на основе пользовательского ввода. Что-то вроде этого:

 SET @isAddress := 1; -- variable that is set to 'Y' for yes, if the user inputs an address. If it's not 1 or greater, then the user has inputted a first name.
SET @userInput := '123'; -- variable that contains the user's input (either address or firstname). 
IF @isAddress > 0 THEN
    SELECT A.FIRST_NAME, A.MIDDLE_NAME, A.Age, B.Address
    FROM table1 A INNER JOIN table2 B on A.ID = B.ID
    WHERE B.Address = @userInput;
ELSE
    SELECT A.FIRST_NAME, A.MIDDLE_NAME, A.Age, B.Address
    FROM table1 A INNER JOIN table2 B on A.ID = B.ID 
    WHERE A.FIRST_NAME = @userInput;
END IF;

[ПРАВИТЬ]
Подумав, вы могли бы использовать упрощенный запрос, подобный этому:

 SET @isAddress := 1; -- variable that is set to 'Y' for yes, if the user inputs an address. If it's not 1 or greater, then the user has inputted a first name.
SET @userInput := '123'; -- variable that contains the user's input (either address or firstname). 

SELECT A.FIRST_NAME, A.MIDDLE_NAME, A.Age, B.Address
FROM table1 A INNER JOIN table2 B on A.ID = B.ID
WHERE (@isAddress > 0 AND B.Address = @userInput)
   OR (@isAddress = 0 AND A.FIRSTNAME = @userInput);

1. Я не понимаю «порядок в каких пунктах». Как вы думаете, имеет ли значение, говорить WHERE a=b OR c=d или WHERE c=d OR a=b нет ? Этого нет (или, по крайней мере, не должно быть). И WHERE A.FIRST_NAME IN ('') OR B.Address IN ('123') равно WHERE B.Address IN ('123') , потому что мы можем предположить, что нет записи с именем first_name = «. Это не равно WHERE A.FIRST_NAME IS NOT NULL OR B.Address IN ('123') , какими, вероятно, были бы все строки (за исключением случая, когда существуют записи без имени).

2. Да, это будет иметь значение, закажете ли вы запрос WHERE a=b OR c=d или WHERE c=d OR a=b . Я не знаю контекста здесь, поэтому не могу сказать, какой вариант более оптимален, но, например,:

3. … но, например: Если c=d это тривиальное утверждение, например, когда, скажем, переменной не существует null , то лучше использовать WHERE c=d OR a=b , потому что сначала оценивается тривиальное утверждение. Между ними может быть разница в производительности. Наконец, я знаю, что оптимизатор запросов, скорее всего, сначала оценит тривиальное условие; но в более сложных случаях это может быть не так.

4. СУБД будет рассматривать условия WHERE в ON любом порядке, который она сочтет целесообразным и выгодным. Было бы странно, если бы он подчинялся порядку, в котором перечислены условия. И это не так. См.: dbfiddle.uk/…

5. Верно, но это зависит от сложности запроса. Оптимизаторы СУБД не всегда понимают это правильно, поэтому всегда лучше написать запрос наиболее оптимальным способом. Я добавил к ответу заметку об индексах. Это то, о чем я не упоминал выше.

Ответ №3:

Я бы предложил в надежде на ленивую оценку WHERE пункта:

 SELECT A.FIRST_NAME, A.MIDDLE_NAME, A.Age, B.Address
FROM table1 A INNER JOIN table2 B on A.ID = B.ID
WHERE A.FIRST_NAME ='somename' OR
B.Address = '123'

Или альтернативно:

 SELECT A.FIRST_NAME, A.MIDDLE_NAME, A.Age, B.Address
FROM table1 A INNER JOIN table2 B on A.ID = B.ID
WHERE A.FIRST_NAME ='somenam%' AND
B.Address LIKE '12%'

Второй вариант подходит для четырех случаев:

Когда оба предложения будут = '' выполнены, он вернет нефильтрованный набор ( LIKE '%' );
Когда A.FIRST_NAME = '' и B.Address != '' когда он вернет набор, отфильтрованный B.Address LIKE '12%' ;
Когда A.FIRST_NAME != '' и B.Address = '' когда он вернет набор, отфильтрованный A.FIRST_NAME LIKE 'somenam%' ;
Когда оба предложения будут != '' выполнены, он вернет набор, отфильтрованный по обоим полям ( LIKE 'somevalue%' ).

Метки: Наличие условий в операторе WHERE в SQL-запросах