Select Query in MySQL

Introduction

The SELECT query is the most important SQL command used to retrieve data from a database.

It allows you to fetch:

  • All records
  • Specific columns
  • Filtered data
  • Sorted and grouped results

Almost every database operation involves the SELECT statement.

What is a SELECT query?

The SELECT statement is used to read data from one or more tables in MySQL.

It does not modify data — it only retrieves it.

Basic Syntax

SELECT column1, column2

FROM table_name;

Selecting All Columns

SELECT * FROM Students;

  • * means all columns
  • Returns complete table data

Selecting Specific Columns

SELECT name, age FROM Students;

Returns only selected columns.

Using WHERE Clause (Filtering Data)

The WHERE clause is used to filter records.

SELECT *

FROM Students

WHERE age > 20;

You can use operators like:

  • =, >, <, >=, <=
  • AND, OR, NOT

Using DISTINCT (Remove Duplicates)

SELECT DISTINCT city FROM Students;

Returns unique values only.

Using ORDER BY (Sorting Data)

SELECT * FROM Students

ORDER BY age ASC;

  • ASC → ascending (default)
  • DESC → descending

Using LIMIT (Restrict Results)

SELECT * FROM Students

LIMIT 5;

Returns only the first 5 rows.

Using Aliases (AS)

Aliases: rename columns for output:

SELECT name AS student_name FROM Students;

Using Aggregate Functions

Aggregate functions perform calculations:

  • COUNT() → number of rows
  • SUM() → total
  • AVG() → average
  • MIN() → minimum
  • MAX() → maximum

Example:

SELECT COUNT(*) FROM Students;

Using GROUP BY

Group rows with the same values:

SELECT city, COUNT(*)

FROM Students

GROUP BY city;

Using HAVING

Filters grouped results:

SELECT city, COUNT(*)

FROM Students

GROUP BY city

HAVING COUNT(*) > 2;

Using JOIN (Multiple Tables)

SELECT can retrieve data from multiple tables using JOIN.

INNER JOIN Example

SELECT Students. name, Courses.course_name

FROM Students

INNER JOIN Courses

ON Students.id = Courses.student_id;

Using Subqueries

A query inside another query:

SELECT name

FROM Students

WHERE age > (SELECT AVG(age) FROM Students);

Using IN

SELECT * FROM Students

WHERE city IN ('Delhi', 'Mumbai');

Using BETWEEN

SELECT * FROM Students

WHERE age BETWEEN 18 AND 25;

Using LIKE (Pattern Matching)

SELECT * FROM Students

WHERE name LIKE 'A%';

Order of Execution in SELECT

Execution order is:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY
  7. LIMIT

Example Full Query

SELECT name, age

FROM Students

WHERE age > 18

GROUP BY city

HAVING COUNT(*) > 1

ORDER BY age DESC

LIMIT 5;

Example Scenario

You can use SELECT to:

  • View all students
  • Filter students by age
  • Count students in each city
  • Sort results
  • Combine tables

Common Mistakes

  • Forgetting FROM clause
  • Using wrong column names
  • Not using WHERE when needed
  • Confusing GROUP BY and ORDER BY
  • Missing quotes for text values

Key Points to Remember

  • SELECT is used to retrieve data
  • Works with clauses like WHERE, ORDER BY, GROUP BY
  • Supports filtering, sorting, grouping
  • Can be used with joins and subqueries
  • Most widely used SQL command