Understanding the SELECT Statement in SQL

The SQL SELECT statement, a cornerstone of querying databases, boasts several powerful features that enable tailored data retrieval. Let's delve into some advanced aspects to enhance your understanding.

Syntax Overview

SELECT * FROM table_name;

In the syntax specify the list of columns from the table in the SELECT statement.

The wild card(*) is used to select all columns in the table.

It's important to note that using * to select all columns may lead to longer execution times, especially if the table contains numerous columns. Be mindful of this potential delay when constructing your queries

FROM specifies the table name. This is usually first executed when one runs a query.

As earlier mentioned SQL is not case sensitive so select/Select would also run with no error. But using it in upper case makes it a bit more easier to read.

Customizing Your Output:

SELECT employee_id, 
first_name,
last_name
FROM employees;

The above code is used to select three columns from the employees table. You can choose as many columns as needed.

Select Unique values

At times, extracting unique values is paramount. Achieving this is straightforward using the DISTINCT keyword:

SELECT DISTINCT first_name
FROM employees

Remember, the DISTINCT keyword affects all columns within the SELECT statement, ensuring complete uniqueness.

Aliasing

SQL allows aliasing to simplify output readability and handle complex queries:

SELECT  department_id, AVG(salary) AS Avg_salary
FROM employees
GROUP BY department_id

Aliases prove especially handy for condensing long column names or clarifying aggregated data labels.

Using AS is optional. The following is equivalent to the previous example:

SELECT  department_id, AVG(salary) Avg_salary
FROM employees
GROUP BY department_id

Limit

It is useful when you want to see just a few rows. Usually faster loading. The LIMIT command is usually the very last part of a query.

Further Reading

Udacity SQL course

Different SQL flavors

DataCamp introduction to SQL

Practice writing your queries online