CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Alex Gordon
Alex Gordon

Reputation: 60851

How to perform a join in SQL Server without using tables

I have two lists and I want to see what the two lists DON'T have in common. For example:

List1:
'a','b','c','123'

List2:
'd','e','f','a','asd','c'

I want output to be:

'b','123','d','e','f','asd'

Something like this?

select * from ('a','b','c','123')
join ('d','e','f','a','asd','c')
on ???

Is there a pure SQL Server solution for this without using tables?

Upvotes: 1

Views: 101

Answers (5)

Ray Saltrelli
Ray Saltrelli

Reputation: 4218

I think you'll have to insert the values into 2 variable tables.

DECLARE @Table1 TABLE (Value VARCHAR(1))
DECLARE @Table2 TABLE (Value VARCHAR(1))

INSERT INTO @Table1 (Value) VALUES ('a')
INSERT INTO @Table1 (Value) VALUES ('b')

INSERT INTO @Table2 (Value) VALUES ('b')
INSERT INTO @Table2 (Value) VALUES ('c')

Then perform some set operations on the 2 tables.

DECLARE @TableUnion TABLE (Value VARCHAR(1))
DECLARE @TableIntersection TABLE (Value VARCHAR(1))
DECLARE @TableExcept TABLE (Value VARCHAR(1))

INSERT INTO @TableUnion 
SELECT * FROM
    ((SELECT * FROM @Table1)
    UNION
    (SELECT * FROM @Table2)) U

INSERT INTO @TableIntersection 
SELECT * FROM
    ((SELECT * FROM @Table1)
    INTERSECT
    (SELECT * FROM @Table2)) I

INSERT INTO @TableExcept
SELECT * FROM
    ((SELECT * FROM @TableUnion)
    EXCEPT
    (SELECT * FROM @TableIntersection)) E

The result set of the final select statement will contain 'a' and 'c'. Which can be concatenated into a single string as follows.

DECLARE @ExceptString VARCHAR(3)

SELECT @ExceptString = 
    CASE 
        WHEN @ExceptString IS NULL THEN Value
        ELSE @ExceptString + ',' + Value
    END
FROM @TableExcept

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280520

Given this function:

CREATE FUNCTION dbo.SplitStrings ( @List NVARCHAR(MAX) )
RETURNS TABLE
AS
   RETURN ( SELECT Item FROM (
           SELECT Item = x.i.value('(./text())[1]', 'nvarchar(max)')
           FROM (
               SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List,',', '</i><i>') 
                    + '</i>').query('.')) AS a CROSS APPLY [XML].nodes('i') AS x(i)
       ) AS y WHERE Item IS NOT NULL);
GO

You can do it with a full outer join:

DECLARE 
    @list1  NVARCHAR(MAX) = N'a,b,c,123', 
    @list2  NVARCHAR(MAX) = N'd,e,f,a,asd,c',
    @output NVARCHAR(MAX) = N'';

SELECT @output += N',' + COALESCE(l1.Item, l2.Item) 
  FROM dbo.SplitStrings(@list1) AS l1
  FULL OUTER JOIN dbo.SplitStrings(@list2) AS l2
  ON l1.Item = l2.Item 
  WHERE l1.Item IS NULL OR l2.Item IS NULL;

SELECT STUFF(@output, 1, 1, N'');

Or similar to @JNK's:

DECLARE 
    @list1  NVARCHAR(MAX) = N'a,b,c,123', 
    @list2  NVARCHAR(MAX) = N'd,e,f,a,asd,c',
    @output NVARCHAR(MAX) = N'';

;WITH l1 AS (SELECT Item FROM dbo.SplitStrings(@list1)), 
      l2 AS (SELECT Item FROM dbo.SplitStrings(@list2))
SELECT @output += N',' + Item 
FROM ( (SELECT Item FROM l1 EXCEPT SELECT Item FROM l2)
        UNION
       (SELECT Item FROM l2 EXCEPT SELECT Item FROM l1)) AS x;

SELECT STUFF(@output, 1, 1, N'');

And probably a variety of other ways too. If order matters, it's going to be a little more complex, but still possible.

Upvotes: 3

stevepastelan
stevepastelan

Reputation: 1304

How about:

with
list1(j) as (select 'a' union select 'b'),
list2(j) as (select 'b' union select 'c')

select coalesce(list1.j, list2.j)
from list1 full join list2
on list1.j = list2.j
where (list1.j is null or list2.j is null)

Upvotes: 1

JNK
JNK

Reputation: 65197

If you have control over the lists, I would just make them table variables:

DECLARE @a TABLE (str varchar(100))
INSERT INTO @a
VALUES
('a'),
('b')...

DECLARE @b table (str varchar(100))
INSERT INTO @b
VALUES
...

(SELECT str FROM @a
EXCEPT 
SELECT str FROM @b)
UNION
(SELECT str FROM @b
EXCEPT
SELECT str FROM @a)

Upvotes: 5

Stelian Matei
Stelian Matei

Reputation: 11623

There is no easy way to accomplish this. To filter the values from a list you need to have them as rows. So you would end up with something like:

SELECT col FROM (
SELECT 'a' as col
UNION
SELECT 'b'
UNION
SELECT 'c') t

WHERE col NOT IN ('a', 'b')

Upvotes: 1

Related Questions