Избегайте повторения строк в SQL-запросе

Литерал массива в однострочном подзапросе: демо в db<>fiddle

select * from gizmo_details, 
(select array['foo','bar','baz','bletch'])_(my_string_arr)
where _gizmo_name=any(my_string_arr) 
  or gizmo_id in(select id from gizmos 
                 where gizmo_name=any(my_string_arr));

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

Однострочная таблица:

create table _(my_string_arr)as values('{foo,bar,baz,bletch}'::text[]);

select * from gizmo_details, _
where _gizmo_name=any(my_string_arr) 
  or gizmo_id in(select id from gizmos 
                 where gizmo_name=any(my_string_arr));

Встроенная постоянно возвращающая функция:

create function _()returns text[] return '{foo,bar,baz,bletch}'::text[];

select * from gizmo_details 
where _gizmo_name=any(_() ) 
  or gizmo_id in(select id from gizmos 
                 where gizmo_name=any(_() ));

Объясните, анализ показывает, что все вызовы этой функции заменяются константой.

Пользовательская переменная всего сеанса:

set my.var='{foo,bar,baz,bletch}';

select * from gizmo_details 
where _gizmo_name=any(current_setting('my.var')::text[]) 
  or gizmo_id in(select id from gizmos 
                 where gizmo_name=any(current_setting('my.var')::text[]));

Psql также позволяет \set использовать его как отдельную переменную на стороне клиента:

\set string_arr $$'{foo,bar,baz,bletch}'$$

select * from gizmo_details
where _gizmo_name=any(:string_arr)
  or gizmo_id in(select id from gizmos 
                 where gizmo_name=any(:string_arr));

Как упомянул @Erwin Brandstetter, одним из преимуществ определения функции или сохранения константы в таблице является то, что вы делаете это один раз, централизованно, после чего они могут быть разделены и повторно использованы разными клиентами по отдельности. сеансы. CTE или подзапрос должен каждый раз входить в тело оператора, а переменные сеанса/клиента вообще не могут быть общими.

Использование CTE:

WITH knownGizmos AS (
    SELECT name
    FROM
    (
        VALUES
            ( 'foo' ), ( 'bar' ), ( 'baz' ), ( 'bletch' )
    ) gn ( name )
)
SELECT
    *
FROM
    gizmo_details AS gd
    INNER JOIN gizmos AS g ON g.id = gd.gizmo_id

    INNER JOIN knownGizmos ON g.gizmo_name = knownGizmos.name

К сожалению, конструктор таблиц VALUES() ISO SQL чрезвычайно многословен по сравнению с литералом массива Postgres.

Альтернативой использованию VALUES() является старый добрый UNION:

WITH knownGizmos AS (
    SELECT 'foo'    AS name UNION
    SELECT 'bar'    AS name UNION
    SELECT 'baz'    AS name UNION
    SELECT 'bletch' AS name
)
SELECT
    *
FROM
    gizmo_details AS gd
    INNER JOIN gizmos AS g ON g.id = gd.gizmo_id

    INNER JOIN knownGizmos ON g.gizmo_name = knownGizmos.name

С CTE это будет выглядеть так

with t as (
  select array['foo', 'bar', 'baz', 'bletch'] arr)
select gd.* from gizmo_details gd, t
where _gizmo_name = any (t.arr)
or gizmo_id 
in (select id from gizmos where gizmo_name = any(t.arr));

Поддержание списка строк в двух SQL-запросах можно интерпретировать по-разному. Если вы хотите создать список из одного запроса и использовать его в другом или сравнить списки из двух запросов, вот разные подходы для общих баз данных SQL:

1.  Using Temporary Tables:You can store the results of the first query into a temporary table and then use it in the second query.-- First query, store results in a temporary table
    CREATE TEMPORARY TABLE temp_list (value VARCHAR(255));
    INSERT INTO temp_list (value)
    SELECT stringValue FROM your_table WHERE some_condition;
    -- Second query, use results from the temporary table
    SELECT *
    FROM another_table
    WHERE another_column IN (SELECT value FROM temp_list);
    -- Optionally, drop the temporary table if it's no longer needed
    DROP TEMPORARY TABLE temp_list;

