Соедините 2 таблицы в SQL и создайте столбец флагов на основе совпадения

Я новичок в SQL. У меня есть 2 таблицы]

Таблица 1:

id   product  location
1    banana    costco
2    apple     walmart
3    lemons    target

Таблица 2:

id
1
2
4

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

id   flag
1    true
2    true 
3    false
4    true

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

Как я могу сделать это в SQL (работает в Athena)?

Я могу присоединиться через подписку, но я не знаю, как создать столбец на основе совпадения

SELECT t2.id from table2 t2,
LEFT JOIN table1 t1 ON t1.id=t2.id

Вы пометили две разные СУБД: MySQL и Athena. Если вы используете Athena, удалите тег MySQL, пожалуйста. Отмечайте только те СУБД, которые вы используете, чтобы получать только те ответы, которые работают для вас, и избавить нас от работы.

Thorsten Kettner 14.01.2023 09:31
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
2
1
65
5
Перейти к ответу Данный вопрос помечен как решенный

Ответы 5

Попробуйте с UNION:

SELECT 
    id,
    true AS flag 
FROM 
    table2

UNION 

SELECT 
    id,
    false AS flag 
FROM 
    table1
WHERE 
    id NOT IN (
        SELECT id FROM table2
    )
SELECT
id,
CASE
WHEN id in (SELECT id FROM table2) THEN 'true'
ELSE 'false'
END AS 'flag'
FROM table1
UNION
SELECT
id,
CASE
WHEN EXISTS (SELECT 1 FROM table2) THEN 'true'
ELSE 'false'
END AS 'flag'
FROM table2;
Ответ принят как подходящий

То, что вы ищете, — это полное внешнее соединение, но MySQL — одна из очень немногих СУБД, не поддерживающих полные внешние соединения.

Полное внешнее соединение будет выглядеть так:

SELECT 
  COALESCE(t1.id, t2.id) AS id,
  (t1.id IS NOT NULL AND t2.id IS NOT NULL) AS flag
FROM table1 t1
FULL OUTER JOIN table2 t2 ON t2.id = t1.id
ORDER BY COALESCE(t1.id, t2.id);

Вот два альтернативных подхода:

Соберите все идентификаторы, затем проверьте, существуют ли они в обеих таблицах:

SELECT 
  id
  id IN (SELECT id FROM table1)
   AND
  id IN (SELECT id FROM table2) AS flag
FROM
(
  SELECT id FROM table1
  UNION 
  SELECT id FROM table2
) ids
ORDER BY id;

ВЫБЕРИТЕ совпадения, отсутствующие идентификаторы table2 и отсутствующие идентификаторы table1, а затем объедините эти результаты.

SELECT id, true AS flag FROM table1 JOIN table2 USING (id)
UNION ALL
SELECT id, false AS flag FROM table1 WHERE id NOT IN (SELECT id FROM table2)
UNION ALL
SELECT id, false AS flag FROM table2 WHERE id NOT IN (SELECT id FROM table1)
ORDER BY id;

ОБНОВЛЕНИЕ: я только что увидел, что вы отметили и MySQL, и Amazon Athena. Это две разные СУБД. MySQL не поддерживает полные внешние соединения, в отличие от Athena. Итак, для Athena должны работать все три запроса, а для MySQL — только второй и третий.

Select 
(case when table1.id is null then table2.id else table1.id end) as id, 
(case when table2.id is null then false else true end) as flag 
from table1 full join table2 
on table1.id = table2.id

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

Вы можете сделать это следующим образом:

select t2.id, 'true' as flag
from table2 t2
union
select id, 'false' as flag
from table1
where id not in ( select id from table2)

Поскольку все идентификаторы в таблице 2 должны быть true, а остальные, которых нет в таблице 2, должны быть false, в объединении нет необходимости.

вы можете попробовать это здесь: https://dbfiddle.uk/QIPoFRTb

Это для того, чтобы они заказали:

select *
from (
     select t2.id, 'true' as flag
     from table2 t2
     union
     select id, 'false' as flag
     from table1
     where id not in ( select id from table2)
) as s
order by id;

Другие вопросы по теме