MySQL SELECT Query: Retrieving Data from the Database

Sunucun 3 min read

MySQL is an open-source relational database management system (RDBMS) used by applications all over the world. The basic way to read information from a database is the SELECT query. In this article we explain the structure of SELECT and its filtering, sorting, limiting and grouping options using a single example table.

Basic Structure of a SELECT Query

SELECT column1, column2, ...
FROM table_name;
  • SELECT: Specifies which columns to return. Use * (asterisk) for all columns.
  • FROM: Specifies the table to read the data from.
  • The query ends with a semicolon (;). SQL keywords are not case-sensitive; they are usually written in capitals for readability.
Illustration: MySQL

Example Table

The examples below use the following table named customers:

idnamecitybalance
1AyşeIstanbul1500
2MehmetAnkara300
3ZeynepIstanbul820
4CanIzmir0

SELECT Query Examples

1. Selecting All Columns

SELECT * FROM customers;

Returns every row and column in the table. It is handy for a quick look at a table, but in application code selecting only the columns you need avoids unnecessary data transfer and makes the query faster.

2. Selecting Specific Columns

SELECT name, city FROM customers;

Returns only the name and city columns. You can also give a column an alias with AS: SELECT name AS customer_name FROM customers;

3. Filtering by a Condition (WHERE)

SELECT name, balance FROM customers
WHERE city = 'Istanbul';

Returns only the customers in Istanbul (Ayşe and Zeynep). Operators such as =, <>, >, <, BETWEEN, IN and LIKE can be used in WHERE.

4. Multiple Conditions (AND / OR)

SELECT name FROM customers
WHERE city = 'Istanbul' AND balance > 1000;

AND requires both conditions to be true, OR requires at least one. This example returns only Ayşe. Use parentheses to control precedence in complex conditions.

5. Sorting (ORDER BY)

SELECT name, balance FROM customers
ORDER BY balance DESC;

Sorts the results by balance from highest to lowest. ASC means ascending (the default) and DESC means descending.

6. Limiting the Number of Results (LIMIT)

SELECT name, balance FROM customers
ORDER BY balance DESC
LIMIT 2;

Returns the two customers with the highest balance. For pagination you can add an offset, for example LIMIT 10 OFFSET 20.

7. Unique Values (DISTINCT)

SELECT DISTINCT city FROM customers;

Lists each city once: Istanbul, Ankara, Izmir.

8. Grouping and Summarizing (GROUP BY)

SELECT city, COUNT(*) AS customer_count, SUM(balance) AS total_balance
FROM customers
GROUP BY city;

Returns the number of customers and the total balance for each city; for Istanbul, 2 customers and a total balance of 2320. To filter grouped results, use HAVING instead of WHERE: ... GROUP BY city HAVING COUNT(*) > 1;

Clause Order

The clauses of a SELECT query are written in this order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT. If the order is wrong, MySQL returns a syntax error.

Conclusion

SELECT is the foundation of reading data in MySQL. By combining column selection, filtering with WHERE, sorting with ORDER BY, limiting with LIMIT and summarizing with GROUP BY, you can retrieve exactly the data you need. On large tables, adding indexes to the columns used in WHERE and ORDER BY makes queries noticeably faster.

Categories

Latest guides