How to use distinct keyword on two columns in oracle sql?

Question

I used distinct keyword on one column it did work very well but when I add the second column in select query it doesn't work for me as both columns have duplicate values. So I want to not show me the duplicate values in both columns. Is there any proper select query for that.

The sample data is:
For Col001:

555
555
7878
7878
89.

Col002:

43
43
56
56
56
67
67
67
79
79
79.

I want these data in this format: Col001:

555
7878
89.

Col002:

43
56
67
79

I tried the following query:

Select distinct col001, col002 from tbl1

Answer 1

DISTINCT works across the entire row considering all values in the row and will remove duplicate values where the entire row is duplicated.

For example, given the sample data:

CREATE TABLE table_name (col001, col002) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 1, 2 FROM DUAL UNION ALL
SELECT 1, 3 FROM DUAL UNION ALL
SELECT 2, 1 FROM DUAL UNION ALL
SELECT 2, 2 FROM DUAL UNION ALL
--
SELECT 1, 2 FROM DUAL UNION ALL -- These are duplicates
SELECT 2, 2 FROM DUAL;

Then:

SELECT DISTINCT
       col001,
       col002
FROM   table_name

Outputs:

COL001	COL002
1	1
1	2
1	3
2	1
2	2

And the duplicates have been removed.

---

If you want to only display distinct values for each column then you need to consider each column separately and can use something like:

SELECT c1.col001,
       c2.col002
FROM   ( SELECT DISTINCT
                col001,
                DENSE_RANK() OVER (ORDER BY col001) AS rnk
         FROM table_name
       ) c1
       FULL OUTER JOIN
       ( SELECT DISTINCT
                col002,
                DENSE_RANK() OVER (ORDER BY col002) AS rnk
         FROM table_name
       ) c2
       ON (c1.rnk = c2.rnk)

Which outputs:

COL001	COL002
1	1
2	2
null	3

db<>fiddle here

Answer 2

Use a set operator. UNION will give you the set of unique values from two subqueries.

select col001 as unq_col_val 
from your_table
union
select col002 
from your_table;

This presumes you're not fussed whether the value comes from COL001 or COL002. If you are fussed, this variant preserves that information:

select  'COL001' as source_col
       ,col001 as unq_col_val 
from your_table
union
select 'COL002' as source_col
       ,col002 
from your_table;

Note that this result set will contain more rows if the same value exists in both columns.