Querying and aggregation
SELECT, aliases and DISTINCT
Design a result set deliberately.
Understand the concept
SELECT chooses the columns or expressions in a result. AS creates a result heading, not a schema change. Unnecessary SELECT * couples an application contract to schema changes and retrieves unneeded data.
DISTINCT removes duplicate combinations across the entire selected column list, not just the first column. DISTINCT City differs from DISTINCT City, FullName. Hiding duplicate rows from a wrong JOIN with DISTINCT does not fix incorrect totals. Row order is not guaranteed without ORDER BY.
Use it to define API and report columns explicitly.
Assuming DISTINCT affects only the first column, or using it to hide a JOIN error.
One topic. Three levels of depth.
SELECT FullName AS CustomerName, City FROM lab.Customer;Columns are explicit; CustomerName is a result alias.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
List distinct combinations of customer names and cities.
Show the solution and explanation
SELECT DISTINCT FullName, City FROM lab.Customer ORDER BY FullName;All six names differ, so six rows remain even though cities repeat.