SQL SELECT: Syntax, Use Cases, and Implementation

Introduction

SQL is an essential tool to know for anybody working with data. It comes with a range of functions that make querying in databases, a breeze. In this article, we are going to see one of the fundamentals of SQL, the SELECT statement. This function helps in retrieving data from one or more tables. We can specify what data needs to be retrieved and how they must be shown as well. Today, we will be covering the basic syntax of SELECT statements in SQL and some examples to illustrate its use cases. So let’s dive in!

If you’re just starting out to explore SQL, here’s a beginner’s guide to help you: SQL For Data Science: A Beginner Guide

Overview

• Understand what the SELECT function in SQL does.
• Learn the syntax of SELECT statements.
• Learn how to implement the SELECT function in various use cases.

What is SELECT in SQL?

SELECT command lets us tell the server (database/table) which data needs to be extracted, filtered, sorted, and also manipulated to our requirements. There are various ways to query and manipulate data. We should be aware of different clauses present in SELECT. We will be seeing some of those below.

Sample Data

Let’s understand the functioning of SELECT in SQL through a live example. For this, we first need to create a sample table and insert some records into it. Here’s how that’s done:

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

Now that we have created the table, we will now insert records into the employees table.

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('John', 'Doe', 'Sales', 60000),
('Jane', 'Smith', 'Marketing', 65000),
('Jim', 'Brown', 'Sales', 55000),
('Jake', 'White', 'IT', 70000),
('Jill', 'Black', 'IT', 72000),
('Janet', 'Green', 'HR', 50000),
('James', 'Blue', 'HR', 52000),
('Jack', 'Yellow', 'Marketing', 67000),
('Julia', 'Purple', 'Sales', 63000),
('Jerry', 'Orange', 'Marketing', 62000);

Now we are ready to test out our various SELECT statements.

SELECT Statements

Let’s Understand SELECT statements using the table created above.

Basic Syntax

SELECT column1, column2, ...
FROM table_name;

Here column1, column2, etc. are the specified columns which will be retrieved from the table. Let’s look into the example.

SELECT first_name, last_name
FROM employees;

SQL SELECT

We can see that all rows and only the specified columns are retrieved from the employees table.

Selecting All Columns

SELECT * 
FROM table_name;

Above code is the syntax to retrieve all the columns from a table. We use “*” wildcard to retrieve all the columns in the table. Let’s use the above syntax for our employee table.

SELECT *
FROM employees;

We can see that all the columns present the table along with all records of the employees table.

Using DISTINCT

SELECT DISTINCT column_name
FROM table_name;

DISTINCT keyword is used when we want to retrieve only the unique values of a column. This is useful when we want to discard duplicate elements in our table.

SELECT DISTINCT department
FROM employees;

We can retrieve various departments present in our table.

Filtering Data with WHERE

SELECT column_name1, column_name2
FROM table_name
WHERE condition;

WHERE clause filters our records based on some condition we specify. After filtering, we get the result set which contains all the records that meet the condition. Let’s use the above syntax for our employees table.

SELECT first_name, last_name
FROM employees
WHERE department = 'Sales';

Here we can see that our query retrieves only the records which are from the sales department.

Using Logical Operators

SELECT column_name
FROM table_name
WHERE condition1 AND condition2;

Here we are using a logical operator to use two conditions on our table to retrieve data from a table. Lets use this on our employees table.

SELECT first_name, last_name
FROM employees
WHERE department = 'Sales' AND salary > 50000;

Since all the employees in the sales department are getting a salary greater than 50000 we see all the records with sales as the department. I would ask you guys to implement this with other departments as well to get a good understanding.

Sorting Results with ORDER BY

SELECT column_name
FROM table_name
ORDER BY column_name_to_order DESC; -- or ASC for ascending order

Here we select columns to be displayed from the table. But we sort by column_name_to_order either in descending order or ascending order. We can use any column in the SELECT statement but can only sort by column_name_to_order in ORDER BY. We can also use multiple columns in ORDER BY. Implementing the above code to our employees table using the above syntax.

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

We can see that the records are sorting in descending order of salary.

Limiting Results with LIMIT

SELECT column_name
FROM table_name
LIMIT int_num;

The ‘LIMIT’ clause specifies the number of records to be returned in the result set. For example LIMIT being 5 will only return 5 records, even if there are more records in the table. If there are less than 5 records, satisfying the conditions LIMIT clause has no effect on the query.

SELECT first_name, last_name
FROM employees
LIMIT 5;

We can see that the above query limits the number of records to 5.

Aggregating Data with GROUP BY

SELECT column_name, aggregate function
FROM table_name
GROUP BY column_name;

The GROUP BY clause groups rows that have the same values into summary rows, like “finding the number of employees in each department”. We often use it with aggregate functions (COUNT, MAX, MIN, SUM, AVG).

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

This query counts the number of employees in each department.

Filtering Grouped Data with HAVING

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;

Here we are adding another clause called HAVING, to the filtered aggregated records. This is similar to using WHERE, but for groups.

We can see that IT and HR departments are missing as the number of records in those departments are not greater than 2.

Conclusion

The SELECT statement is a very powerful tool in SQL for querying data. Understanding its various clauses and options allows you to retrieve and manipulate data efficiently. It helps you in various tasks like filtering, sorting, or aggregating data. The SELECT statement provides the flexibility to perform these operations with ease. With the understanding of the SELECT statement and its associated clauses, you can unlock the full potential of SQL to manage and query your database effectively.

Learn More: SQL: A Full Fledged Guide from Basics to Advanced Level

Frequently Asked Questions