{"id":12549,"date":"2018-04-10T06:47:19","date_gmt":"2018-04-10T06:47:19","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=12549"},"modified":"2021-05-09T13:10:59","modified_gmt":"2021-05-09T07:40:59","slug":"impala-distinct-operator","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/impala-distinct-operator\/","title":{"rendered":"Impala DISTINCT Operator – Syntax & Example"},"content":{"rendered":"

In our previous tutorial, we studied\u00a0the Impala Offset Clause<\/strong>. Now, we are going to study\u00a0Impala DISTINCT Operator. Although, there is much more to learn about using Impala DISTINCT Operator.
\nSo, let’s start\u00a0Impala DISTINCT Operator.<\/p>\n

<\/h2>\n

What is Impala DISTINCT Operator?<\/span><\/h2>\n

While we want to filter the results or \u00a0to remove duplicates, we use The DISTINCT operator in a SELECT statement:<\/span>
\n<\/span>
\n<\/span>— Returns the unique values from one column.<\/b>
\n<\/b>— NULL is included in the set of values if any rows have a NULL in this column.<\/b>
\n<\/b>select distinct c_birth_country from Employees;<\/b>
\n<\/b>— Returns the unique combinations of values from multiple columns.<\/b>
\n<\/b>select distinct c_salutation, c_last_name from Employees;<\/b><\/b><\/p>\n

Moreover, to find how many different values a column contains, we can use DISTINCT in combination with an aggregation function.Typically COUNT():<\/span>
\n<\/span>
\n<\/span>— Counts the unique values from one column.<\/b>
\n<\/b>— NULL is not included as a distinct value in the count.<\/b><\/b><\/p>\n

select count(distinct c_birth_country) from Employees;<\/pre>\n
— Counts the unique combinations of values from multiple columns.<\/b>
\n<\/b><\/p>\n
select count(distinct c_salutation, c_last_name) from Employees;<\/pre>\n
However, make sure that using DISTINCT in more than one aggregation function in the same query is not supported by Impala SQL. To understand more, we could not have a single query with both COUNT(DISTINCT c_first_name) and COUNT(DISTINCT c_last_name) in the SELECT list.<\/span>
\n<\/span><\/p>\n
Syntax for Distinct Operator in Impala<\/span><\/h2>\n
So, the syntax for using Impala DISTINCT Operator is-
\n<\/span><\/p>\n
select distinct columns\u2026 from table_name;<\/pre>\n
Zero-length strings<\/span><\/h2>\n
Impala considers zero-length strings (“”), NULL for purposes of clauses such as DISTINCT and GROUP BY. Also, space to all is different values.<\/span>
\n<\/span>
\n<\/span>Note:<\/b>
\n<\/span>As we discussed earlier, Impala only allows a single COUNT(DISTINCT columns) expression in each query.<\/span>
\n<\/span>
\n<\/span>Moreover, we can produce an estimate of the distinct values for a column by specifying NDV(column), if we do not need precise accuracy.<\/span> Here, a query can contain multiple instances of NDV(column).<\/p>\n
Also, can enable the APPX_COUNT_DISTINCT query option, to make Impala automatically rewrite COUNT(DISTINCT) expressions to NDV().
\n<\/span>
\n<\/span>Further, we can use the following technique for queries involving a single table in order to produce the same result as multiple COUNT(DISTINCT) expressions:<\/span>
\n<\/span>
\n<\/span><\/p>\n
select v1.c1 result1, v2.c1 result2 from\n (select count(distinct col1) as c1 from t1) v1\n   cross join\n (select count(distinct col2) as c1 from t1) v2;<\/pre>\n
By the way, prefer to use the NDV() technique while practical because CROSS JOIN is an expensive operation.<\/span>
\n<\/span>
\n<\/b>Note:<\/b>
\n<\/span>Make sure, Impala does not do any ordering of DISTINCT values in contrast with some database systems.<\/span>\u00a0Basically, that always return DISTINCT values in sorted order. So, to avoid that include an ORDER BY clause always, if we need the values in alphabetical or numeric sorted order.<\/p>\n
Impala Distinct Operator – Example<\/span><\/h2>\n
Let’s discuss Impala DISTINCT Operator Example,
\n<\/span>
\nSo, let us suppose we have a table named <\/span>Employees <\/b>in the database my_db.<\/span> Its contents are \u2212<\/p>\n
[quickstart.cloudera:21000] > select distinct name, age, address from Employees;\nQuery: select distinct  name, age, address from Employees\n<\/pre>\n\n\n\n\n\n\n\n\n\n\n\n
Name<\/b><\/td>\nAge<\/b><\/td>\nAddress<\/b><\/td>\n<\/tr>\n
Shubham<\/b><\/td>\n32<\/b><\/td>\nDelhi<\/b><\/td>\n<\/tr>\n
Shubham<\/b><\/td>\n32<\/b><\/td>\nDelhi<\/b><\/td>\n<\/tr>\n
Monika<\/b><\/td>\n25<\/b><\/td>\nMumbai<\/b><\/td>\n<\/tr>\n
Kajal<\/b><\/td>\n27<\/b><\/td>\nAlirajpur<\/b><\/td>\n<\/tr>\n
Revti<\/b><\/td>\n25<\/b><\/td>\nIndore<\/b><\/td>\n<\/tr>\n
Revti<\/b><\/td>\n25<\/b><\/td>\nIndore<\/b><\/td>\n<\/tr>\n
Shreyash<\/b><\/td>\n23<\/b><\/td>\nPune<\/b><\/td>\n<\/tr>\n
Mehul<\/b><\/td>\n22<\/b><\/td>\nHyderabad<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
However, we can see that the name of the <\/span>Employees<\/b> Shubham and Revti entered twice. So, \u00a0now we can select the unique values using the Impala Distinct Operator.<\/span>
\n<\/span>
\n<\/span><\/p>\n
[quickstart.cloudera:21000] > select distinct name, age, address from Employees;\nOn executing, the above query gives the following output.\nQuery: select distinct  name from Employees<\/pre>\n\n\n\n\n\n\n\n\n\n
Name<\/b><\/td>\nAge<\/b><\/td>\nAddress<\/b><\/td>\n<\/tr>\n
Shubham<\/b><\/td>\n32<\/b><\/td>\nDelhi<\/b><\/td>\n<\/tr>\n
Monika<\/b><\/td>\n25<\/b><\/td>\nMumbai<\/b><\/td>\n<\/tr>\n
Kajal<\/b><\/td>\n27<\/b><\/td>\nAlirajpur<\/b><\/td>\n<\/tr>\n
Revti<\/b><\/td>\n25<\/b><\/td>\nIndore<\/b><\/td>\n<\/tr>\n
Shreyash<\/b><\/td>\n23<\/b><\/td>\nPune<\/b><\/td>\n<\/tr>\n
Mehul<\/b><\/td>\n22<\/b><\/td>\nHyderabad<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Fetched 9 row(s) in 1.46s<\/b>
\nSo, this was all about\u00a0Impala DISTINCT Operator. Hope you like our explanation.<\/p>\n
Conclusion<\/span><\/h2>\n
Hence, in this article, we have seen how to use Impala DISTINCT Operator properly. However, if you want to ask any doubt, feel free to ask in the comment section. we will definitely respond.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
In our previous tutorial, we studied\u00a0the Impala Offset Clause. Now, we are going to study\u00a0Impala DISTINCT Operator. Although, there is much more to learn about using Impala DISTINCT Operator. So, let’s start\u00a0Impala DISTINCT Operator....<\/p>\n","protected":false},"author":6,"featured_media":54318,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[6510,6511,16631],"class_list":["post-12549","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-impala","tag-impala-distinct-operator","tag-impala-distinct-operator-with-example","tag-zero-length-strings-in-impala"],"yoast_head":"\nImpala DISTINCT Operator - Syntax & Example - DataFlair<\/title>\n<meta name=\"description\" content=\"Impala DISTINCT Operator, Syntax of DISTINCT operators, Example of Impala DISTINCT operators, Zero-length strings, What is Distinct operators in Impala\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/impala-distinct-operator\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Impala DISTINCT Operator - Syntax & Example - DataFlair\" \/>\n<meta property=\"og:description\" content=\"Impala DISTINCT Operator, Syntax of DISTINCT operators, Example of Impala DISTINCT operators, Zero-length strings, What is Distinct operators in Impala\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/impala-distinct-operator\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-04-10T06:47:19+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-05-09T07:40:59+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/04\/Impala-distinct-operators.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Impala DISTINCT Operator - Syntax & Example - DataFlair","description":"Impala DISTINCT Operator, Syntax of DISTINCT operators, Example of Impala DISTINCT operators, Zero-length strings, What is Distinct operators in Impala","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/impala-distinct-operator\/","og_locale":"en_US","og_type":"article","og_title":"Impala DISTINCT Operator - Syntax & Example - DataFlair","og_description":"Impala DISTINCT Operator, Syntax of DISTINCT operators, Example of Impala DISTINCT operators, Zero-length strings, What is Distinct operators in Impala","og_url":"https:\/\/data-flair.training\/blogs\/impala-distinct-operator\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-04-10T06:47:19+00:00","article_modified_time":"2021-05-09T07:40:59+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/04\/Impala-distinct-operators.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/impala-distinct-operator\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/impala-distinct-operator\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"Impala DISTINCT Operator – Syntax & Example","datePublished":"2018-04-10T06:47:19+00:00","dateModified":"2021-05-09T07:40:59+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/impala-distinct-operator\/"},"wordCount":568,"commentCount":0,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/impala-distinct-operator\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/04\/Impala-distinct-operators.jpg","keywords":["Impala DISTINCT Operator","Impala DISTINCT Operator with Example","Zero-Length Strings in Impala"],"articleSection":["Impala Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/impala-distinct-operator\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/impala-distinct-operator\/","url":"https:\/\/data-flair.training\/blogs\/impala-distinct-operator\/","name":"Impala DISTINCT Operator - Syntax & Example - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/impala-distinct-operator\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/impala-distinct-operator\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/04\/Impala-distinct-operators.jpg","datePublished":"2018-04-10T06:47:19+00:00","dateModified":"2021-05-09T07:40:59+00:00","description":"Impala DISTINCT Operator, Syntax of DISTINCT operators, Example of Impala DISTINCT operators, Zero-length strings, What is Distinct operators in Impala","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/impala-distinct-operator\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/impala-distinct-operator\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/impala-distinct-operator\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/04\/Impala-distinct-operators.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/04\/Impala-distinct-operators.jpg","width":1200,"height":628,"caption":"Distinct Operator in Impala"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/impala-distinct-operator\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"Impala Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/impala\/"},{"@type":"ListItem","position":3,"name":"Impala DISTINCT Operator – Syntax & Example"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/12549","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=12549"}],"version-history":[{"count":1,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/12549\/revisions"}],"predecessor-version":[{"id":94035,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/12549\/revisions\/94035"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/54318"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=12549"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=12549"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=12549"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}