CODEWITHSUNDEEP
sqlpostgresql
Oto Shavadze
Oto Shavadze

Reputation: 42753

Get data from both table, where data isn't common for this tables

I have 2 tables

TABLE jt1

name
---
A
B
C

and TABLE jt2

name
---
B
C
D

I need get names from both tables, which is not common for both tables, that is result must be

result
------
A
D

This is my query, but may be there is better solution for this?

    SELECT jt1.name  AS name  FROM jt1
    LEFT JOIN jt2
    ON jt1.name = jt2.name
    WHERE jt2.name IS NULL
    
    UNION
    
    SELECT jt2.name  AS name  FROM jt2
    LEFT JOIN jt1
    ON jt2.name = jt1.name
    WHERE jt1.name IS NULL

Upvotes: 0

Views: 135

Answers (5)

wildplasser
wildplasser

Reputation: 44240

SELECT    COALESCE(jt1.name,  jt2.name) AS zname
FROM      jt1
FULL JOIN jt2 ON jt1.name = jt2.name
WHERE     jt2.name IS NULL OR jt1.name IS NULL
  ;

BTW: the naive solution could probably be faster:

SELECT name
FROM a (WHERE NOT EXISTS SELECT 1
        FROM b WHERE b.name = a.name)
UNION ALL
SELECT name 
FROM b (WHERE NOT EXISTS SELECT 1
        FROM a WHERE a.name = b.name)
   ;

BTW: I purposely use UNION ALL here, because I know that the two legs cannot have any overlap, and the removal of duplicates can be omitted.

Upvotes: 2

Denis de Bernardy
Denis de Bernardy

Reputation: 78433

Not very efficient, but you could use an exclude statement:

Select name from A
Union
Select name from B
Except
Select A.name from A join B on B.name = A.name

This full join should also do the trick:

Select coalesce(a.name, b.name) as name
From a full join b on a.name = b.name
Where a.name is null or b.name is null

Upvotes: 0

user330315
user330315

Reputation:

A combination of EXCEPT and UNION will do the trick as well.

I can't tell if that is more efficient that the other solutions though:

(
  SELECT name 
  FROM jt1
  EXCEPT
  SELECT name 
  FROM jt2
)
UNION 
(
  SELECT name 
  FROM jt2
  EXCEPT
  SELECT name 
  FROM jt1
)  
ORDER BY Name;

(The paranthesises are not really necessary, I just added them to visualize the approach)

Upvotes: 1

Himanshu
Himanshu

Reputation: 32602

You can use FULL JOIN for this:

SELECT    jt1.name 
  FROM      jt1
  FULL JOIN jt2
         ON jt1.name = jt2.name
  WHERE     jt2.name IS NULL
UNION
SELECT jt2.name 
  FROM jt1
  FULL JOIN jt2
         ON jt1.name = jt2.name
  WHERE jt1.name IS NULL
ORDER BY Name;

Output:

╔══════╗
║ NAME ║
╠══════╣
║ A    ║
║ D    ║
╚══════╝

See this SQLFiddle

Upvotes: 0

RoughPlace
RoughPlace

Reputation: 1121

you could do something along the lines of 

select * from jt where jt.name not in (select name from jt2)
union
select * from jt2 where jt2.name not in (select name from jt)

or if the entries are only singular in each table you could do a count and a union all or you could even setup something with a full outer join (probably not very fast) or you could setup a cursor and temporary tables to do this

ultimately it will depend on how your indexes are set up and the amount of data involved and the architecture to determine which is best

probably worth trying a few and profiling them yourself to find out

Upvotes: 0

Related Questions