MySQL SELECT Statement

How to write a MySQL SELECT statement with an example? It is used to retrieve data from one or more tables present in the database. To explain the SELECT statement, we will use the world database that came along with the installation.

The MySQL SELECT Statement syntax can be written as:

-- Syntax
SELECT [ColumnName1], [ColumnName2]....,[ColumnNameN]
FROM Source;

• column names: Choose the columns from the tables depending on the requirement. It may be one or more.
• Source: Choose one or more tables from the Database. Joins are used to join multiple tables.

MySQL SELECT Statement Example

You can use the MySQL SELECT statement to retrieve rows without referencing. For example, the below statement will return 2 as output.

SELECT 1+1;

In the above case, MySQL allows you to use DUAL as the dummy table name. Again it is just an option.

SELECT  1+1 FROM DUAL;

MySQL SELECT * All Columns

In this example, we are going to choose all the existing Columns present in the country table using an asterisk (*). In the SELECT statement, * is the shortcut to represent all the available columns in the source tables. Remember, USE world will change the default database to the world.

USE world;
SELECT * 
  FROM country;

Or You can also try using the schema name before the table name.

SELECT * FROM world.country;

Problems with MySQL SELECT * Statement

• It retrieves all the columns from the specified table. Most of the time, we don’t require all of them.
• It retrieves the columns in default order. Sometimes we may need to alter the order of the columns while displaying them.
• If there are any changes made in the underlying table (Reordering Columns, Adding, or Removing Columns) will not reflect the Views created using the *.

NOTE: Please don’t use the MySQL SELECT * FROM statement in real-time, even if you want to retrieve all the columns present in a table.

The ideal approach, Placing all (or required) column names after the SELECT keyword. For instance, the above statement can rewrite as:

USE world;
SELECT Code, Name, Continent, Region, 
       SurfaceArea, IndepYear, Population, LifeExpectancy, 
       GNP, GNPOld, LocalName, GovernmentForm, 
       HeadOfState, Capital, Code2       
FROM country;

MySQL SELECT Statement for Few Columns

In real-time, choosing all the present columns in a table is not the case. It is because there will be some columns such as IDs, Bar codes, Modified dates, Rowguid, Photos, etc., which might not require.

For this, the SELECT statement allows us to pick a few or any specific columns from the tables. We can do it by placing the required col names, followed by the keyword.

USE world;
SELECT Name,
       Continent,
       Region,
       SurfaceArea,
       Population        
FROM country;

NOTE: If you forget to place the comma between the columns, it assumes the second column is the Alias for the first. So, Please be careful.

MySQL SELECT Statement using Workbench GUI

If you don’t know the syntax or you don’t want to write a query, then use Workbench Intellisense to write the SELECT Statement for you.

To do so, First, Navigate to the required Database (under schemas) and choose the table needed. Right Click on the Table will open the Context Menu with different options. Please choose Select Rows – Limit 1000. Here, the limit value was taken from the query window

This option will generate the SELECT Statement for us.

NOTE: If you want to see all the records, change the Limit to 1000 rows in the query window to the Don’t Limit option.

Command prompt Example

In this example, we will write a MySQL select statement in the command prompt to pick up a few columns from the country table.

SELECT Code, Name, Continent, Region, 
       SurfaceArea, Population, Capital
FROM country;

Points To Remember

• It returns all the records present in the table. If you want to restrict the Rows, then please use the WHERE Clause.
• Please avoid using SELECT * for selecting all the column’s names.
• Use Joins to work from Multiple Tables.