Selecting Data

Selecting data from a table means retrieving or viewing data that are stored in a table. Selecting data is like opening a cabinet drawer and reading the contents of the files.

Why would you want to do this? Let's say you're running an online store, and you have a table that contains information about all your products. If a customer asks about the price of a specific product, you would need to "select" that information from your products table to answer their question.

SELECT Keyword

In SQL, you use the SELECT statement to retrieve data. This command is followed by the names of the columns you want to see. If you want to see all columns, you can use an asterisk (*).

Here's an example:

SELECT * FROM products;

This command would retrieve all data from the products table. But if you only wanted to see the product names and prices, you could do:

SELECT product_name, price FROM products;

This command would only retrieve the names and prices of all products. Column names are comma-separated.

Filtering Results with the WHERE Keyword

Sometimes, you may want to filter the results. The queries shown previously grab all records, but you may be interested in one or a few results. We can use the WHERE clause to filter results.

The WHERE clause is used to filter results and extract only those records that fulfill a specified condition.

For example, suppose you have a customers table, and you want to find all customers who live in a certain city. You could do that using the WHERE clause.

SELECT * FROM customers
WHERE city = 'New York';

In this SQL statement:

1. SELECT * FROM Customers tells SQL that we want to see all columns (*) from the customers table.
2. WHERE city = 'New York' is the filter. This tells SQL that we only want the rows where the city is 'New York'.

So, in plain English, this command is saying: "Show me all the information from the 'customers' table, but only for customers who live in New York."

Comparison Operators

We're not limited to comparing two values with the = character. SQL offers a few comparison operators you can use in a WHERE clause. Here are a few examples:

Multiple Search Conditions

You can create multiple search conditions with logical operators. Logical operators in SQL are used to combine or modify conditions in SQL statements. They can be used in conjunction with the WHERE clause to create more complex or specific filters when selecting data.

Here's an example:

SELECT * FROM Customers
WHERE City = 'New York' AND Age > 21;

In this example, the AND operator is used to combine two conditions. This SQL command would only select customers who are both from New York and over the age of 21.

If you used the OR operator instead:

SELECT * FROM Customers
WHERE City = 'New York' OR Age > 21;

This would select any customer who is either from 'New York' or over the age of 21 (or both).

So logical operators can be very useful when you need to apply more than one condition to your data selection.

Sorting Results

You're not limited to grabbing results. If you need to sort the results, you can sort your results using the ORDER BY clause. This is particularly useful when you want to view your data in a specific order, either in ascending (ASC) or descending (DESC) order.

Let's look at an example:

SELECT * FROM customers
ORDER BY last_name ASC;

In this SQL statement, ORDER BY last_name ASC is used to sort the result set by the last_name column in ascending order. So, you'd see the customers with a last name starting with 'A' first, then 'B', and so on.

If you wanted to sort by last name in descending order instead, you could do:

SELECT * FROM customers
ORDER BY last_name DESC;

This would show customers with a last name starting with 'Z' first, then 'Y', and so on.

So, the ORDER BY clause, along with ASC and DESC, can be very useful when you need to view your data in a specific order.

Limiting and Offsetting Results

Limiting and offsetting results in SQL is a common practice, especially when dealing with large datasets or implementing features like pagination. There are two commonly used keywords, which are called LIMIT and OFFSET.

• LIMIT: Restricts how many rows you retrieve.
• OFFSET: Skips over a certain number of rows before starting to retrieve.

Think of it like this, if you were flipping through a book, LIMIT would determine how many pages you read, and OFFSET would determine from which page you start reading.

Limiting Results

The LIMIT keyword restricts the number of rows returned by a query. If you only want to retrieve a certain number of rows, you can use LIMIT.

For instance, if you only want to see the first 10 customers from a customers table, you would write:

SELECT * FROM customers
LIMIT 10;

Think of it like scooping water from a big bucket with a small cup. The LIMIT keyword defines the size of the cup.

Offsetting Results

OFFSET lets you skip a certain number of rows before starting to retrieve data. It's usually used in combination with LIMIT. This is especially useful for pagination in applications, like when you have "Page 1", "Page 2", etc., and each page shows a specific number of results.

For example, if you still want 10 results but not from the very first row—say you want to skip the first 20 rows and then retrieve the next 10—you would write:

SELECT * FROM Customers
LIMIT 10 OFFSET 20;

This can also be thought of as "skipping over" a certain number of results before you start collecting the ones you want.

In many databases, including MySQL and MariaDB, there's a shorthand for this:

SELECT * FROM Customers
LIMIT 20, 10;

Here, the first number (20) is the offset, and the second number (10) is the limit.

Key Takeaways

• The SELECT keyword is used in SQL to choose and display data from a database. The data returned is stored in a result table called the result set.
• The WHERE clause is used to filter records and extract only those that fulfill a specific condition.
• Comparison operators are used in the WHERE clause to specify conditions. Operators include = (equal), != (not equal), > (greater than), < (less than), >= (greater than or equal), <= (less than or equal), LIKE (match pattern), IN (within a list), and BETWEEN (within a range).
• Logical operators are used to combine or modify conditions in SQL. Operators include AND, OR, and NOT.
• The ORDER BY clause is used to sort the result set in ascending or descending order. The keywords are ORDER BY (sort the result set), ASC (ascending order, which is the default), and DESC (descending order).
• The LIMIT is used to constrain the number of rows returned by a query. It's like specifying the maximum number of records you want from the result set.
• The OFFSET keyword is used to skip a number of rows in the result set before starting to return rows. It's typically used with LIMIT for features like pagination.