The SELECT DISTINCT clause in SQL is used to select distinct (unique) rows from a table. This can be useful when you want to eliminate duplicate rows from a table, or when you need to count the number of unique values in a column.
In this article we will show you how to use the SELECT DISTINCT clause in SQL and provide some examples.
Read also: SQL Interview Questions
SELECT DISTINCT Syntax:
SELECT DISTINCT column_name1, column_name2, column_nameN FROM table_name;
SELECT DISTINCT * FROM table_name;
SELECT DISTINCT ROW_NUMBER () OVER (ORDER BY column1) AS “Row Number” FROM table_name;
The SELECT DISTINCT clause can be used in a number of different ways, as shown in the syntax examples above. In most cases, you will want to use the SELECT DISTINCT clause together with the WHERE clause to filter out specific rows from a table. For example, the following query will select the distinct (unique) values from the “Products” table:
SELECT DISTINCT ProductID FROM Products;
Now let’s take a look at an example that uses the SELECT DISTINCT clause with the ORDER BY clause.
The following query will select the distinct (unique) rows from the “Products” table, and then order them by the “ProductID” column:
- SELECT DISTINCT ROW_NUMBER () OVER (ORDER BY ProductID) AS “Row Number” FROM Products;
- You can also use the SELECT DISTINCT clause with the GROUP BY clause to group data by unique values. For example, the following query will return a list of products and the number of units sold for each product:
- SELECT DISTINCT ProductName, COUNT (*) as “Units Sold” FROM Products GROUP BY ProductName;
As you can see, the SELECT DISTINCT clause can be a useful tool for eliminating duplicate data and obtaining unique values from a table. In the next section we will show you how to use the SELECT DISTINCT clause to count the number of unique values in a column.
SELECT DISTINCT to Count the Number of Unique Values in a Column:
- The SELECT DISTINCT clause can also be used to count the number of unique values in a column. For example, the following query will return the number of distinct (unique) email addresses in the “Employees” table:
- SELECT COUNT (DISTINCT Email) as “Number of Email Addresses” FROM Employees;
- As you can see, the SELECT DISTINCT clause can be used to perform a variety of tasks, such as selecting distinct (unique) rows, counting the number of unique values in a column, and ordering data by unique values. In the next section we will show you some more examples of how to use the SELECT DISTINCT clause.
- The SELECT DISTINCT clause is used in SQL to select the unique (distinct) rows from a table. The SELECT DISTINCT clause can be used with the asterisk (*) wildcard character to select all of the distinct rows from a table.
- The SELECT DISTINCT clause is often used with the GROUP BY clause to group the data by column. The results of the SELECT DISTINCT clause can then be used with the HAVING clause to filter the data.
- The following example shows how to use the SELECT DISTINCT clause to select the unique (distinct) customers from the Customers table:
- SELECT DISTINCT Customer ID FROM Customers;
- The following example shows how to use the SELECT DISTINCT clause to select the unique (distinct) states from the States table:
- SELECT DISTINCT State FROM States;
- The following example shows how to use the SELECT DISTINCT clause to select the unique (distinct) products from the Products table:
- SELECT DISTINCT Product Name FROM Products;
- The following example shows how to use the SELECT DISTINCT clause to select the unique (distinct) orders from the Orders table:
- SELECT DISTINCT OrderID FROM Orders;
- The following example shows how to use the SELECT DISTINCT clause to select the unique (distinct) customers and products from the Customers and Products tables:
- SELECT DISTINCT CustomerID, ProductName FROM Customers, Products;
- The following example shows how to use the SELECT DISTINCT clause to select the unique (distinct) states and products from the States and Products tables:
- SELECT DISTINCT State, Product Name FROM States, Products;
- The following example shows how to use the SELECT DISTINCT clause to select the unique (distinct) customers, products, and orders from the Customers, Products, and Orders tables:
- SELECT DISTINCT Customer ID, Product Name, Order ID FROM Customers, Products, Orders;
Conclusion:
The SELECT DISTINCT clause is used in SQL to select the unique (distinct) rows from a table. The SELECT DISTINCT clause can be used with the asterisk (*) wildcard character to select all of the distinct rows from a table. The SELECT DISTINCT clause is often used with the GROUP BY clause to group the data by column.
