MySQL Select Statement

The MySQL Select Query is used to get one or more records from a table. It does not affect the database i.e. the result set is just for viewing, it does not alter the records in the table. There are two main syntaxes of the select clause, one to select all columns of a table and one to select specific columns.

MySQL Select syntax

SELECT * from <table-names>
[where <conditions>];

Syntax to select specific columns:

SELECT <column-names> from <table-names>
[where <conditions>];

The MySQL Select clause can be used with almost all operators and conditions in the where clause to build complex and huge queries. We will look at the most commonly used examples of the same. We will use the MySQL Workbench to run the Queries. The method to run the Queries using the command-line client is the same.

Simplest MySQL Query :

The Simplest MySQL Query that can be run is:

SELECT <clause>;

An example of the same is:

Select 1 + 2;

#which is equivalent to writing

SELECT 1+ 2 FROM DUAL;

MySQL Select all columns

To select Select all columns from a table, we do:

SELECT * FROM RENTAL
LIMIT 10;

MySQL Select with specific column names

We can specify the column names that we want in the result set and the result set will contain only those columns from the table.

SELECT INVENTORY_ID, CUSTOMER_ID, RETURN_DATE
FROM RENTAL
LIMIT 12;

MySQL Select Clause with conditions i.e. Where clause

To filter records based on conditions we can use the where clause along with the Select clause. This query can be as complex or as simple as need be. An example of a simple query with only one table and a simple condition is:

SELECT INVENTORY_ID,CUSTOMER_ID,RETURN_DATE
FROM RENTAL
WHERE CUSTOMER_ID =459;

MySQL Select clause in a Sub-Query

Select can also be used as a sub-query or inner query. A subquery is used to fetch records that are used as input for the outer query. An example of this is

SELECT *
FROM PAYMENT
WHERE AMOUNT >=2.99
AND CUSTOMER_ID = 1
AND RENTAL_ID IN (SELECT rental_id from RENTAL);

MySQL Select Clause with aggregate functions

Aggregate functions are functions like SUM, AVG, MAX, MIN, COUNT, etc. The Select Clause with aggregate functions may or may not have a Having Clause.

Select Clause with aggregate functions and no Having Clause in MySQL

This example uses an aggregate function but no Having Clause

SELECT AVG(AMOUNT),CUSTOMER_ID
FROM PAYMENT
GROUP BY CUSTOMER_ID;

Select Clause with aggregate function and Having Clause in MySQL

This example uses both the Select and Having Clause.

SELECT C.CUSTOMER_ID,C.FIRST_NAME,C.LAST_NAME
FROM PAYMENT P,CUSTOMER C
WHERE P.CUSTOMER_ID = C.CUSTOMER_ID
GROUP BY C.CUSTOMER_ID
HAVING AVG(P.AMOUNT) > 3
LIMIT 20;

Select Clause with multiple tables in MySQL:

The select clause can be used to show columns from multiple tables based on conditions. Such Queries are known as join queries.

SELECT S1.STORE_ID, S2.SALES 
FROM STORE S1
JOIN (
	SELECT CUS.STORE_ID, SUM(PAY.AMOUNT) SALES
	FROM CUSTOMER CUS
	JOIN PAYMENT PAY
	ON PAY.CUSTOMER_ID = CUS.CUSTOMER_ID
    GROUP BY CUS.STORE_ID
  ) S2
ON S1.STORE_ID = S2.STORE_ID
ORDER BY S1.STORE_ID;