Trying to find the overlapping columns between two tables and use the result in the same SQL query.
Example of code with two subqueries that work individually but not able to combine them together:
WITH DIFF AS (
SELECT column_name AS Columns
-- SELECT ARRAY_AGG(column_name) AS Columns
-- SELECT STRING_AGG(column_name) AS Columns
FROM (
SELECT column_name, 'S' AS S
FROM DB.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
WHERE table_name = 'T1'
INTERSECT DISTINCT
SELECT column_name, 'S' AS S
FROM DB.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
WHERE table_name = 'T2'
)
-- GROUP BY S
)
SELECT T1.*, T2.* EXCEPT(SELECT Columns FROM DIFF)
-- SELECT T1.*, T2.* EXCEPT (T1.*)
FROM DB.T1
LEFT JOIN DB.T2 USING(GUID)
Using Python pandas, the join is so easy because of the power of suffixes in case of duplicated columns but in the current SQL, we are missing this power.
