CODEWITHSUNDEEP
mysqlsql-server
Sunitha Bharathy
Sunitha Bharathy

Reputation: 109

How to retrieve the not common values from 2 tables?

I have the 2 following tables t1, t2 with values,

t1        t2
1         4
2         2
3         3

Now I want to output 

1 
4

How can I get this output in select query ?

Upvotes: 1

Views: 10450

Answers (3)

gbn
gbn

Reputation: 432261

Another way. Just COUNT them.

This works if the values are unique per table

SELECT
    CombinedValue
FROM
    (
    SELECT t1 AS CombinedValue FROM t1
    UNION ALL
    SELECT t2 FROM t2
    ) foo
GROUP BY
    CombinedValue
HAVING
    COUNT(*) = 1

If not unique per table

SELECT
    CombinedValue
FROM
    (
    SELECT DISTINCT t1 AS CombinedValue FROM t1
    UNION ALL
    SELECT DISTINCT t2 FROM t2
    ) foo
GROUP BY
    CombinedValue
HAVING
    COUNT(*) = 1

Upvotes: 0

Guffa
Guffa

Reputation: 700332

This will get you each item from t1 that is not present in t2, and each item in t2 that is not present in t1:

select t1.id from t1
left join t2 on t2.id = t1.id
where t2.id is null

union all

select t2.id from t2
left join t1 on t1.id = t2.id
where t1.id is null

(I have assumed that the field name in each table is named id just for the sake of being able to write a query against the tables.)

Another way would be:

select coalesce(t1.id, t2.id)
from t1
full outer join t2 on t2.id = t1.id
where t1.id is null or t2.id is null

Upvotes: 5

Gnanam R
Gnanam R

Reputation: 407

you can use Joins in MySql to proceed and to obtain result.

this will help you http://www.techrepublic.com/article/sql-basics-query-multiple-tables/1050307

Upvotes: 0

Related Questions