Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

Select Query

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

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
﻿
﻿
﻿
﻿
﻿
﻿
﻿
﻿

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

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

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

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

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

SELECT Statement

MySQL SELECT Statement

Syntax

Example

Output

Selecting specific columns

Output

Select without table

Output

Aliasing a select_expr

Output

With the ORDER BY clause

Example

Output

With the LIMIT clause

Example

Output

With the UNION clause

Example

Output

Output

Output

ORDER BY and LIMIT in Unions

Example

Output

JOIN clause

Syntax

Example

Output

Output

SELECT ... INTO Statement

Example

Output

Sample.txt