Selecting Columns and Records
This lesson covers the fundamental SQL syntax to specify exactly which columns you want to retrieve from a database table, and how to get only unique values.
Selecting Specific Columns
When querying a database, you often only need a subset of the available information. SQL's SELECT statement allows you to specify exactly which columns you wish to retrieve, rather than fetching everything. This precision improves query performance, especially with large tables, and makes your results easier to read and analyze.
first_name, city, and salary columns from the employees table.Retrieving All Columns with the Wildcard
For quick data exploration or when you genuinely need every piece of information, SQL provides a convenient wildcard character, the asterisk (). Using SELECT retrieves all columns from the specified table. While useful for initial debugging or understanding a table's structure, it is generally discouraged in production queries.
Fetching unnecessary data can significantly impact query performance and network bandwidth, especially with wide tables or large datasets. Explicitly naming columns also makes your queries more readable and resilient to schema changes, as adding new columns won't unexpectedly alter your result set.
SELECT generally discouraged in SQL queries?Filtering for Unique Values with DISTINCT
Database tables often contain duplicate values within a column. For instance, an employees table might list many employees from the same city. To retrieve only the unique values from one or more columns, you use the DISTINCT keyword. This keyword is placed directly after SELECT and before the column name(s).
When applied to a single column, DISTINCT filters the result set to show each unique value only once, effectively removing any redundant entries. This is useful for understanding the range of categories or attributes present in your data.
When you apply DISTINCT to multiple columns, it considers the combination of values across all specified columns. A row is considered unique only if the combination of values in all the DISTINCT columns has not appeared before. This means if first_name and last_name are selected, a row is unique if that specific first_name and last_name pair has not been seen.
This behavior is important to remember: DISTINCT does not find unique values within each column independently when multiple columns are listed. Instead, it ensures that each entire row (defined by the selected columns) in the result set is unique.
department_id values from the employees table. Then, modify it to find unique combinations of city and department_id.SELECT column1, column2retrieves only specified columns, improving performance and readability.SELECT is useful for quick exploration but avoid in production for efficiency and maintainability.
DISTINCTremoves duplicate rows based on the selected column(s), or the combination of columns if multiple are specified.Precisely selecting columns is the first step to efficient and targeted data retrieval in SQL.