SQL Select Tutorial

Overview

Now you have your data populated (if you haven’t, please go back to the previous post) to complete the setup.

In this post, I’m going to show you various way to query your data using the SELECT command. When using in combination with other clauses (such as WHERE, LIMIT), you can get exactly what you want from your data.

Let’s get started.

Basic SELECT usage

Select all data from a table

When you don’t have a lot of data, you can use SELECT * to get all the data from a table:

-- Select all columns from a table
SELECT * FROM customers;
SELECT * FROM orders;

The data returns from SELECT * FROM customers is:

Select certain columns

There are times you only want to get data from specific columns. For example, you only need to customer’s id, first name and email from the customers table, here is the query you need:

-- Select specific columns from a table
SELECT customer_id, first_name, email FROM customers;

You can set the columns’ names to different names if you want/need. For example, I want to rename the column email to customer_email, here is the query:

-- Select specific columns from a table
SELECT customer_id, first_name, email as customer_email FROM customers;

Select only distinct values

There are times you want to get only the distinct values in a column. In such cases, use DISTINCT. For example, you want to know how many unique first name in your customers table:

-- Select distinct values from a column
SELECT DISTINCT first_name FROM customers;

Filter result using WHERE

When your data grows bigger, select * could cause serious problems. Doing so could cause our database server runs out of memory. In addition, you are only interested in a subset of your data. In such case, you can use WHERE to create conditions that your data must match.

-- Select only the records that have customer_id < 10
SELECT * FROM customers WHERE customer_id < 10;

Of course, you can have many conditions in your WHERE statement. In such case, chain them with AND

-- Multiple conditions in WHERE
SELECT * FROM customers WHERE customer_id < 10 AND last_name = 'Doe' AND phone LIKE '%1234'

ORDER the results

You can order your result by specific columns (plural). For example, you want to see the largest number of items in a single order, this is the query:

-- SELECT with ORDER BY single column
SELECT * FROM order_items ORDER BY quantity DESC;

Take a look at the column price for line 4 and 5. As you can see, the quantity for these two items are the same. If you also want to order the price, add the price column to the ORDER BY clause:

-- SELECT with ORDER BY multiple columns
SELECT * FROM order_items ORDER BY quantity DESC, price DESC;

LIMIT number of record

In the ORDER BY example above, if you are only interested in the top 3 items, you can use LIMIT:

-- SELECT with ORDER BY multiple columns and LIMIT
SELECT * FROM order_items ORDER BY quantity DESC, price DESC LIMIT 3;

Conclusion

In this post, I’ve shown you the most common SELECT queries. With these knowledge, you can perform some useful queries against your database. HOwever, the SELECT statement could be much more powerful than this. I’ll introduce you to some advanced SELECT queries in the next post.

Đạt Trần

I build softwares that solve problems. I also love writing/documenting things I learn/want to learn.