MySQL Select Statement

Chapters

MySQL Tutorial

The SELECT statement in MySQL is used to retrieve data from one or more tables in a database. To explain its functionality, let’s use the Northwind database, a commonly used sample database that contains tables representing a small business scenario (e.g., customers, orders, products, etc.).

Basic Syntax of `SELECT`

SELECT column1, column2, ... 
FROM table_name
WHERE condition;

column1, column2, ...: The columns you want to retrieve.
table_name: The table from which you want to fetch the data.
WHERE condition: Optional. Filters the rows returned by the query.

Example Northwind Table: `Customers`

CustomerID	CompanyName	ContactName	City	Country
ALFKI	Alfreds Futterkiste	Maria Anders	Berlin	Germany
ANATR	Ana Trujillo	Ana Trujillo	Mexico City	Mexico
BERGS	Berglunds snabbköp	Christina B	Luleå	Sweden

1. Select All Columns

Retrieve all the columns and rows from the Customers table:

SELECT * FROM Customers;

Result:

CustomerID	CompanyName	ContactName	City	Country
ALFKI	Alfreds Futterkiste	Maria Anders	Berlin	Germany
ANATR	Ana Trujillo	Ana Trujillo	Mexico City	Mexico
BERGS	Berglunds snabbköp	Christina B	Luleå	Sweden

2. Select Specific Columns

Retrieve only CompanyName and Country:

SELECT CompanyName, Country FROM Customers;

Result:

CompanyName	Country
Alfreds Futterkiste	Germany
Ana Trujillo	Mexico
Berglunds snabbköp	Sweden

3. Filter Rows with `WHERE`

Retrieve customers from Germany:

SELECT * FROM Customers WHERE Country = 'Germany';

Result:

CustomerID	CompanyName	ContactName	City	Country
ALFKI	Alfreds Futterkiste	Maria Anders	Berlin	Germany

4. Sorting Results with `ORDER BY`

Retrieve customers and sort them by City in ascending order:

SELECT CompanyName, City FROM Customers ORDER BY City ASC;

Result:

CompanyName	City
Alfreds Futterkiste	Berlin
Berglunds snabbköp	Luleå
Ana Trujillo	Mexico City

5. Limiting Results with `LIMIT`

Retrieve only the first two customers:

SELECT * FROM Customers LIMIT 2;

Result:

CustomerID	CompanyName	ContactName	City	Country
ALFKI	Alfreds Futterkiste	Maria Anders	Berlin	Germany
ANATR	Ana Trujillo	Ana Trujillo	Mexico City	Mexico

6. Using Wildcards with `LIKE`

Find customers whose ContactName starts with ‘A’:

SELECT * FROM Customers WHERE ContactName LIKE 'A%';

Result:

CustomerID	CompanyName	ContactName	City	Country
ALFKI	Alfreds Futterkiste	Maria Anders	Berlin	Germany
ANATR	Ana Trujillo	Ana Trujillo	Mexico City	Mexico

7. Using Aggregate Functions

Count the total number of customers:

SELECT COUNT(*) AS TotalCustomers FROM Customers;

Result:

TotalCustomers
3

8. Grouping Results with `GROUP BY`

Group customers by Country and count the number of customers in each country:

SELECT Country, COUNT(*) AS CustomerCount 
FROM Customers 
GROUP BY Country;

Result:

Country	CustomerCount
Germany	1
Mexico	1
Sweden	1

9. Joining Tables

Retrieve the names of customers along with their orders from the Orders and Customers tables:

Customers Table:

CustomerID	CompanyName	ContactName	Country
ALFKI	Alfreds Futterkiste	Maria Anders	Germany

Orders Table:

OrderID	CustomerID	OrderDate
10248	ALFKI	2023-01-10

SELECT Customers.CompanyName, Orders.OrderDate 
FROM Customers 
JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result:

CompanyName	OrderDate
Alfreds Futterkiste	2023-01-10

10. Subqueries

Find customers who have placed an order:

SELECT * 
FROM Customers 
WHERE CustomerID IN (SELECT CustomerID FROM Orders);

Result:

CustomerID	CompanyName	ContactName	Country
ALFKI	Alfreds Futterkiste	Maria Anders	Germany

Conclusion

The SELECT statement is the foundation of working with MySQL databases. Using it with clauses like WHERE, ORDER BY, GROUP BY, and LIMIT allows you to query, filter, and organize data effectively. Mastering these techniques is essential for building powerful and dynamic database-driven applications.