How to Select Data in SQL

In the modern world, where information is becoming an increasingly valuable resource, databases (DBs) remain an integral part of any information system, and the ability to retrieve data from them with maximum efficiency becomes a decisive factor in successfully working with these systems. SQL (Structured Query Language) is a specialized programming language for managing records stored in relational databases. Within SQL, there are many operators and methods that allow developers to retrieve the required information from a DB.

This article is a practical guide for those who want to learn how to select data from an SQL table. In this guide, we will explore the syntax of the SELECT statement, learn how to filter data using WHERE, and examine how to aggregate data using GROUP BY and HAVING.

Basics of the SELECT Statement

SQL, being an incredibly flexible language for managing data, offers many tools for working with information stored in databases. One of the most important and widely used tools is the SELECT statement, which allows users to retrieve information from a DB. This statement allows us to select the specific columns from a table and apply various operations to the data.

The syntax of the SELECT statement is simple and easy to understand. It begins with the keyword SELECT, followed by a list of columns from which we will retrieve data and the table name from which we will extract the data. Here’s what it looks like in practice:

SELECT field1, field2
FROM data_table;

In this example, field1 and field2 are the specific columns we want to retrieve, and data_table is the name of the table from which we want to fetch the data.

You can use the SELECT statement in many different ways. For example, if the task is to select all columns from a particular table, we can use the * symbol, which serves as a wildcard for all columns:

SELECT * FROM StaffMembers;

This query will return all the data contained in the StaffMembers table.

In addition, we can use SELECT to retrieve only unique values from a specific column, excluding duplicate entries, which is especially useful when analyzing data:

SELECT DISTINCT DivisionID
FROM StaffMembers;

In this example, the query returns a list of unique DivisionID values from the StaffMembers table, removing all duplicate entries.

The SELECT statement also allows the use of various aggregation functions, such as COUNT, SUM, AVG, and others. These functions are key for performing aggregate operations that help analyze large volumes of data to obtain totals, averages, or other types of aggregate statistics. For example, we can use the COUNT function to count the number of rows in a table:

SELECT COUNT(StaffID)
FROM StaffMembers;

This query will return the total number of employees. Similarly, we can use other aggregation functions to calculate sums, averages, and other aggregate statistics for the data.

Another useful operator is ORDER BY, which orders the results of a query according to specific criteria. This operator allows us to sort data either in ascending (ASC) or descending (DESC) order. If we do not specify the sort order explicitly, ascending order will be used by default. Here's how it looks in practice:

SELECT *
FROM StaffMembers
ORDER BY Surname DESC;

In this example, the query results will be presented in sorted order, where the data will be sorted by employees' surnames in reverse alphabetical order, from the last name in the alphabet to the first.

The SELECT statement plays an important role in SQL as it determines which specific data will be included in the query results. It can be used in conjunction with other operators, so let’s move on to the next key SQL query component—the WHERE clause, which allows us to set specific conditions for data selection.

Using WHERE to Filter Data

The WHERE clause in SQL provides data filtering based on specified conditions, allowing you to retrieve, update, or delete only the data that meets certain criteria.

Without the WHERE clause, we would be forced to extract all data from the table and then manually filter it, for example, in an application, to perform specific tasks. This would be highly inefficient, especially for large databases.

The WHERE clause can be used with various operators such as equality (=), inequality (<>), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), as well as more specialized operators like BETWEEN, which allows you to specify a range of values, LIKE, designed for pattern matching, and IN, which allows you to select data from a specific set.

Let’s look at some examples of using WHERE to filter data.

Using the WHERE clause with the equality operator (=):

SELECT *
FROM StaffMembers WHERE StaffID = 123456;

In this case, the equality operator selects the record where the employee ID matches the number 123456. This is the simple equivalent of the equality operator in mathematics.

Using WHERE with the greater than (>) or less than (<) operators:

SELECT *
FROM StaffMembers WHERE Wage > 60000;

Here, the > operator is used, which filters out unnecessary data and returns information about employees whose wage exceeds 60,000. This operator can be useful when searching for records with values above or below a certain threshold.

Using WHERE with BETWEEN:

