how to select distinct value from multiple tables

Question

I need to get distinct values from 3 tables.

When I perform this code:

select DISTINCT(city) from a,b,c

I get an error which says that my column 'city' is ambiguous.

Also I have tried this:

select DISTINCT(city) from a NATURAL JOIN b NATURAL JOIN c

With this code I receive nothing from my tables.

Let me show you on the example of what I am trying to do:

TABLE A               TABLE B                TABLE C
id  |  city           id  |  city            id  | city
1   |  Krakow         1   |  Paris           1   | Paris
2   |  Paris          2   |  London          2   | Krakow
3   |  Paris          3   |  Oslo
4   |  Rome

And I need to get result like this

RESULTS
 city
 ----
 Krakow
 Paris
 Rome
 London
 Oslo

Order of the cities is not important to me I just need to have them all, and there should be only one representation of each city.

Any idea? I was thinking to use id's in the JOIN but there are not connected so I can't use that.

score 74 · Accepted Answer · edited May 01 '18 at 19:31

74

The UNION keyword will return unique records on the result list. When specifying ALL (UNION ALL) will keep duplicates on the result set, which the OP don't want.

SELECT city FROM tableA
UNION
SELECT city FROM tableB
UNION
SELECT city FROM tableC

SQLFiddle Demo

RESULT

╔════════╗
║  CITY  ║
╠════════╣
║ Krakow ║
║ Paris  ║
║ Rome   ║
║ London ║
║ Oslo   ║
╚════════╝

edited May 01 '18 at 19:31

oldboy

5,729
6
38
86

answered Mar 09 '13 at 12:41

John Woo

258,903
69
498
492

Exactly what I need. This UNION is like DISTINCT? can you explain it a bit? – user123_456 Mar 09 '13 at 12:44
I don't know why this answer has been downvoted. hehe As far as I know, I understood the question. `:)` Let's hear from the downvoter's point of view. – John Woo Mar 09 '13 at 12:45
@user123_456 see my updated answer. also demo here [SQLFiddle Demo](http://www.sqlfiddle.com/#!2/442ef/8) – John Woo Mar 09 '13 at 12:54
okay I have only one question if you don't mind. If I have a additional column in each of the tables for example `spa_column` where do I need to put where clause so it affects on all tables. – user123_456 Mar 09 '13 at 12:58
3

wrap the whole query in a subquery, [SQLFiddle Demo](http://www.sqlfiddle.com/#!2/442ef/10) – John Woo Mar 09 '13 at 13:01
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/25881/discussion-between-user123-456-and-jw) – user123_456 Mar 09 '13 at 13:08

score 5 · Answer 2 · answered Mar 09 '13 at 12:44

5

SELECT city FROM A
UNION DISTINCT
SELECT city FROM B
UNION DISTINCT
SELECT city FROM C

answered Mar 09 '13 at 12:44

James C

14,047
1
34
43

2

this also produce correct answer but why do you need to add `DISTINCT` keyword with `UNION` will always return unique records unless `ALL` is specified? [SQL Fiddle Demo](http://www.sqlfiddle.com/#!2/442ef/7) – John Woo Mar 09 '13 at 12:48
1

You're quite right. "UNION" defaults to "UNION DISTINCT" (rather than "UNION ALL") – James C Mar 10 '13 at 11:52

how to select distinct value from multiple tables

2 Answers2

Linked