2.  Using Subqueries:You can use a subquery directly within the IN clause of your second query.SELECT *
    FROM another_table
    WHERE another_column IN (
        SELECT stringValue
        FROM your_table
        WHERE some_condition
    );
3.  Using Common Table Expressions (CTEs):With Common Table Expressions, you can name a subquery and reference it in the main query.WITH list AS (
        SELECT stringValue
        FROM your_table
        WHERE some_condition
    )
    SELECT *
    FROM another_table
    WHERE another_column IN (SELECT stringValue FROM list);

4.  Using Joins:If you want to join the results of two queries based on strings, you can join tables on the string columns.SELECT a.*
    FROM another_table a
    JOIN your_table b ON a.another_column = b.stringValue
    WHERE b.some_condition;

5.  Storing List in Application Memory:If the lists are not too large, you can store the list of strings from the first query in your application's memory and use it to construct the second query.In pseudocode:list = executeQuery("SELECT stringValue FROM your_table WHERE some_condition")
    executeQuery("SELECT * FROM another_table WHERE another_column IN (" + list.join(", ") + ")")Remember to properly parameterize your queries to prevent SQL injection when using this method.

6.  Using Variables:In some databases like MySQL, you can store results in variables and use them in subsequent queries within the same session.SET @list := (SELECT GROUP_CONCAT(stringValue SEPARATOR ',') FROM your_table WHERE some_condition);
    SET @query := CONCAT('SELECT * FROM another_table WHERE another_column IN (', @list, ')');
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

Соответствующий подход зависит от возможностей вашей базы данных SQL и особенностей вашего варианта использования, таких как размер данных, соображения производительности и необходимость выполнения операции в рамках одной транзакции или сеанса.

Вы выполняете одно и то же сравнение для двух столбцов. Общее решение, позволяющее избежать повторения, — поместить значения из каждого столбца в строки искусственного столбца и выполнить сравнение в этом столбце. Этот подход создает повторяющиеся совпадения, поэтому необходима дополнительная работа.

В вашем примере поместите имена гизмо из основной и подробной таблиц в один столбец и используйте оператор in в этом столбце. Это может создать две строки для каждого гизмо, поэтому подавите их с помощью полусоединения:

select *
from gizmo_details
where exists (
    select *
    from gizmos, lateral (values
        (gizmo_details._gizmo_name),
        (gizmos.gizmo_name)
    ) as x(name)
    where gizmo_details.gizmo_id = gizmos.id
    and x.name in ('foo', 'bar', 'baz', 'bletch')
)

Или отдельно:

select distinct on (gizmo_details.id) gizmo_details.*
from gizmos
join gizmo_details on gizmos.id = gizmo_details.gizmo_id, lateral (values
    (gizmo_details._gizmo_name),
    (gizmos.gizmo_name)
) as x(name)
where x.name in ('foo', 'bar', 'baz', 'bletch')

СУХОЙ - Не повторяйтесь.

Повторяющиеся строки?

Несколько строк в gizmo_details могут соответствовать одной и той же входной строке. Такие дубликаты сохраняются.
Но одна и та же строка в gizmo_details может соответствовать нескольким входным строкам (если могут быть повторяющиеся входные данные) или один и тот же gizmo_details.gizmo_id может соответствовать несколько раз. Такие дубликаты удаляются.

Это может быть или не быть проблемой. Это просто то, что обычно упускают из виду. Некоторые из опубликованных на данный момент решений в этом отношении не соответствуют вашему оригиналу.

Если в любом из трех наборов (таблицы, входные данные) могут быть дубликаты, возможно, вы захотите объяснить, как бороться с дубликатами. Существуют подходящие стили запросов для любой возможной спецификации.

Вы не просили оптимизировать производительность запросов (что может быть возможно помимо всего остального здесь), но правильность всегда на первом месте.

Повторение в рамках одного запроса

CTE — это один из способов сохранить одиночный запрос СУХИМ:

WITH input(gizmo_name) AS (
   SELECT unnest('{foo, bar, baz, bletch}'::text[])  -- your input as array
   )
