SQL SELECT Statement

The SQL SELECT statement is one of the most commonly used statements in SQL. It is used to retrieve data from one or more tables in a database.

The SELECT statement is used to select columns and rows from a table, and it is the most basic and important SQL statement.

This guide will cover the basics of the SELECT statement and provide examples to help you understand how it works.

Basic Syntax

The basic syntax of the SELECT statement is as follows:

SELECT column1, column2, ...
FROM table_name;

The SELECT keyword is used to specify the columns that you want to retrieve.
The FROM keyword is used to specify the table that you want to retrieve data from.

For example, to select all the columns from the employees table, you would use the following SQL statement:

SELECT *
FROM employees;

The * is a wildcard that selects all columns.

Selecting Specific Columns

You can also select specific columns from a table by listing them after the SELECT keyword.

For example, to select the first_name, last_name, and salary columns from the employees table, you would use the following SQL statement:

SELECT first_name, last_name, salary
FROM employees;

Filtering Results with the WHERE Clause

The WHERE clause is used to filter results based on specific conditions. You can use the WHERE clause to filter results based on a specific value in a column.

For example, to retrieve all employees with a salary greater than $50,000, you would use the following SQL statement:

SELECT *
FROM employees
WHERE salary > 50000;

You can also use multiple conditions in the WHERE clause.

For example, to retrieve all employees with a salary greater than $50,000 and who work in the Sales department, you would use the following SQL statement:

SELECT *
FROM employees
WHERE salary > 50000 AND department = 'Sales';

You can also use the OR operator to retrieve results that match either of the conditions.

For example, to retrieve all employees with a salary greater than $50,000 or who work in the Sales department, you would use the following SQL statement:

SELECT *
FROM employees
WHERE salary > 50000 OR department = 'Sales';

Sorting Results with the ORDER BY Clause

The ORDER BY clause is used to sort the results of a SELECT statement in ascending or descending order.

By default, the results are returned in ascending order. To sort the results in descending order, you can use the DESC keyword.

For example, to retrieve all employees sorted by salary in descending order, you would use the following SQL statement:

SELECT *
FROM employees
ORDER BY salary DESC;

Joining Multiple Tables

You can also use the SELECT statement to retrieve data from multiple tables. This is done using the JOIN clause.

The JOIN clause is used to combine rows from two or more tables based on a related column between them.

For example, you could use the following SQL statement to retrieve all employees and the departments they work in:

SELECT employees.*, departments.name
FROM employees
JOIN departments ON employees.department_id = departments.id;

SQL SELECT DISTINCT Statement

The SQL SELECT DISTINCT statement is used to return only distinct (unique) values in the result set of a SELECT query. This means that duplicate values are eliminated from the result set, and only unique values are returned. The DISTINCT keyword is used with the SELECT statement to specify that only distinct values should be returned.

For example, consider the following table named employees with columns first_name and last_name.

first_name | last_name
----------------------
John       | Smith
Mary       | Johnson
John       | Doe

If we want to select the first_name column and we want to remove the duplicate values, we can use the following query:

SELECT DISTINCT first_name FROM employees;

This query will return the following result:

first_name
----------
John
Mary

You can also use the DISTINCT keyword with multiple columns. In this case, the query will return unique combinations of values in the specified columns. For example, the following query will return the unique combinations of first_name and last_name:

SELECT DISTINCT first_name, last_name FROM employees;

This query will return the following result:

first_name | last_name
----------------------
John       | Smith
Mary       | Johnson
John       | Doe

It’s worth noting that the DISTINCT keyword applies to all columns in the SELECT statement, so if you want to select all columns while only returning distinct rows, you can use SELECT DISTINCT *.

It’s also important to note that the order of the columns in the SELECT statement doesn’t affect the result when using the DISTINCT keyword. So, SELECT DISTINCT first_name, last_name will return the same result as SELECT DISTINCT last_name, first_name.

In summary, the SQL SELECT DISTINCT statement is used to return only distinct (unique) values in the result set of a SELECT query. This can be useful when you want to remove duplicate values and only retrieve unique values from a table.

Takeaways

The SQL SELECT statement is a powerful tool for retrieving data from a database. It allows you to select specific columns, filter results with the WHERE clause, sort results with the ORDER BY clause, and retrieve data from multiple tables with the JOIN clause.

Understanding how to use the SELECT statement is an essential part of working with SQL and databases. With the knowledge of the basic syntax and the various clauses and operators, you can retrieve and analyze data in various ways.

The examples provided in this guide are just the beginning of what you can do with the SELECT statement, as you can combine these clauses to create more complex queries and retrieve the data you need for your analysis and decision making.