• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL SELECT

← Previous Next →

In MySQL, the SELECT statement is one of the most important SQL commands. It allows you to retrieve data from one or more tables. Almost every MySQL operation involves fetching data at some point, and the SELECT statement provides flexible ways to get exactly the information you need.

Think of SELECT as a way to ask the database a question. You tell MySQL what columns you want and from which table, and it returns the matching records. You can also filter, sort, and manipulate data as part of your query.

What is SELECT Statement?

The basic syntax of a SELECT statement is simple:

SELECT column1, column2
FROM table_name;

  • SELECT specifies the columns you want.

  • FROM indicates the table to retrieve data from.

Example:

SELECT name, age FROM students;

This fetches only the name and age columns from the students table.

If you want all columns, you can use the asterisk (*) symbol:

SELECT * FROM students;

This retrieves every column in the table.

Retrieving Specific Data

Sometimes, you don’t need all the data. MySQL allows you to filter and control the results.

Using WHERE Clause

The WHERE clause lets you specify conditions:

SELECT * FROM students
WHERE age > 18;

Here, only students older than 18 are retrieved.

Using Comparison Operators

You can use operators like:

  • = (equal to)

  • > (greater than)

  • < (less than)

  • >= (greater than or equal to)

  • <= (less than or equal to)

  • <> or != (not equal to)

Example:

SELECT name FROM students
WHERE age >= 20;

Using Logical Operators

You can combine multiple conditions using AND, OR, and NOT.

  • AND: Both conditions must be true.

SELECT * FROM students
WHERE age > 18 AND city = 'Mumbai';

  • OR: Either condition can be true.

SELECT * FROM students
WHERE city = 'Delhi' OR city = 'Mumbai';

  • NOT: Excludes specific conditions.

SELECT * FROM students
WHERE NOT city = 'Delhi';

Sorting Results with ORDER BY

After retrieving data, you may want it in a specific order. The ORDER BY clause helps you sort results.

Syntax:

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC|DESC;

  • ASC (ascending) is default.

  • DESC (descending) sorts from highest to lowest.

Example:

SELECT name, age FROM students
ORDER BY age DESC;

This lists students from oldest to youngest.

Limiting Results with LIMIT

Sometimes you don’t need all rows. The LIMIT clause restricts the number of results returned:

SELECT * FROM students
LIMIT 5;

This retrieves only the first 5 rows. You can also skip a certain number of rows:

SELECT * FROM students
LIMIT 2, 5;

This skips the first 2 rows and fetches the next 5.

Selecting Distinct Values

To avoid duplicate entries, MySQL provides the DISTINCT keyword:

SELECT DISTINCT city FROM students;

This returns unique cities without repetition.

Using Aliases

Aliases let you rename columns or tables in the result for readability:

SELECT name AS StudentName, age AS StudentAge
FROM students;

For tables:

SELECT s.name, c.course_name
FROM students AS s
JOIN courses AS c ON s.course_id = c.id;

Combining Data with JOINs

SELECT becomes powerful when you retrieve data from multiple related tables. MySQL supports several types of JOINs:

  • INNER JOIN: Returns records that match in both tables.

SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.course_id = courses.id;

  • LEFT JOIN: Returns all records from the left table, with matching rows from the right table (or NULL if no match).

  • RIGHT JOIN: Returns all records from the right table, with matching rows from the left table.

  • FULL OUTER JOIN: MySQL doesn’t directly support it, but similar results can be achieved using UNION.

Using Functions in SELECT

MySQL allows you to perform calculations or transformations in SELECT statements using functions:

  • COUNT: Count the number of rows.

SELECT COUNT(*) FROM students;

  • AVG: Average of a numeric column.

SELECT AVG(age) FROM students;

  • MAX and MIN: Find maximum or minimum values.

SELECT MAX(age) AS OldestStudent FROM students;

  • CONCAT: Combine columns.

SELECT CONCAT(name, ' - ', city) AS Info FROM students;

Best Practices for SELECT Statements

  1. Always specify only the columns you need, instead of using *, for better performance.

  2. Use WHERE clauses to filter data rather than fetching everything.

  3. Combine JOINs carefully to avoid unnecessary duplication.

  4. Use indexes on frequently queried columns to speed up SELECT queries.

  5. Avoid very large queries without LIMIT when working with big datasets.

Real-Life Example

Suppose you are managing a school database with two tables: students and courses. You want to list all students older than 18, along with their course names, sorted by age:

SELECT s.name, s.age, c.course_name
FROM students AS s
INNER JOIN courses AS c ON s.course_id = c.id
WHERE s.age > 18
ORDER BY s.age ASC;

This query:

  • Joins the students and courses tables

  • Filters students older than 18

  • Sorts them by age in ascending order

Summary of the Tutorial

  • The SELECT statement is the core of data retrieval in MySQL.

  • It allows filtering, sorting, and combining data from multiple tables.

  • Keywords like WHERE, ORDER BY, LIMIT, DISTINCT, and JOINs enhance its functionality.

  • SELECT queries are the foundation for reports, dashboards, and any data-driven decision-making.

Mastering SELECT is essential before moving on to more advanced MySQL queries, because every complex operation in MySQL begins with a SELECT statement.

Practice Questions

  1. Write a query to retrieve all columns from the students table.

  2. Select only the name and age columns from the students table.

  3. Retrieve all records from the students table where age is greater than 18.

  4. List all students from the city Mumbai.

  5. Select the name and course_id of students, sorting the results by age in descending order.

  6. Retrieve the first 5 students from the table using the LIMIT clause.

  7. Fetch the unique cities of students using the DISTINCT keyword.

  8. Use an alias to rename the name column as StudentName in the output.

  9. Write a query to list all students along with their course names using an INNER JOIN with the courses table.

  10. Count the total number of students in the students table.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top