Я новичок в 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
Попробуйте с 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;
Вы пометили две разные СУБД: MySQL и Athena. Если вы используете Athena, удалите тег MySQL, пожалуйста. Отмечайте только те СУБД, которые вы используете, чтобы получать только те ответы, которые работают для вас, и избавить нас от работы.