MySQL - SELECT Statement



MySQL SELECT Statement

The SELECT statement is used to retrieve the rows from one or more (existing) tables. You can use various clauses along with this statement.

Syntax

Following is the basic syntax of the SELECT statement −

SELECT select_expr FROM table_references WHERE where_condition

Where, select_expr is the expression indicating the columns you need to retrieve, table_reference are the name or reference of the table from which you ca retrieve the rows.

"*" can be used to select all the columns in a table and just by passing the name of a table as table_reference you can retrieve all the records.

Example

Assume we have created a table with name Sales in MySQL database using CREATE TABLE statement as shown below −

CREATE TABLE sales(
   ID INT,
   ProductName VARCHAR(255),
   CustomerName VARCHAR(255),
   DispatchDate date,
   DeliveryTime time,
   Price INT,
   Location VARCHAR(255)
);

Following query inserts a row in the above created table −

INSERT INTO SALES values
(1, 'Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 
'Hyderabad'),
(2, 'Earphones', 'Roja', DATE('2019-05-01'), TIME('11:00:00'), 2000, 
'Vishakhapatnam'),
(3, 'Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 
'Vijayawada'),
(4, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 
'Chennai'),
(5, 'Headset', 'Jalaja', DATE('2019-04-06'), TIME('11:08:59'), 6000, 
'Goa');

Following query retrieves the records of the Sales table with the price value greater than 6000, using the SELECT statement −

SELECT * FROM SALES WHERE PRICE>6000;

Output

Following is the output of the above query −

ID ProductName CustomerName DispatchDate DeliveryTime Price Location
1 Key-Board Raja 2019-09-01 11:00:00 7000 Hyderabad
4 Mobile Vanaja 2019-03-01 10:10:52 9000 Chennai

Selecting specific columns

By specifying the names of the required columns as selectexpr you can retrieve the desired column values of records of a table. Following query retrieves the ID, CustomerName, ProductName, Price columns from the Sales table −

SELECT ID, CustomerName, ProductName, Price FROM SALES;

Output

The above query produces the following output −

ID CustomerName ProductName Price
1 Raja Key-Board 7000
2 Roja Earphones 2000
3 Puja Mouse 3000
4 Vanaja Mobile 9000
5 Jalaja Headset 6000

Select without table

Using the SELECT statement You can also retrieve the desired computations without providing the table reference −

SELECT 1250*2256;

Output

Following is the output of the above query −

1250*2256
2820000

Aliasing a select_expr

You can use the alias column name as select expression.

Assume we have created a table with name MyPlayers in MySQL database using CREATE statement as shown below

CREATE TABLE Players(
   ID INT,
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255),
   PRIMARY KEY (ID)
);

Now, we will insert 7 records in Players table using INSERT statements −