SELECT *
FROM StaffMembers WHERE Wage BETWEEN 60000 AND 80000;

The BETWEEN operator allows you to select records that fall within a specified range of values. In this case, it selects all employees whose salary is between 60,000 and 80,000, inclusive. This is useful when you need to extract a specific range of values.

Using WHERE with LIKE and Wildcard Characters:

SELECT *
FROM StaffMembers WHERE StaffName LIKE '%ja%';

The LIKE operator is used for pattern matching. In SQL databases, two wildcard characters can represent patterns: % replaces zero or more characters, and _ replaces exactly one character. In this specific example, the query returns all records from the StaffMembers table where the staff names contain "ja." This approach is often used when the exact value is not known, or when multiple matches need to be found.

These are just a few examples of the possibilities with WHERE in SQL. The variety of combinations and operators makes it a powerful tool when working with data.

Next, we will look at the AND, OR, and NOT operators, which are commonly used together with WHERE to create more complex queries for databases.

Using the AND, OR, and NOT Operators

AND, OR, and NOT are key logical operators in SQL. They are used to combine or invert conditions in SQL operators such as WHERE, HAVING, and others.

• The AND operator is used to create a query that returns true only when both conditions being compared are true. Let’s consider an example:

SELECT * FROM StaffMembers WHERE Wage > 60000 and ExperienceYears > 3;

In this case, the AND operator links two selection criteria: a wage greater than 60,000 and more than 3 years of experience. The result of this query will be records from the table that satisfy both conditions simultaneously.

• The OR operator returns true if at least one of the conditions is true:

SELECT * FROM StaffMembers WHERE Division = 'Production' OR Division = 'Advertising';

Here, the OR operator connects two selection conditions. The query will return records from the StaffMembers table where the employee belongs to either the 'Production' or 'Advertising' department.

• The NOT operator inverts the logical value of a condition, returning true if the condition is false, and false if the condition is true:

SELECT * FROM StaffMembers WHERE NOT (Division = 'HR');

In this query, the NOT operator inverts the condition Division = 'HR'. The query will return all rows from the StaffMembers table where the department is not 'HR'. This allows you to create queries that exclude certain categories of data.

These operators can be combined in any way to create complex conditions. For example:

SELECT * FROM StaffMembers WHERE (Division = 'Production' OR Division = 'Advertising') AND ExperienceYears > 5;

Here, the AND and OR operators are combined to create a more complex selection condition. The query will return only those records from the StaffMembers table where the department is either 'Production' or 'Advertising' and the employee has more than five years of experience.

Aggregating Data with GROUP BY and HAVING

In SQL, GROUP BY and HAVING are often used together to aggregate data and calculate various statistical measures based on grouping data by predefined criteria.

Let’s take a closer look at the GROUP BY operator. It is used to group rows in the result set by the values of a specific column or group of columns.  After the grouping, we can use aggregation functions like COUNT, SUM, AVG, and others to calculate statistical data for each individual group.

Example:

SELECT ClientID, COUNT(PurchaseID)
FROM Purchases
GROUP BY ClientID;

In this example, we count the total number of purchases (PurchaseID) made by each client (ClientID).

The HAVING operator is similar to WHERE, but the key difference is that HAVING is applied after the grouping has been done using GROUP BY. The main purpose of HAVING is to filter groups based on already computed aggregate values. This allows us to display only those groups that meet the criteria we set.

For instance, after performing the aggregation, we can filter to only groups meeting a certain threshold.

Example:

SELECT ClientID, COUNT(PurchaseID)
FROM Purchases
GROUP BY ClientID
HAVING COUNT(PurchaseID) > 3;

In this example, we only see clients (ClientID) whose total number of orders exceeds three.

Note that HAVING is applied in SQL queries exclusively after using GROUP BY. You cannot use HAVING without first grouping the data using GROUP BY.

In general, the order of operations in SQL looks like this:

• FROM: Specify the data source.

• WHERE: Filter data before grouping.

• GROUP BY: Group rows into sets based on column values.

• HAVING: Filter groups after they’ve been created.

• SELECT: Specify which columns will appear in the query result.

• ORDER BY: Sort the results in the desired order.

