SQL Query to get a single column 'name' with distinct values from two other tables having same column 'name'

Question

There are two tables as cats and dogs with 'id' and 'name' for each pet in both tables. The schema can be understood from below:

TABLE 'dogs'
    id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR (50) NOT NULL
TABLE 'cats'
    id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR (50) NOT NULL

The question asks to 'Write a query that select all distinct pet.name'

display table or using sample code to make it easier for readers — Sate Wedos, Oct 15 '17 at 04:51
If dogs and cats are the only ones, you can just select distinct name from both and union them — information_interchange, Oct 15 '17 at 04:53
research the word "union" in sql eg https://www.w3schools.com/sql/sql_union.asp — Paul Maxwell, Oct 15 '17 at 05:00
@Used_By_Already Except that in general w3schools content is terrible. — philipxy, Oct 22 '17 at 16:14

operator-overlord · Answer 1 · 2018-09-18T19:38:01.107

5

SELECT name FROM dogs 
UNION 
SELECT name FROM cats

Using UNION removes the duplicate rows so using DISTINCT is not necessary here. If you want to display every pet name not only the distinct ones, you should use UNION ALL instead.

edited Sep 18 '18 at 19:38

answered Jun 05 '18 at 11:15

operator-overlord

51
1
4

Erfan Mohammadi · Answer 2 · 2018-05-26T04:18:48.110

1

CREATE TABLE Pets(
ID INTEGER NOT NULL PRIMARY KEY
,Name VARCHAR(50) NOT NULL);

SELECT DISTINCT Name FROM Pets

edited May 26 '18 at 04:18

answered Oct 15 '17 at 08:09

Erfan Mohammadi

424
3
7

Why not just `union` on exists tables? – Aleks Andreev Oct 15 '17 at 09:12

score 1 · Answer 3 · edited Mar 11 '19 at 12:53

1

Firstly create a temp table which will hold data from both the tables as mentioned below

create Temp table pet
as select name from dogs
union 
select name from cats;

Select the distinct names from the temp table

select distinct name from pet;

edited Mar 11 '19 at 12:53

Adriaan

17,741
7
42
75

answered Mar 11 '19 at 12:50

Rohit Hannurkar

55
1
6

Your named the key(word)s to solution (`UNION` and `select DISTINCT`), good!. You could explain them and please check if [temp table is created like this in MySQL](https://stackoverflow.com/questions/5859391/create-a-temporary-table-in-a-select-statement-without-a-separate-create-table). – hc_dev Mar 11 '19 at 13:06
@hc_dev, do you mean to say the difference between `TEMP` and `TEMPORARY` keyword, both are acceptable – Rohit Hannurkar Mar 11 '19 at 13:36
I did not try to use this *abbrevation*. But good you have written that both will work. Since the asking OP is obviously new to SQL and MySQL it would be good teaching to explain keywords (add links: e.g. to [MySQL docs](https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html), and avoid shortcut-keywords which could confuse when further researching SO. – hc_dev Mar 11 '19 at 13:49

score 0 · Answer 4 · edited Oct 15 '17 at 22:58

0

select distinct name from dogs
union
select distinct name from cats

edited Oct 15 '17 at 22:58

Bhanuchander Udhayakumar

1,581
1
12
30

answered Oct 15 '17 at 06:11

Ab Bennett

1,391
17
24

1

Union by itself will remove every duplicate. Using 'select distinct' is redundant. – Paul Maxwell Oct 22 '17 at 19:28
What you say is true but doing distinct either side can make it quicker if indexes on it, it will never make it worse – Ab Bennett Oct 25 '17 at 09:21
1

https://stackoverflow.com/questions/30142553/combining-select-distinct-with-union-distinct-in-mysql-any-effect – Ab Bennett Oct 25 '17 at 09:44

score -1 · Answer 5 · answered Mar 20 '18 at 17:38

-1

select name from (select name, "dog" as pet from cats union all select name, "cat" as pet from dogs) group by name order by name

answered Mar 20 '18 at 17:38

Marcel Minotelli

1

3

Welcome to Stack Overflow! Thank you for this code snippet, which might provide some limited short-term help. A proper explanation [would greatly improve](//meta.stackexchange.com/q/114762) its long-term value by showing *why* this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please [edit] your answer to add some explanation, including the assumptions you've made. – Toby Speight Mar 20 '18 at 18:26
@TobySpeight How nicely put your comment! Should be an _auto-expandable template_ on SO for `[exp-code]`. – hc_dev Mar 11 '19 at 13:57

SQL Query to get a single column 'name' with distinct values from two other tables having same column 'name'

5 Answers5