Using Union and order by clause in MySQL - MySQL Developer Tutorial

Using Union and order by clause in MySQL

Union or Union all is used to combine two or more result sets. We often need to return the output in sorted form after combining the results. There are different ways to return the sorted output or use order by. In below example, I am going to use Common table expressions.

Syntax:

with cte as (
Select column1,column2,column2 from TableOne
Union all
Select column1,column2,column3 from TableTwo
)
Select * from cte order by column1

Example : 

Let's say If I have to tables customer and customer4 , My query for Union and order by looks like below. I want to order by customerid.

Create customer table with sample data : 

CREATE TABLE `customer` (
  `idcustomer` int,
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza','Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');

create customer4 table with some sample data :

CREATE TABLE `customer4` (
  `idcustomer` int,
  `firstname` char(50)  NULL,
  `lastname` Nchar(30)  NULL,
  `age` int(3) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer4(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza','Ali',39,'505-4141969','1980-01-01','M'),
(5,'Aamir','Naz',39,'505-4141969','1980-01-01','M');

Using Union and order by clause in MySQL

with cte as (
Select customerid, firstname, lastname from customer
Union 
Select customerid, firstname, lastname from customer4)
Select * From cte order by customerid ;

Union ALL in MySQL - MySQL Developer Tutorial

Union ALL in MySQL 

MySQL Union ALL operation is used to combine two or more result sets. Before you use UNION ALL operator in MySQL or MariaDB, you need to remember couple of things.

UNION ALL will return Duplicate records if result sets have same records.

1. When you combine two or more result sets, all the result sets should have the same number of columns.
2. The data type of the columns of each result set should be compatible or same like other results set columns.

Syntax : 

Union ALL in MySQL syntax

Select Column1,Column2,Column2 from TableOne
UNION ALL
Select Column1,Column2,Column3 from TableTwo
UNION ALL
Select Column1,Column2,Column2 from TableThree

Example : 

Let's create couple of tables  by using below scripts and then we use Union ALL operator.

CREATE TABLE `customer` (
  `idcustomer` int,
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza','Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');

create table customer4.

CREATE TABLE `customer4` (
  `idcustomer` int,
  `firstname` char(50)  NULL,
  `lastname` Nchar(30)  NULL,
  `age` int(3) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer4(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza','Ali',39,'505-4141969','1980-01-01','M'),
(5,'Aamir','Naz',39,'505-4141969','1980-01-01','M')

Let  UNION ALL combine the rows or customer and customer4 table.

select * from customer
UNION ALL 
Select * from customer4;

MySQL Union ALL operator

Notice that in customer and customer4 some of the columns have different data types but it worked in UNION ALL as the data types are compatible.  Also duplicate records are returned as UNION ALL returns all records from each result set and does not remove duplicate.

Union in MySQL - MySQL Developer Tutorial

Union in MySQL 

MySQL Union operation is used to combine two or more result sets. Before you use UNION operator in MySQL or MariaDB, you need to remember couple of things.

UNION result set will be Distinct records. You don't have to use Distinct. If result sets have duplicate records, UNION will return only distinct records.

1. When you combine two or more result sets, all the result sets should have the same number of columns.
2. The data type of the columns of each result set should be compatible or same like other results set columns.

Syntax : 

Union in MySQL syntax

Select Column1,Column2,Column2 from TableOne
UNION
Select Column1,Column2,Column3 from TableTwo
UNION 
Select Column1,Column2,Column2 from TableThree

Example : 

Let's create couple of tables  by using below scripts and then we use Union operator.

CREATE TABLE `customer` (
  `idcustomer` int,
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza','Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');

create table customer4.

CREATE TABLE `customer4` (
  `idcustomer` int,
  `firstname` char(50)  NULL,
  `lastname` Nchar(30)  NULL,
  `age` int(3) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza','Ali',39,'505-4141969','1980-01-01','M'),
(5,'Aamir','Naz',39,'505-4141969','1980-01-01','M')

Let's union combine the rows or customer and customer4 table.

select * from customer
UNION 
Select * from customer4;

MySQL Union 

Notice that in customer and customer4 some of the columns have different data types but it worked in UNION as the data is compatible.  Also Union returned distinct records in result set from two tables.

Select random records using ORDER BY RAND() in MySQL - MySQL Developer Tutorial

Select random records using ORDER BY RAND() in MySQL 

Scenario : 

Let's say you are working as MySQL Developer and you are asked to provide some sample data from a table. You need to provide 10 rows or 100 rows but they should be selected random from table.

Solution : 

You can use Rand ( ) function with Limit to get RANDOM ROWS from table. By using Limit you will restrict the number of rows you want to return.

Select * from YourTableName Order by RAND() Limit Count;

Let's say if we want to get 10 Random rows from customer table, we can use below statement in MySQL or MairaDB.

Select * from customer order by RAND () Limit 10;

How to use Limit in MySQL Query - MySQL Developer Tutorial

How to use Limit in MySQL Query

Limit is used to restrict the number of rows you want to returned by select statement in MySQL. Tables can have thousands or millions of rows and sometime we need to only get some rows to take a look, we can use Limit clause to restrict the number of rows returned by our select query in MySQL.

Syntax :

Here is the syntax for Limit, there are two parameters you can pass to Limit Count and Offset.

Select column1,Column2,.... from YourTableName
Limit Offset, Count;

Let's create a sample table with some data and then we will use Limit to retrieve records.

CREATE TABLE `customer` (
  `idcustomer` int,
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza','Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');

Use Limit With Count only: 

Now let's say that we want to get only two rows from customer table, we can use below statement.

select * from customer Limit 2;

In this example, we are not sure about the order of rows which will be returned. Above query will return only two records. If you want to sort ( order by ) the records and then get 2 top out of them, you can use below statement. If you have auto_increment enabled, by ordering ascending or descending you can get Oldest or newest Records. Same way if you have createdatetime for your rows, you can get oldetest and newest records by using order by and Limiting the rows in Select.

select * from customer order by id Limit 3 ;

Using Offset  with Limit : 

Offset is used to specify the offset of the first row to be returned. Let's say if you want to start after 2 rows, then you will be using below statement.

Select * from customer  Limit 2 Offset 2;

You can also use order by to make sure the order in which you would like to get the rows.

FOUND_ROWS() Function for Total Number of Rows Affected in MySQL - MySQL Developer Tutorial

FOUND_ROWS() Function for Total Number of Rows Affected in MySQL

If you want to know how many rows are affected by last statement in MySQL in given sessions, you can use FOUND_ROWS() function.

Let's say we have a table customer with below definition and some sample records.

CREATE TABLE `customer` (
  `idcustomer` int,
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,null,'Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M');

Now you can run any statement and then we can run Found_Rows() function to check how many rows are affected.

If we simple run select statement on  customer table.

Select * from customer;

The above query has returned use 4 rows from customer table. We can check the rows affected by above query by using FOUND_ROWS() function.

Select FOUND_ROWS();

use FOUND_ROWS() Function to get rows affected by last statement

How to use Count in MySQL Query - MySQL Developer Tutorial

How to use Count in MySQL Query

Let's say if you need to get the count of all the rows from MySQL table, you can use COUNT Function.

Let's create our sample table customer and then insert some records before we run different scenarios for Count function in MySQL.

CREATE TABLE `customer` (
  `idcustomer` int,
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,null,'Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M');

Get Total Rows from a Table: 

To get the total row count from a table, you can use below syntax.

Select count(*) From YourTableName; 

Now if we want to get the row count from our customer table, we will use below query.

Select count(*) from customer;

MySQL Tutorial- Get Row Count by using Count function in MySQL

Get Count of values in Table Column : 

Let's say if you want to get the count of values in column of a table, you can use below syntax, Remember that it will ignore the Null values in the Count.

Select count(columnName) from YourTableName;

Let's say if want to get count of values in firstName in customer table, we can use below statement, As there is one Null value, the count will ignore that and will return only 3 out of 4 rows.

Select count(firstname) from customer;

Get Distinct Count : 

If you are interested to get Distinct ( Different) values count, you can use Distinct with Count function in MySQL.

In below example, if we want to get distinct values for firstname column in customer table. we can use below script. Notice that it will not consider the Null values in Count.

Select count(distinct firstname)  from customer;

MySQL Distinct Count from Column Table

How to Select Distinct Data from Table in MySQL or MariaDB - MySQL Developer Tutorial

How to Select Distinct Data from Table in MySQL or MariaDB

When you use distinct keyword in Select, it returns you distinct (different) values.  Below is the syntax you will use to get distinct values from MySQL Table.

Select distinct column1,column2,... from YourTableName;

Example :
Let's create customer table and then insert some rows in customer table.

CREATE TABLE `customer` (
  `idcustomer` int,
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert some values in table.

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Aamir','Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M');

Now let's say if you want to get the distinct or different values from first name column, you can run below  statement.

Select distinct firstname from customer;

The above query will return you only "Aamir".

Now let's say if you want to get distinct values for firstname and phonenumber, then you will get two values.

Select distinct firstname,phonenumber from customer;

How to Select data from Table in MySQL or MariaDB - MySQL Developer Tutorial

How to Select data from Table in MySQL or MariaDB

The data is saved in tables in Database / Schema in MySQL or other Relation Database management systems. Once the data is saved by manual process or by using the Application, we need to retrieve the data in Application or in Reporting. Select is the statement that we use to retrieve the data from tables/views in MySQL.

Select all Records from Table :

If we need to Select all the columns in table with all rows in table, we can use below syntax.

Select * from YourTableName;

The * in above query represents all columns.

Show Selected Column : 

We don't need to see the data from all the columns sometime. In those cases we can show or select the data from the columns we like. You can use below syntax to show select columns from a table or view in MySQL or MariaDB.

Select column1,Column2,Column3,.... From YourTableName;

Column Alias:

Sometime the column names in the tables are not that readable and you want to present more readable column names when you retrieve the records. You can Alias any column name by using "ColumnName as ColumnAliasName".

Select column1 As ColumnAlisaName, Column2,Column3,...From YourTableName;

Columns with Space in Column Names:

I don't really advise to have space in column name. But there are many scenarios when people have space in column names such as "Fist Name", in those cases when you have space between column name, you have to use acute ` around column names when using in Select or anywhere in queries.

Select `column name`, column2, `first name`, column 4 from YourTableName;

How to retrieve data from MySQL Table | Get data from MariaDB Database table

How to get record counts for all tables in MySQL database - MySQL developer Tutorial

How to get record counts for all tables in MySQL database

If you are interested to get rough idea about Row Count for each of the table, you can use tables from information_schema. That will give you rough estimation of row count.

SELECT Table_name,SUM(table_rows) As RowCount 
     FROM information_schema.tables 
     WHERE TABLE_SCHEMA = 'YourDBName';

To get the actual count, you need to use count(*) function for each of the table. You can generate the query for each of the table with count(*) , once the queries are generated, copy them and run into new query window to get the table with row count.

select Concat('Select "',table_name,
'" as tablename,count(*) from ',table_name,' Union ') as Query from 
 INFORMATION_SCHEMA.TABLES 
WHERE table_schema = 'YourDatabaseName';

Copy the output and remove the UNION from the last line and run rest of statement to get the table names with rowcount.

Determine record count for all the tables in MySQL Database - MySQL Tutorial for Developers

How to get the sizes of the tables of a MySQL database - MySQL Developer Tutorial

How to get the sizes of the tables of a MySQL database

Below query can be used to get the size of tables in a database in MySQL. We will use information_schema.table system table to get the name for tables and size.

SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "YourDatabaseName";

Update the above query according to your database name and you will get the list of tables in specific databases with size in MB.

Determine size of tables in MySQL database - MySQL Tutorial for developers

How to get list of all MySQL Databases - MySQL Developer Tutorial

How to get list of all MySQL Databases

Often as MySQL DBAs or MySQL developers we need to get the current list of Databases which exits on MySQL Server. You can use below query that uses system tables to get the list of MySQL Databases.

You can use

show databases;

Or you can use below

Select schema_name from information_schema.schemata;

Get list of all MySQL Database | Get All databases names from MariaDB

How to check if MySQL database exists - MySQL Developer Tutorial

How to check if MySQL database exists

You can use below query to check if the database / schema exits in MySQL

Select schema_name from information_schema.schemata
where schema_name='YourDatabaseName';

or you can use below.

show databases like '%YourDBName%';

Check if MySQL Database exists | How to check if database exists in MairaDB

How to find Duplicate Records in MySQL Table - MySQL Developer Tutorial

How to find Duplicate Records in MySQL Table

There are many ways you can find the duplicate records. Let's create sample table customer with some duplicate records and then write queries to find duplicate records in MySQL or MariaDB.

CREATE TABLE `customer` (
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

Insert duplicate records in MySQL Table customer.

insert into customer
(firstname,lastname,age,phonenumber,dob,gender)
values
('Aamir','shahzad',39,'505-424000','1980-01-01','M'),
('Aamir','shahzad',39,'505-424000','1980-01-01','M'),
('Aamir2','',40,'505-4141000','1980-02-01','M'),
('Aamir2','',40,'505-4141000','1980-02-01','M'),
('Aamir3',' ',41,'505-4141000','1980-02-01','M'),
('Aamir5',' ',41,'505-4141000','1980-02-01','M')
;

We are using all the columns to find duplicate records. You can change the query according to columns in which you would like to check the duplicate records.

Use Group by and Having Clause:

We can use Group by Having clause to find the duplicate records. Check the below script

Select firstname,lastname,age,phonenumber,dob,gender,
count(*) as RecordCnt
 from customer 
 group by firstname,lastname,age,phonenumber,dob,gender
 having count(*)>1;

By using Row _Number in MySQL to find Duplicate Record : 

We can use Row_Number to find duplicate records, here first I wrote the query and then used as derived query to filter where Row_Number>1 to get only duplicate records.

select * From (
 Select *,
 ROW_NUMBER() OVER (
 PARTITION BY firstname,lastname,age,phonenumber,dob,gender
 ORDER BY firstname,lastname,age,phonenumber,dob,gender)
 as RowNumber
 from customer) d 
 where RowNumber>1;

How to delete a certain row from MySQL table with same column values - MySQL Developer Tutorial

How to delete a certain row from MySQL table with same column values

Let's say you have a table with some columns and the table have duplicate rows. You need to delete the row with same values in other row.

Let's create the customer table and insert duplicate records (same records) and then we can delete the duplicate row.

CREATE TABLE `customer` (
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

Insert duplicate records. In this example, you can see that first two rows are duplicate records.

insert into customer
(firstname,lastname,age,phonenumber,dob,gender)
values
('Aamir','shahzad',39,'505-424000','1980-01-01','M'),
('Aamir','shahzad',39,'505-424000','1980-01-01','M'),
('Aamir2','',40,'505-4141000','1980-02-01','M'),
('Aamir3',' ',41,'505-4141000','1980-02-01','M')
;

If we need to delete one record out of duplicate record from above customer table, we can use below delete statement. We used Limit to handle this situation. Limit 1 will return only 1 record.

Delete from customer
where firstname='Aamir' and lastname='shahzad'
limit 1;

Let's say if you have 5 duplicate records and you need to delete 4 out of them, then you will be using Limit 4.