SELECT statement

Overview

The SELECT statement helps you obtain the data you need from one or more tables.

The application of this statement will be helpful in several cases listed below:

• Evaluating data from only particular fields in a table.

• Reviewing data from several tables at the same time.

• Retrieving the data based on specific criteria.

Syntax

To request data from a table using the SELECT statement, you can use the following syntax:

SELECT * FROM table_name;

You are allowed to filter the table by column. Refer to the syntax below.

SELECT column1, column2, ...
FROM table_name;

We will define each syntax as follows.

• SELECT determines the data we need from the database or a table.

• * referred to as select star or asterisk or represents all. It defines that the query should return all columns of the queried tables.

• FROM clause indicates the table(s) to retrieve data from.

• table_name represents the table(s) name.

• column1, column2, ... these are used to specify the columns from where we want to retrieve the data.

The SELECT statement is case insensitive, which means select or SELECT has the same result.

Examples

We have a table named student_data that stores the id, name, and where the student lives.

CREATE TABLE student_data (
  id int,
  name string,
  domicile string
);
INSERT INTO student_data 
    (id, name, domicile) 
VALUES 
    (119291,'Jordan','Los Angeles'),
    (119292,'Mike','Melbourne'),
    (119293,'Will','Sydney');

All the examples below are executed in the public schema. You can also display table from another specific schema. Click here for more info.

#Case 1: Query data from all columns

1. In the first case, we want to display all the data from the student_data table. Please refer to the syntax below:

SELECT * FROM table_name;

2. Use the SELECT statement within the table name to get all the data:

SELECT * FROM student_data;

3. If you have successfully run the query, you will get all the data from the student_data table.

+--------+----------+----------------+
| id     | name     | domicile       |
+--------+----------+----------------+
| 119291 | Jordan   | Los Angeles    | 
| 119292 | Mike     | Melbourne      |
| 119293 | Will     | Sydney         |
+--------+----------+----------------+

#Case 2: Query data from specific columns

1. We want to get the list of students’ names with their IDs. Please refer to the syntax below:

SELECT column_1, column_2 FROM table_name;

2. Run the following query:

SELECT id, name FROM student_data;

3. If you have successfully run the query, you will get a list of students’ IDs & names from the student_data table.

+--------+----------+
| id     | name     | 
+--------+----------+
| 119291 | Jordan   | 
| 119292 | Mike     |
| 119293 | Will     | 
+--------+----------+

#Case 3: Query data from a specific column with the condition

1. If we have a large number of data, skimming for the desired data will require a long time. We can apply some conditions to the SELECT statement. Please refer to the syntax below:

SELECT column_1 FROM table_name WHERE condition;

2. Let’s say we want to know the student’s name who lives in Sydney, have a look and run the query below:

SELECT name FROM student_data WHERE domicile='Sydney';

3. If you have successfully run the query, we now know that Will lives in Sydney.

+----------+ 
| name     | 
+----------+
| Will     | 
+----------+