Несколько столбцов SQL в разделе IN

Если нам нужно запросить таблицу на основе некоторого набора значений для данного столбца, мы можем просто использовать предложение IN.

Но если запрос должен выполняться на основе нескольких столбцов, мы не могли бы использовать предложение IN (grepped в потоках SO.)

Из других потоков SO мы можем обойти эту проблему, используя предложения join или exist и т.д. Но все они работают, если в базу данных находятся как основная таблица, так и данные поиска.

E.g
User table:
firstName, lastName, City

Учитывая список (firstname, lastName) кортежей, мне нужно получить города.

Я могу думать о следующих решениях.

1

Создайте запрос выбора, например,

SELECT city from user where (firstName=x and lastName=y) or (firstName=a and lastName=b) or .....

2

Загрузите все значения firstName, lastName в промежуточную таблицу и выполните соединение между таблицей "user" и новой промежуточной таблицей.

Есть ли какие-либо варианты решения этой проблемы и что является предпочтительным для решения этой проблемы в целом?

Ответ 1

Вы можете сделать следующее:

SELECT city FROM user WHERE (firstName, lastName) IN (('a', 'b'), ('c', 'd'));

sqlfiddle.

Ответ 2

Часто становится легче загружать ваши данные в базу данных, даже если это только для запуска быстрого запроса. Жестко закодированные данные кажутся быстрыми, но быстро становится больно, если вы начинаете делать изменения.

Однако, если вы хотите скопировать имена непосредственно в свой запрос, это более чистый способ сделать это:

with names (fname,lname) as (
    values
        ('John','Smith'),
        ('Mary','Jones')
)
select city from user
    inner join names on
        fname=firstName and
        lname=lastName;

Преимущество этого заключается в том, что он несколько отделяет ваши данные от запроса.

(Это синтаксис DB2, возможно, вам потребуется небольшая настройка в вашей системе).

Ответ 3

В Oracle вы можете сделать это:

SELECT * FROM table1 WHERE (col_a,col_b) IN (SELECT col_x,col_y FROM table2)

Ответ 4

Убедитесь, что у вас есть индекс в столбцах firstname и lastname и перейдите к 1. Это действительно не будет иметь большого влияния на производительность.

РЕДАКТИРОВАТЬ: После комментария @Dems относительно рассылки кеша плана лучшим решением может быть создание вычисленного столбца в существующей таблице (или отдельном представлении), который содержал бы связанное имя Firstname + Lastname, что позволяет выполнить запрос, например

SELECT City 
FROM User 
WHERE Fullname in (@fullnames)

где @fullnames выглядит немного как "'JonDoe', 'JaneDoe'" и т.д.

Ответ 5

Определите, отличается ли список имен с каждым запросом или используется повторно. Если он используется повторно, он принадлежит базе данных.

Даже если он уникален для каждого запроса, может быть полезно загрузить его во временную таблицу (синтаксис #table) по соображениям производительности - в этом случае вы сможете избежать перекомпиляции сложного запроса.

Если максимальное число имен исправлено, вы должны использовать параметризованный запрос.

Однако, если ни одно из приведенных выше случаев не применяется, я хотел бы включить в запрос имена в запросе, как в вашем подходе # 1.

Ответ 6

В общем вы можете легко написать Where-Condition, как это:

select * from tab1
where (col1, col2) in (select col1, col2 from tab2)

Примечание
Oracle игнорирует строки, в которых один или несколько выбранных столбцов имеют значение NULL. В этих случаях вы, вероятно, захотите использовать NVL -Funktion для сопоставления NULL со специальным значением (которое не должно быть в значениях)

select * from tab1
where (col1, NVL(col2, '---') in (select col1, NVL(col2, '---') from tab2)

oracle sql