This sequence reflects the logic of query processing in SQL. The filtering conditions through WHERE are applied before grouping, which helps reduce the volume of data being processed. Conditions defined in HAVING apply to already formed data groups, allowing for more detailed analysis.

The GROUP BY and HAVING operators are essential tools for data aggregation in SQL.  Their use provides extensive data analysis capabilities, allowing statistical data collection and the identification of patterns, trends, and relationships within the data.

Using JOIN to Combine Tables

Often, developers need to select data from two SQL tables. To accomplish this, the JOIN operator is used, allowing data from two or more sources to be combined based on matching values in specific columns.

Tables in a database usually have linking columns that correlate with keys in other tables, thus enabling the linking of data. This allows for automatic synchronization of changes across related tables, which is an invaluable advantage when working with large databases where information is split across multiple tables.

The structure of a query using JOIN looks like this:

SELECT dataField(s)
FROM tableA
JOIN tableB
ON tableA.dataField = tableB.dataField;

In this case, JOIN is used to combine two tables (tableA and tableB). The join is performed based on a common column (dataField). Additionally, the query includes the selection of specific columns (dataField(s)) that the developer wants to display in the final result.

It is important to note that in SQL, there are different types of table joins, including:

• INNER JOIN: This allows us to retrieve only those rows that have matching records in both tables, meaning where the join condition is met:

SELECT Purchases.PurchaseID, Clients.ClientName
FROM Purchases
INNER JOIN Clients
ON Purchases.ClientID = Clients.ClientID;

• LEFT (OUTER) JOIN: This is used when we need to retrieve all rows from the left table (the one specified first in the query), and the matching rows from the right table. If there are no matching rows in the right table, the results for those rows will contain NULL values:

SELECT Clients.ClientName, Purchases.PurchaseID
FROM Clients
LEFT JOIN Purchases
ON Clients.ClientID = Purchases.ClientID;

• RIGHT (OUTER) JOIN: This works similarly to the LEFT JOIN, but in reverse. Here, we get all the records from the right table, supplemented with matching data from the left table. If no matches are found for records from the right table, NULL will be placed in the columns for the left table:

SELECT Clients.ClientName, Purchases.PurchaseID
FROM Clients
RIGHT JOIN Purchases
ON Clients.ClientID = Purchases.ClientID;

• FULL (OUTER) JOIN: This type of join gives us all rows from both tables that have corresponding records. In other words, it combines the LEFT and RIGHT JOINs. If there are rows in the first table with no matching rows in the second table, the columns from the second table will contain NULL for those rows. Similarly, if records from the second table do not have matches in the first table, the columns from the first table will contain NULL for those rows:

SELECT Clients.ClientName, Purchases.PurchaseID
FROM Clients
FULL OUTER JOIN Purchases
ON Clients.ClientID = Purchases.ClientID;

It is worth noting that although the FULL (OUTER) JOIN is a standard SQL feature, not all SQL systems support it. For example, MySQL does not have built-in support for FULL (OUTER) JOIN, but you can emulate it using a combination of LEFT JOIN and UNION:

SELECT Clients.ClientName, Purchases.PurchaseID
FROM Clients
LEFT JOIN Purchases
ON Clients.ClientID = Purchases.ClientID

UNION 

SELECT Clients.ClientName, Purchases.PurchaseID
FROM Purchases
LEFT JOIN Clients
ON Clients.ClientID = Purchases.ClientID
WHERE Clients.ClientID IS NULL;

This query first performs a left outer join, attaching records from the Purchases table to the Clients table. Then, it joins records from Clients to Purchases that were not selected in the first query (i.e., those where ClientID is NULL). Finally, it combines the results of these two queries.

In this section, we discussed different types of JOIN in SQL. Each of these joins provides flexibility in managing which data from related tables we want to see in the result set.

Conclusion

In this guide, we explored the use of SQL operators such as SELECT, WHERE, ORDER BY, JOIN, GROUP BY, and HAVING through practical examples. These operators offer users extensive capabilities for processing information, enabling complex analytical queries and extracting maximum value from stored data. We hope that you now have a clear understanding of how to use SQL to extract data from a database!