Select distinct values from multiple columns in same table

Question

I am trying to construct a single SQL statement that returns unique, non-null values from multiple columns all located in the same table.

 SELECT distinct tbl_data.code_1 FROM tbl_data WHERE tbl_data.code_1 is not null UNION SELECT tbl_data.code_2 FROM tbl_data WHERE tbl_data.code_2 is not null;

For example, tbl_data is as follows:

 id code_1 code_2 --- -------- ---------- 1 AB BC 2 BC 3 DE EF 4 BC

For the above table, the SQL query should return all unique non-null values from the two columns, namely: AB, BC, DE, EF.

I'm fairly new to SQL. My statement above works, but is there a cleaner way to write this SQL statement, since the columns are from the same table?

That table structure give me a feeling your DB isn't normalized... — gdoron
– gdoron, Commented Jul 2, 2012 at 23:39
You don't need the distinct in the first query - union will do that for you. — Blorgbeard
– Blorgbeard, Commented Jul 2, 2012 at 23:40
@gdoron: The codes correspond to various designations, which may indeed be repeated, i.e. a particular record may have BC and BC for codes 1 and 2. The designation of code 1 versus 2 is also significant. There is a third table look-up table for the various codes. Not the best, but that's what I'm dealing with. — regulus
– regulus, Commented Jul 3, 2012 at 0:08
@regulus I updated my answer with the execution plan for your query so you can compare. — Iain Samuel McLean Elder
– Iain Samuel McLean Elder, Commented Jul 7, 2012 at 21:57

Dexygen · Accepted Answer · 2018-05-24 12:51:04Z

It's better to include code in your question, rather than ambiguous text data, so that we are all working with the same data. Here is the sample schema and data I have assumed:

CREATE TABLE tbl_data ( id INT NOT NULL, code_1 CHAR(2), code_2 CHAR(2) ); INSERT INTO tbl_data ( id, code_1, code_2 ) VALUES (1, 'AB', 'BC'), (2, 'BC', NULL), (3, 'DE', 'EF'), (4, NULL, 'BC');

As Blorgbeard commented, the DISTINCT clause in your solution is unnecessary because the UNION operator eliminates duplicate rows. There is a UNION ALL operator that does not elimiate duplicates, but it is not appropriate here.

Rewriting your query without the DISTINCT clause is a fine solution to this problem:

SELECT code_1 FROM tbl_data WHERE code_1 IS NOT NULL UNION SELECT code_2 FROM tbl_data WHERE code_2 IS NOT NULL;

It doesn't matter that the two columns are in the same table. The solution would be the same even if the columns were in different tables.

If you don't like the redundancy of specifying the same filter clause twice, you can encapsulate the union query in a virtual table before filtering that:

SELECT code FROM ( SELECT code_1 FROM tbl_data UNION SELECT code_2 FROM tbl_data ) AS DistinctCodes (code) WHERE code IS NOT NULL;

I find the syntax of the second more ugly, but it is logically neater. But which one performs better?

I created a sqlfiddle that demonstrates that the query optimizer of SQL Server 2005 produces the same execution plan for the two different queries:

The query optimizer produces this execution plan for both queries: two table scans, a concatenation, a distinct sort, and a select.

If SQL Server generates the same execution plan for two queries, then they are practically as well as logically equivalent.

Compare the above to the execution plan for the query in your question:

The DISTINCT clause makes SQL Server 2005 perform a redundant sort operation.

The DISTINCT clause makes SQL Server 2005 perform a redundant sort operation, because the query optimizer does not know that any duplicates filtered out by the DISTINCT in the first query would be filtered out by the UNION later anyway.

This query is logically equivalent to the other two, but the redundant operation makes it less efficient. On a large data set, I would expect your query to take longer to return a result set than the two here. Don't take my word for it; experiment in your own environment to be sure!

One of the Best Answers I think I have ever read on SO. Excellent detail and examples, and test results.

John Woo · Accepted Answer · 2012-07-03 00:09:26Z

try something like SubQuery:

SELECT derivedtable.NewColumn FROM ( SELECT code_1 as NewColumn FROM tbl_data UNION SELECT code_2 as NewColumn FROM tbl_data ) derivedtable WHERE derivedtable.NewColumn IS NOT NULL

The UNION already returns DISTINCT values from the combined query.

Giacomo1968 · Accepted Answer · 2020-09-01 15:27:01Z

Union is applied wherever the row data required is similar in terms of type, values etc. It doesnt matter you have column in the same table or the other to retrieve from as the results would remain the same ( in one of the above answers already mentioned though).

As you didn't wanted duplicates theres no point using UNION ALL and use of distinct is simply unnecessary as union gives distinct data

Can create a view would be best choice as view is a virtual representation of the table. Modifications could be then done neatly on that view created

Create VIEW getData AS ( SELECT distinct tbl_data.code_1 FROM tbl_data WHERE tbl_data.code_1 is not null UNION SELECT tbl_data.code_2 FROM tbl_data WHERE tbl_data.code_2 is not null );

Giacomo1968 · Accepted Answer · 2020-09-01 15:27:36Z

Try this if you have more than two Columns:

CREATE TABLE #temptable (Name1 VARCHAR(25),Name2 VARCHAR(25)) INSERT INTO #temptable(Name1, Name2) VALUES('JON', 'Harry'), ('JON', 'JON'), ('Sam','harry') SELECT t.Name1+','+t.Name2 Names INTO #t FROM #temptable AS tSELECT DISTINCT ss.value FROM #t AS t CROSS APPLY STRING_SPLIT(T.Names,',') AS ss

Collectives™ on Stack Overflow

Select distinct values from multiple columns in same table

4 Answers 4

1 Comment

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Linked

Related