SELECT *
FROM   gizmo_details
WHERE  _gizmo_name = ANY (TABLE input)
OR     gizmo_id IN (SELECT g.id FROM gizmos g JOIN input USING (gizmo_name));

Во многом это сводится к вопросу определения констант в SQL. Видеть:

• Есть ли способ определить именованную константу в запросе PostgreSQL?

Повторение в течение одного сеанса

Чтобы повторять этот запрос снова и снова, подготовленные операторы — это один из способов сохранить его СУХИМ:

PREPARE qr1 (text[]) AS
SELECT *
FROM   gizmo_details gd
WHERE  gd._gizmo_name = ANY ($1)
OR     gd.gizmo_id IN (SELECT g.id FROM gizmos g WHERE g.gizmo_name = ANY ($1));

Вызов:

EXECUTE qr1('{foo, bar, baz, bletch}'::text[]);  -- array input

Это (также) предотвращает повторение ввода внутри запроса.

Подготовленные операторы встроены во многие клиентские библиотеки в качестве стандартной функции.

Повторение между сеансами

Функция на стороне сервера сохраняет это значение «СУХИМ». Входной параметр VARIADIC — это просто дополнительная функция. Видеть:

• Передача нескольких значений в одном параметре

CREATE OR REPLACE FUNCTION f_my_func(VARIADIC _gizmo_names text[])
  RETURNS SETOF gizmo_details
  LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
SELECT *
FROM   gizmo_details gd
WHERE  gd._gizmo_name = ANY (_gizmo_names)
OR     gd.gizmo_id IN (SELECT g.id FROM gizmos g WHERE g.gizmo_name = ANY (_gizmo_names));
$func$

Вызов:

SELECT * FROM f_my_func('foo', 'bar', 'baz', 'bletch');  -- list input

Или:

SELECT * FROM f_my_func(VARIADIC '{foo, bar, baz, bletch}');  -- back to array input

Гораздо более универсален, чем подготовленные операторы, плюс его можно вкладывать во внешние запросы (в отличие от подготовленного оператора).

Кроме того, использование _ в качестве префикса для переменных и/или параметров, но никогда для имен столбцов, является общепринятым соглашением об именах. Необязательно, но я придерживаюсь этого.

WITH string_list AS (
    SELECT 'foo' AS value UNION ALL
    SELECT 'bar' UNION ALL
    SELECT 'baz' UNION ALL
    SELECT 'bletch'
)
SELECT
    *
FROM
    gizmo_details
WHERE
    _gizmo_name IN (SELECT value FROM string_list)
    OR gizmo_id IN (
        SELECT id FROM gizmos
        WHERE gizmo_name IN (SELECT value FROM string_list)
    );

Чтобы избежать повторения списка строк и сделать ваш код SQL более удобным в обслуживании, вы можете использовать общее табличное выражение (CTE) или временную таблицу, чтобы сохранить список значений один раз и ссылаться на него несколько раз. Таким образом, если вам нужно обновить список, вам нужно будет сделать это только в одном месте. Два подхода:

Использование общего табличного выражения (CTE):

WITH GizmoNames AS (
    SELECT 'foo' AS gizmo_name UNION ALL
    SELECT 'bar' UNION ALL
    SELECT 'baz' UNION ALL
    SELECT 'bletch'
)
SELECT
    *
FROM
    gizmo_details
WHERE
    _gizmo_name IN (SELECT gizmo_name FROM GizmoNames)
    OR gizmo_id IN (
        SELECT id
        FROM gizmos
        WHERE gizmo_name IN (SELECT gizmo_name FROM GizmoNames)
    );

Использование временной таблицы:

CREATE TEMPORARY TABLE GizmoNames (gizmo_name VARCHAR(255));

INSERT INTO GizmoNames (gizmo_name) VALUES
    ('foo'), ('bar'), ('baz'), ('bletch');

SELECT
    *
FROM
    gizmo_details
WHERE
    _gizmo_name IN (SELECT gizmo_name FROM GizmoNames)
    OR gizmo_id IN (
        SELECT id
        FROM gizmos
        WHERE gizmo_name IN (SELECT gizmo_name FROM GizmoNames)
    );

DROP TEMPORARY TABLE GizmoNames;