Database

Home / Topic / Database / SELECT Operations in SQL

SELECT Operations in SQL

The SELECT command is one of the most powerful and widely used tools in SQL, as it allows us to query and retrieve data from our tables. In this chapter, we will learn how to use the SELECT command to extract the information we need from our databases.

Basic Queries with SELECT

The simplest use of SELECT is to retrieve all records from a table. Below is a basic example to get all users from the users table:

sql

"This command selects all columns and rows from the users table. The asterisk indicates that we want to select all columns."

Selecting Specific Columns

Sometimes we may not want to retrieve all columns from a table. We can specify which columns we want to obtain in the SELECT command. Below is an example to select only the names and emails of the users:

sql

"In this case, we are selecting only the name and email columns from the users table. Other columns, like id or created_at, will not be included in the result."

Filtering Results with WHERE

We can use the WHERE clause to filter results and get only the records that meet certain criteria. For example, if we want to obtain only the users whose email ends with 'example.com', we can use the following query:

sql

"Here we are filtering the user records whose emails end with example.com. We use the WHERE clause with LIKE and the percentage symbol to search for patterns."

Using Comparison Operators

In addition to LIKE, we can use other comparison operators such as =, >, <, >=, and <= to filter the results. Below is an example to select users who were created after January 1, 2023:

sql

"This command selects users whose created_at field has a date after January 1, 2023."

Ordering Results with ORDER BY

We can order the results of a query using the ORDER BY clause. By default, the results are ordered in ascending order, but we can specify a descending order if needed. For example, to get users ordered by creation date in descending order, we would use the following command:

sql

"This command selects users from the users table and orders them by creation date, from most recent to oldest, using the ORDER BY clause with the DESC keyword to indicate descending order."

Limiting the Number of Results with LIMIT

Sometimes it's helpful to limit the number of rows returned in a query, especially when working with large volumes of data. We can use the LIMIT clause to specify how many rows we want to obtain. Below is an example to get only the first 5 users:

sql

"In this case, we are limiting the query to just five records. The LIMIT clause allows us to control how many rows we want to return in the query."

Using Joins for Relational Queries

In relational databases, data is often distributed across multiple tables. We can use JOIN to combine data from different tables in a single query. Below is an example of how to combine data from the users and orders tables:

sql

"In this example, we are using JOIN to combine data from the users table and orders table. The query selects the user's name and the total of their order where the records match between both tables using the id field from users and the user_id field from orders."

Summary

In this chapter, we have learned how to use the SELECT command to query data in SQL. We explored how to filter results with WHERE, order data with ORDER BY, limit results with LIMIT, and how to combine data from multiple tables using JOIN. In the next chapter, we will see how to update data in tables using the UPDATE command.

Back to previous chapter

Mark as read and continue

Ask me anything