I want to run two queries that are pretty similar such that the query returns one table with two columns (one column for each query ran)

Query #1:

SELECT groupID 
FROM tbl 
WHERE username = 'admin' AND permission LIKE 'sample.%'

Query #2:

SELECT groupID 
FROM tbl 
WHERE username = 'administrator' AND permission LIKE 'sample.%'

Desired return:

Is it possible? I was able to get the expected return by doing a FULL OUTER JOIN but I read that JOINS are between two tables and this only looks at one table.

CodePudding user response：

You could try assigning a row number first to each row of each table, then match the two table on this ranking:

WITH cte_admin AS (
         SELECT groupID, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS rn
         FROM tbl1 
     ),
     cte_administrator AS (
         SELECT groupID, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS rn
         FROM tbl2
     )
SELECT t1.groupID AS g1,
       t2.groupID AS g2
FROM       cte_admin t1
INNER JOIN cte_administrator t2
        ON t1.rn = t2.rn
UNION
SELECT t1.groupID AS g1,
       t2.groupID AS g2
FROM      cte_admin t1
LEFT JOIN cte_administrator t2
       ON t1.rn = t2.rn
WHERE t2.rn IS NULL
UNION
SELECT t1.groupID AS g1,
       t2.groupID AS g2
FROM      cte_administrator t1
LEFT JOIN cte_admin  t2
       ON t1.rn = t2.rn
WHERE t2.rn IS NULL

A fully tested solution will be provided if you can share contents from the table tbl.

For the full outer join, three joins are needed:

• INNER JOIN to match corresponding row numbers between the two tables
• LEFT JOIN from table1 to table2 WHERE table2 is null to match excess rows from table1 in case this has more rows than table2
• LEFT JOIN from table2 to table1 WHERE table1 is null to match excess rows from table2 in case this has more rows than table1

A pseudo test is done here.