Join 2 tables in SQL and create a flag column based on a match-CodePudding

I am new to SQL. I have 2 tables]

Table 1:

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

Table 2:

id
1
2
4

I want to join these 2 tables based on id. If there is a match, I want to create a new column in resultant table so that my result looks like this

id   flag
1    true
2    true 
3    false
4    true

i.e if the id exists in table2, I want to flag as true else false. I want to include all ids in the resultant table

How can I do this in SQL (Running in Athena)?

I can join through following but I don't know to create a column based on a match

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

CodePudding user response：

Try with a UNION:

SELECT 
    id,
    true AS flag 
FROM 
    table2

UNION 

SELECT 
    id,
    false AS flag 
FROM 
    table1
WHERE 
    id NOT IN (
        SELECT id FROM table2
    )

CodePudding user response：

What you are looking for is a full outer join, but MySQL is one of the very few RDBMS that don't support full outer joins.

A full outer join would look like this:

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);

Here are two alternative approaches:

Collect all IDs, then check whether they exist in both tables:

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;

SELECT the matches, the missing table2 IDs, and the missing table1 IDs, then union these results.

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;

UPDATE: I just see that you tagged both MySQL and Amazon Athena. These are two different DBMS. MySQL does not support full outer joins, Athena does. So, for Athena all three queries should work, while for MySQL ony the second and third will.

CodePudding user response：

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;

CodePudding user response：

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

full join will help get you all ids, in selection you can use case statement to avoid picking nulls in id and flag columns.

CodePudding user response：

You can do it as follows :

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)

Since all ids in table2 must be true, and the others that are not in table2 must be false there is no need of join.

you can try it from here : https://dbfiddle.uk/QIPoFRTb

This is to have them ordered :

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;