CODEWITHSUNDEEP
sqlsql-server
wouter de jong
wouter de jong

Reputation: 597

let union show values beside each other instead of below each other

I got a SQL query which displays values out of two tables below each other. Now I want the table to select the values out of the two different tables besides each other. This is my query right now:

(select i1,i2 from t1
except
select i1,i2 from t2)
union all
(select i1,i2 from t2
except
select i1,i2 from t1)

This is my sample data:

Data I have in table1: 

 i1   i2
---------
| 1 | 1 |
---------
| 2 | 2 |
---------
| 2 | 3 |
---------

data i have in table2:

 i1   i2
---------
| 1 | 1 |
---------
| 2 | 3 |
---------
| 2 | 4 |
---------

outcome:

---------
| 2 | 2 |  << this one comes from table 1
---------
| 2 | 4 |  << this one comes from table 2
---------

wanted outcome:

   t1      t2
-----------------
| 2 | 2 | 2 | 4 |
-----------------

Upvotes: 2

Views: 1554

Answers (3)

sandeep rawat
sandeep rawat

Reputation: 4957

try 

SELECT *
FROM 
    (
        SELECT i1, i2
        FROM t1

        EXCEPT

        SELECT i1, i2
        FROM t2
    ) a, 
    (
        SELECT i1, i2
        FROM t2

        EXCEPT

        SELECT i1, i2
        FROM t1
    ) b

Upvotes: 0

Jota Pardo
Jota Pardo

Reputation: 878

I think the best way would be to use a FULL JOIN and then perform a WHERE with the NULL data. Because if you make a CROSS JOIN you would get more data than you need.

SELECT *
FROM t1
FULL JOIN t2
    ON t1.i1 = 2.i1
        AND t1.i2 = t2.i2
WHERE t1.Id IS NULL
    OR t2.Id IS NULL

I'll explain with an example:

IF OBJECT_ID('tempdb..#t1') IS NOT NULL
            DROP TABLE #t1

CREATE TABLE #t1
(
    Id INT IDENTITY,
    i1 INT,
    i2 INT
)

INSERT INTO #t1
(
    i1,i2
)
VALUES
(1,1)
,(2,2)
,(2,3)
,(2,6)

SELECT * FROM #t1

IF OBJECT_ID('tempdb..#t2') IS NOT NULL
            DROP TABLE #t2

CREATE TABLE #t2
(
    Id INT IDENTITY,
    i1 INT,
    i2 INT
)

INSERT INTO #t2
(
    i1,i2
)
VALUES
(1,1)
,(2,3)
,(2,4)
,(2,5)
,(2,7)

SELECT * FROM #t2


SELECT *
FROM #t1
FULL JOIN #t2
    ON #t1.i1 = #t2.i1
        AND #t1.i2 = #t2.i2
WHERE #t1.Id IS NULL
    OR #t2.Id IS NULL

SELECT *
FROM #t1 a
CROSS JOIN #t2 b
WHERE NOT EXISTS (SELECT 1
                  FROM #t2 c
                  WHERE a.i1 = c.i1 
                    AND a.i2 = c.i2
                  )
  AND NOT EXISTS (SELECT 1
                  FROM #t1 c
                  WHERE b.i1 = c.i1 
                    AND b.i2 = c.i2
                  )

RESULT

In the first case you would get 5 records because (2,2) and (2,6) do not exist in t2, and (2,4), (2,5), (2,7) do not exist in t1. So you would have 5 results.

Id          i1          i2          Id          i1          i2
----------- ----------- ----------- ----------- ----------- -----------
2           2           2           NULL        NULL        NULL
4           2           6           NULL        NULL        NULL
NULL        NULL        NULL        3           2           4
NULL        NULL        NULL        4           2           5
NULL        NULL        NULL        5           2           7

(5 row(s) affected)

However, in the CROSS JOIN you would get 6 results because you would make a Cartesian product. 2 x 3 = 6 CROSS JOIN Explanation

Id          i1          i2          Id          i1          i2
----------- ----------- ----------- ----------- ----------- -----------
2           2           2           3           2           4
2           2           2           4           2           5
2           2           2           5           2           7
4           2           6           3           2           4
4           2           6           4           2           5
4           2           6           5           2           7

(6 row(s) affected)

Upvotes: 1

Hart CO
Hart CO

Reputation: 34774

You can do this with a CROSS JOIN and NOT EXISTS:

SELECT *
FROM t1 a
CROSS JOIN t2 b
WHERE NOT EXISTS (SELECT 1
                  FROM t2 c
                  WHERE a.i1 = c.i1 
                    AND a.i2 = c.i2
                  )
  AND NOT EXISTS (SELECT 1
                  FROM t1 c
                  WHERE b.i1 = c.i1 
                    AND b.i2 = c.i2
                  )

A CROSS JOIN joins every record from one table with every record from the other, so every combination of rows is returned. NOT EXISTS is used to filter out records from t1 that ever appear in t2 and vice versa for the 2nd NOT EXISTS

Upvotes: 0

Related Questions