Select Statement in SQL

Select Statement - Select Statement in SQL with examples

In this section we are going to learn one of the most used sql statements by the IT professionals while working with the database. This SQL statement is the SQL select statement. This statement is used to retrieve data from a database by specificity under some conditions. In this section we will see many examples. You should have access to MySQL or any other database for running the queries given here.

The select statement is used to read all or specific data from the database. For example you can read all data from the database or particular record(s) from the underlying table in the database.

Syntax of Select Statement


SELECT column1,column2, column... FROM table_name ;

Video Tutorial: Select Statement in SQL

Here is the complete instruction of using the select query in SQL for getting data from database:

In the select statement you can provide the table column names and finally the table_name after FROM statement.

Open the MySQL client and then connect to your database. Here is the query to connect to database:


connect learningmysql;

Here is the screen shot of the MySQL Client:

MySQL Client

Now you should create the table using following query:


CREATE TABLE `customer` (
  `cust_id` INT NOT NULL AUTO_INCREMENT,
  `customer_name` VARCHAR(45) NULL,
  `city` VARCHAR(100) NULL,
  `country` VARCHAR(100) NULL,
  PRIMARY KEY (`cust_id`));

Now insert the data using following query:


INSERT INTO `customer` (`customer_name`, `city`, `country`) VALUES ('Mohan', 'Delhi', 'India');
INSERT INTO `customer` (`customer_name`, `city`, `country`) VALUES ('John', 'Susanville', 'USA');
INSERT INTO `customer` (`customer_name`, `city`, `country`) VALUES ('Rajeev', 'Delhi', 'India');
INSERT INTO `customer` (`customer_name`, `city`, `country`) VALUES ('Ashok', 'Patna', 'India');
INSERT INTO `customer` (`customer_name`, `city`, `country`) VALUES ('Ravi', 'Banglore', 'India');
INSERT INTO `customer` (`customer_name`, `city`, `country`) VALUES ('Mason', 'New York', 'USA');
INSERT INTO `customer` (`customer_name`, `city`, `country`) VALUES ('Ujjawal', 'Baglore', 'India');

Now let's look at various query to read data from database.

Reading all data

The select * from customer query will display all data from database as shown below:


mysql> select * from customer;
+---------+---------------+------------+---------+
| cust_id | customer_name | city       | country |
+---------+---------------+------------+---------+
|       1 | Mohan         | Delhi      | India   |
|       2 | John          | Susanville | USA     |
|       3 | Rajeev        | Delhi      | India   |
|       4 | Ashok         | Patna      | India   |
|       5 | Ravi          | Banglore   | India   |
|       6 | Mason         | New York   | USA     |
|       7 | Ujjawal       | Baglore    | India   |
+---------+---------------+------------+---------+
7 rows in set (0.00 sec)

Selecting 2 columns cust_id, customer_name from the table

You can specify two columns in the select statement as shown below to get only two columns from database:


mysql> select cust_id, customer_name from customer;
+---------+---------------+
| cust_id | customer_name |
+---------+---------------+
|       1 | Mohan         |
|       2 | John          |
|       3 | Rajeev        |
|       4 | Ashok         |
|       5 | Ravi          |
|       6 | Mason         |
|       7 | Ujjawal       |
+---------+---------------+
7 rows in set (0.00 sec)

Table alias for easy query

You can also alias the table name which simplifies the query writing. Here is the example of aliasing customer table as c:


mysql> select c.cust_id, c.customer_name from customer c;
+---------+---------------+
| cust_id | customer_name |
+---------+---------------+
|       1 | Mohan         |
|       2 | John          |
|       3 | Rajeev        |
|       4 | Ashok         |
|       5 | Ravi          |
|       6 | Mason         |
|       7 | Ujjawal       |
+---------+---------------+
7 rows in set (0.00 sec)

Where condition with Select Statement

Following query that shows all the columns from database for the customer 'Ravi':


mysql> select * from customer where customer_name ='Ravi';
+---------+---------------+----------+---------+
| cust_id | customer_name | city     | country |
+---------+---------------+----------+---------+
|       5 | Ravi          | Banglore | India   |
+---------+---------------+----------+---------+
1 row in set (0.00 sec)

Select all records where country is India

Following query selects all the data where country is 'India':


mysql> select * from customer where country ='India';
+---------+---------------+----------+---------+
| cust_id | customer_name | city     | country |
+---------+---------------+----------+---------+
|       1 | Mohan         | Delhi    | India   |
|       3 | Rajeev        | Delhi    | India   |
|       4 | Ashok         | Patna    | India   |
|       5 | Ravi          | Banglore | India   |
|       7 | Ujjawal       | Baglore  | India   |
+---------+---------------+----------+---------+
5 rows in set (0.00 sec)

Use of count(*) with select statement

Here is the query which returns the number of rows where country is 'India':


mysql> select count(*) from customer where country ='India';
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.01 sec)

Distinct with Select Query

The distinct statement removes any duplicate form the query results and displays only unique data. Here is the query which uses the distinct statement to select all unique country form the customer table:


mysql> select distinct country from customer;
+---------+
| country |
+---------+
| India   |
| USA     |
+---------+

In this tutorial we have leaned Select Statement with many examples in SQL.

Related Tutorials:

SQL Tutorials