In SQL, the SELECT DISTINCT statement is used to retrieve unique values from a specified column or set of columns in a table. This statement returns a single instance of each distinct value in the column, eliminating any duplicate values.
The syntax for the SELECT DISTINCT statement is straightforward. Simply use the SELECT keyword followed by the DISTINCT keyword, and then specify the column or columns from which you want to retrieve distinct values. For example, to retrieve a list of unique values from a column called "city" in a table called "customers", you would use the following SQL statement:
SELECT DISTINCT city FROM customers;
The SELECT DISTINCT statement is useful in a variety of scenarios. For example, you may want to retrieve a list of all unique products in an inventory database, or you may want to find all distinct customer names in a sales database. The SELECT DISTINCT statement can also be used in conjunction with other SQL statements, such as WHERE clauses and JOIN statements, to further refine the results.
It's important to note that the SELECT DISTINCT statement can have an impact on query performance, particularly on large tables with many duplicate values. Retrieving unique values requires additional processing and memory overhead, which can slow down the query. Therefore, it's important to use the SELECT DISTINCT statement judiciously and only when necessary.
Another important consideration when using the SELECT DISTINCT statement is the order in which columns are specified. When retrieving unique values from multiple columns, the DISTINCT keyword applies to all columns specified in the SELECT statement. For example, if you retrieve unique values from two columns, the query will only return rows where the combination of values in those two columns is unique.
Read More:- SQL SELECT
In summary, the SELECT DISTINCT statement is a powerful tool for retrieving unique values from a table. It can be used to quickly and easily generate lists of distinct values for analysis or reporting purposes. However, it should be used with caution on large tables and with careful consideration of the columns being retrieved.
The SELECT DISTINCT statement is used to return only distinct (different) values.
Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values
SELECT DISTINCT Syntax
Demo Database
A demo database is a sample database that is designed to showcase the capabilities of a particular database management system (DBMS) or to serve as a template for developing a new database. It typically contains a set of tables, relationships between those tables, and sample data that can be used to demonstrate the functionality of the DBMS or to test applications that interact with the database.
The structure and content of a demo database will vary depending on the purpose for which it is designed. For example, a demo database for a human resources application might contain tables for employees, departments, and job titles, along with sample data for each of those tables. A demo database for an e-commerce site might contain tables for products, orders, and customers, along with sample data for each of those tables.
One popular example of a demo database is the Northwind database, which was developed by Microsoft to demonstrate the capabilities of their Access and SQL Server DBMS. The Northwind database contains tables for customers, orders, products, and suppliers, along with relationships between those tables and sample data that can be used to run queries and generate reports.
Another example of a demo database is the Sakila database, which was developed by MySQL to showcase their database management system. The Sakila database contains tables for customers, rentals, and inventory, along with relationships between those tables and sample data that can be used to demonstrate the functionality of the MySQL DBMS.
Demo databases can be useful for a variety of purposes, including training, testing, and prototyping. They can help developers and users to quickly understand the structure and functionality of a database, and to experiment with different queries and data models. They can also be a valuable resource for learning SQL and database design, as users can practice writing queries and designing tables in a safe, controlled environment.
In conclusion, demo databases are a valuable resource for anyone who is learning about database management systems or who needs to test applications that interact with databases. They provide a structured and realistic environment for experimenting with data models and queries, and can be used for a variety of purposes, including training, testing, and prototyping.
Below is a selection from the "Customers" table in the Northwind sample database:
SELECT Example Without DISTINCT
The SELECT statement is used to retrieve data from one or more tables in a database. While the SELECT DISTINCT statement is used to retrieve unique values from a specific column or set of columns, the regular SELECT statement returns all values, including duplicates.
To illustrate the difference between SELECT and SELECT DISTINCT, let's use the example of a table called "orders" that contains information about customer orders:
SELECT customer_name FROM orders;
This statement retrieves all of the customer names from the "orders" table, including any duplicates. For example, if there are two orders from a customer named "John Smith", the SELECT statement will return "John Smith" twice.
To eliminate the duplicate values, we could use the SELECT DISTINCT statement:
SELECT DISTINCT customer_name FROM orders;
This statement retrieves only the unique customer names from the "orders" table, eliminating any duplicate values.
However, there are situations where we may want to include duplicate values in our query results. For example, we may want to count the number of orders for each customer, including any duplicate orders. In this case, we could use the regular SELECT statement and the COUNT() function:
SELECT customer_name, COUNT(order_id) AS order_count
FROM orders
GROUP BY customer_name;
This statement retrieves the customer names and the number of orders for each customer from the "orders" table, including any duplicate orders. The GROUP BY clause is used to group the results by customer name and the COUNT() function is used to count the number of orders for each group.
In conclusion, the regular SELECT statement retrieves all values, including duplicates, from a specific column or set of columns in a table. It can be useful in situations where we need to include duplicate values in our query results or when we want to use aggregate functions like COUNT(), SUM(), or AVG(). The SELECT DISTINCT statement, on the other hand, retrieves only unique values from a specific column or set of columns and is useful in situations where we need to eliminate duplicate values.
The following SQL statement selects all (including the duplicates) values from the "Country" column in the "Customers" table:
Example
Now, let us use the SELECT DISTINCT statement and see the result.
SELECT DISTINCT Examples
The following SQL statement selects only the DISTINCT values from the "Country" column in the "Customers" table:
The following SQL statement lists the number of different (distinct) customer countries:
Note: The example above will not work in Firefox! Because COUNT(DISTINCT column_name) is not supported in Microsoft Access databases. Firefox is using Microsoft Access in our examples.
Here is the workaround for MS Access:
