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

Question

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

That is not the purpose, or how a union works. Use a join with a NOT EXISTS clause. — OldProgrammer, Jan 06 '17 at 16:43
I don't think it's a bad question, it's just hard to follow your data examples, would be better if they had column headers probably. — Hart CO, Jan 06 '17 at 16:51
Agreed with @HartCO on this one. You are just subject to some serial downvoters for asking basic questions, but the column header would have helped a lot. — S3S, Jan 06 '17 at 16:53
This question is a total mystery with column names that mean anything. — Hogan, Jan 06 '17 at 16:59
Can you show us what would be the result if you add de record (2,6) in t1 and add the records (2,5),(2,7) in t2? Please — JotaPardo, Jan 06 '17 at 21:08

score 1 · Accepted Answer · edited May 23 '17 at 12:24

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)

I thought of using a full join, but to get the '2,2' record on the same line as the '2,4' record you'd have to use `<>` and `OR`, and then you still have to filter unwanted lines using the `NOT EXIST`s. — Hart CO, Jan 06 '17 at 20:08
@HartCO Yes, I agree with you. I asked the user to give us more details in case he added additional records. So I can see better what it is that you want. — JotaPardo, Jan 06 '17 at 21:12

score 0 · Answer 2 · answered Jan 06 '17 at 17:06

0

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

answered Jan 06 '17 at 17:06

Hart CO

34,064
6
48
63

That would return more records than needed if the user has much more data. I would make a Cartesian product that I do not think the user needs. – JotaPardo Jan 06 '17 at 17:29
You might be underestimating the query optimizer, but I'd love to see an alternative that returns the desired output. – Hart CO Jan 06 '17 at 20:03

score 0 · Answer 3 · edited Jan 06 '17 at 21:24

0

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

edited Jan 06 '17 at 21:24

JotaPardo

817
9
27

answered Jan 06 '17 at 17:52

sandeep rawat

4,797
1
18
36

It returns the same result of CROSS JOIN, isn´t it? – JotaPardo Jan 06 '17 at 21:04

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

3 Answers3