Insert into Players values
(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India'),
(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica'),
(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka'),
(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India'),
(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India'),
(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India'),
(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Following query retrieves the First_Name, Last_Name values as name using the AS clause −

SELECT CONCAT(last_name,' ',first_name) AS name, Country FROM 
Players ORDER BY name;

Output

The above mysql query generates the following output −

name Country
Anderson James England
Dhawan Shikhar India
Jadeja Ravindra India
Kohli Virat India
Sharma Rohit India
Trott Jonathan SouthAfrica

With the ORDER BY clause

The ORDER BY clause is used to arrange the records of a table based on the specified column we can use this clause along with the TABLE statement as shown below −

TABLE table_name ORDER BY column_name;

Where table_name is the name of the table and column_name is the name of the column based on which you need to arrange the specified table.

Example

Assume we have created a table named EMP using the CREATE statement as shown below −

CREATE TABLE EMP (
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT
);

Now, let us insert values in the above created table using the INSERT statement as shown below −

INSERT INTO EMP VALUES
('Krishna', 'Sharma', 19, 'M', 2000),
('Raj', 'Kandukuri', 20, 'M', 7000),
('Ramya', 'Ramapriya', 25, 'F', 5000),
('Alexandra', 'Botez', 26, 'F', 2000);

Following query arranges and retrieves the contents of the EMP table based on the FIRST_NAME column −

SELECT * FROM EMP ORDER BY FIRST_NAME;

Output

Following is the output of the above mysql query −

FIRST_NAME LAST_NAME AGE SEX INCOME
Alexandra Botez 26 F 2000
Krishna Sharma 19 M 2000
Raj Kandukuri 20 M 7000
Ramya Ramapriya 25 F 5000

With the LIMIT clause

While fetching records if you want to limit them by a particular number, you can do so, using the LIMIT clause of MYSQL. You can use this clause too along with the TABLE statement as shown below −

SELECT * FROM table_name LIMIT lt_number OFFSET off_number;

Where, table_name is the name of the table, lt_number is the number of records to be retrieved and off_number is the offset number.

If you need to limit the records starting from nth record (not 1st), you can do so, using OFFSET along with LIMIT.

Example

Following query arranges the records of the EMP table based on the INCOME column and retrieves the first two records −

SELECT * FROM EMP ORDER BY INCOME LIMIT 2;

Output

The above mysql query will generate the output as shown below −

FIRST_NAME LAST_NAME AGE SEX INCOME
Krishna Sharma 19 M 2000
Alexandra Botez 26 F 2000

With the UNION clause

The MySQL UNION clause is used to combine the results of two or more SELECT/TABLE statements without returning any duplicate rows.

To use this UNION clause, each SELECT statement must have

  • The same number of columns selected.
  • The same number of column expressions.
  • The same data type and,
  • Have them in the same order

Following is the syntax to use the UNION clause (with the TABLE statement) −

TABLE table_name1 UNION TABLE table_name2;

Example

Assume we have created a table named Student using the CREATE statement as shown below −

CREATE TABLE Student (
   Name VARCHAR(20), 
   age INT
);

Now, let us insert three records into the Student table −

INSERT INTO Student VALUES 
('Krishna', 22),
('Raju', 20),
('Rahman', 21);

You can verify the contents of the student table as shown below −

SELECT * FROM Student;

Output

Following is the output of the above query −

Name age
Krishna 22
Raju 20
Rahman 21

Suppose we have another table with same number of rows along (with the data types) −

CREATE TABLE Staff (
   Name VARCHAR(20), 
   age INT
);

Now, let us insert few records into the Staff table −

INSERT INTO Staff VALUES 
('Amit', 35),
('Nanda', 33),
('Swathi', 39);

You can verify the contents of the student table as shown below −

SELECT * FROM Staff;

Output

Following is the output of the above query −

Name age
Amit 35
Nanda 33
Swathi 39

Following query combines the above two tables using the JOIN clause −

SELECT * FROM STUDENT UNION SELECT * FROM STAFF;

Output

The above mysql query generates the following output −

Name age
Krishna 22
Raju 20
Rahman 21
Amit 35
Nanda 33
Swathi 39

ORDER BY and LIMIT in Unions

You can also use ORDER BY or LIMIT clauses to two SELECT statements and join them using UNION. To do so, place the 2 SELECT statements within parenthesis and join them using UNION.

Example

(SELECT * FROM Staff ORDER BY age LIMIT 2) UNION 
(SELECT * FROM Student ORDER BY age LIMIT 2);

Output

The above query generates the output as shown below −

Name age
Nanda 33
Amit 35
Raju 20
Rahman 21

JOIN clause

When you have divided the data in two tables you can fetch combined records from these two tables using Joins.

Syntax

Following is the syntax of the MySQL Join clause −

escaped_table_reference: {
   table_reference
   | { JOIN table_reference }
}

Example

Suppose we have created a table with name EMPLOYEE using the following CREATE statement −

CREATE TABLE EMPLOYEE(
   ID INT NOT NULL,
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT,
   CONTACT INT
);

Now, let us inserts few records into the EMPLOYEE table −

INSERT INTO Employee VALUES
(101, 'Ramya', 'Rama Priya', 27, 'F', 9000, 101),
(102, 'Vinay', 'Bhattacharya', 20, 'M', 6000, 102),
(103, 'Sharukh', 'Sheik', 25, 'M', 8300, 103),
(104, 'Sarmista', 'Sharma', 26, 'F', 10000, 104);

Suppose, if we have created another table named CONTACT using the following CREATE statement −

CREATE TABLE CONTACT(
   ID INT NOT NULL,
   EMAIL CHAR(20) NOT NULL,
   PHONE LONG,
   CITY CHAR(20)
);

Now, let's insert four records into the CONTACT table −

INSERT INTO CONTACT (ID, EMAIL, CITY) VALUES
(101, 'ramya@mymail.com', 'Hyderabad'),
(102, 'vinay@mymail.com', 'Vishakhapatnam'),
(103, 'sharukha@mymail.com', 'Pune'),
(104, 'sarmista@mymail.com', 'Mumbai');

Following statement retrieves data combining the values in these two tables −

SELECT * from EMPLOYEE JOIN CONTACT ON EMPLOYEE.CONTACT = CONTACT.ID;

Note: For the following format output, you can use "\G" at the end of above query.

Output

Following is the output of the above query −

************* 1. row *************
        ID: 101
FIRST_NAME: Ramya
 LAST_NAME: Rama Priya
       AGE: 27
       SEX: F
    INCOME: 9000
   CONTACT: 101
        ID: 101
     EMAIL: ramya@mymail.com
     PHONE: NULL
      CITY: Hyderabad
************* 2. row *************
        ID: 102
FIRST_NAME: Vinay
 LAST_NAME: Bhattacharya
       AGE: 20
       SEX: M
    INCOME: 6000
   CONTACT: 102
        ID: 102
     EMAIL: vinay@mymail.com
     PHONE: NULL
      CITY: Vishakhapatnam
************* 3. row *************
        ID: 103
FIRST_NAME: Sharukh
 LAST_NAME: Sheik
       AGE: 25
       SEX: M
    INCOME: 8300
   CONTACT: 103
        ID: 103
     EMAIL: sharukha@mymail.com
     PHONE: NULL
      CITY: Pune
************* 4. row *************
        ID: 104
FIRST_NAME: Sarmista
 LAST_NAME: Sharma
       AGE: 26
       SEX: F
    INCOME: 10000
   CONTACT: 104
        ID: 104
     EMAIL: sarmista@mymail.com
     PHONE: NULL
      CITY: Mumbai

While joining two queries you can use the alias of the table in the query using the AS clause as shown below −

SELECT t1.FIRST_NAME, t2.email FROM employee AS t1 INNER JOIN 
contact AS t2 ON t1.id = t2.id;

Output

The above mysql query produces the following output −

FIRST_NAME email
Ramya ramya@mymail.com
Vinay vinay@mymail.com
Sharukh sharukha@mymail.com
Sarmista sarmista@mymail.com

SELECT ... INTO Statement

Using the SELECT ..... INTO statement you can store the result of the statement. You can store results into a variable (or multiple variables), into an output file and, into a dump file where a single row is stored. Following is the syntax of this

SELECT select_expression INTO variable_list
Or,
SELECT select_expression INTO OUTFILE output_file
Or,
SELECT select_expression INTO DUMPFILE dump_file

Example

Assume we have created a table named emp and populated it using the following queries −

CREATE TABLE Emp (
   ID INT, 
   Name VARCHAR(255), 
   Salary INT, 
   Location VARCHAR(255)
);

Now, let's inserts few records into the Emp table −

INSERT INTO Emp VALUES
(101, 'Raju', 35000, 'Bangalore'),
(102, 'Raman', 45000, 'Vishakhapatnam'),
(103, 'Rahman', 55000, 'Hyderabad');

Following query retrieves the name and location of an employee from the above created table and stores in variables −

SELECT Name, Location INTO @name, @loc FROM Emp where ID =101;

You can verify the values of the variables as −

SELECT @name, @loc;

Output

Following is the output of the above mysql query −

@name @loc
Raju Bangalore

Following query retrieves the contents of the emp table and stores in a text file locally −

SELECT Name, Location INTO OUTFILE 'Folder_Location/sample.txt' FROM Emp;

If you verify the sample.txt file you can observe the records of the emp table as −

Sample.txt

Raju Bangalore
Raman Vishakhapatnam
Rahman Hyderabad
Advertisements