MySQL UNION - Complete Tutorial

What is a union?

Unions combine the results from multiple SELECT queries into a consolidated result set.

The only requirements for this to work is that the number of columns should be the same from all the SELECT queries which needs to be combined .

  

Suppose we have two tables as follows

Let's now create a UNION query to combines both tables using DISTINCT

SELECT `column1`,` column1FROM `table1`
UNION DISTINCT
SELECT ` column1`,` column1`  FROM `table2`;

Here duplicate rows are removed and only unique rows are returned.

Note: MySQL uses the DISTINCT clause as default when executing UNION queries if nothing is specified.

Let's now create a UNION query to combines both tables using ALL

SELECT `column1`,` column1` FROM `table1`
UNION ALL
SELECT ` column1`,` column1`  FROM `table2`;

Here duplicate rows are included and since we use ALL.

Why use unions

Suppose there is a flaw in your database design and you are using two different tables meant for the same purpose. You want to consolidate these two tables into one while omitting any duplicate records from creeping into the new table. You can use UNION in such cases.

Summary

• The UNION command is used to combine more than one SELECT query results into a single query contain rows from all the select queries.
• The number of columns and data types in the SELECT statements must be the same in order for the UNION command to work. 
• The DISTINCT clause is used to eliminate duplicate values from the UNION query result set. MySQL uses the DISTINCT clause as the default when executing UNION queries if nothing is specified.
• The ALL clause is used to return all even the duplicate rows in the UNION query.

Practical examples using MySQL workbench

In our myFlixDB lets combine

membership_number and full_names from Members table

with

movie_id and title from movies table

We can use the following query

SELECT `membership_number`,`full_names` FROM `members`
UNION
SELECT `movie_id`,`title` FROM `movies`;

Executing the above script in MySQL workbench against the myflixdb gives us the following results shown below.

membership_number
full_names
1
Janet Jones
2
Janet Smith Jones
3
Robert Phil
4
Gloria Williams
5
Leonard Hofstadter
6
Sheldon Cooper
7
Rajesh Koothrappali
8
Leslie Winkle
9
Howard Wolowitz
16
67% Guilty
6
Angels and Demons
4
Code Name Black
5
Daddy's Little Girls
7
Davinci Code
2
Forgetting Sarah Marshal
9
Honey mooners
19
movie 3
1
Pirates of the Caribean 4
18
sample movie
17
The Great Dictator
3
X-Men

Read More

MySQL SubQuery Tutorial with Examples

What are sub queries?

A sub query is a select query that is contained inside another query. The inner select query is usually used to determine the results of the outer select query.

Let's look into the sub query syntax -

A common customer complaint at the MyFlix Video Library is the low number of movie titles. The management wants to buy movies for a category which has least number of titles.

You can use a query like

SELECT category_name FROM categories WHERE category_id =( SELECT MIN(category_id) from movies);

It gives a result

Let's see how this query works

The above is a form of Row Sub-Query. In such sub-queries the , inner query can give only ONE result. The permissible operators when work with row subqueries are [=, >, =, <=, ,!=,  ]

Let's look at another example ,

Suppose you want Names and Phone numbers of members of people who have rented a movie and are yet to return them.  Once you get Names and Phone Number you call them up to give a reminder. You can use a query like

SELECT full_names,contact_number FROM   members  WHERE  membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL );

Let's see how this query works

In this case, the inner query returns more than one results. The above is type of Table sub-query.

Till now we have seen two queries , lets now see an example of triple query!!!

Suppose the management wants to reward the highest paying member.

We can run a query like

Select full_names From members WHERE membership_number = (SELECT membership_number FROM payments WHERE amount_paid = (SELECT MAX(amount_paid) FROM payments));

The above query gives the following result -

Sub-Queries Vs Joins!

When compare with Joins , sub-queries are simple to use and easy to read. They are not as complicated as Joins

Hence there are frequently used by SQL beginners.

But sub-queries have performance issues.  Using a join instead of a sub-query can at times give you upto 500 times performance boost.

Given a choice, it is recommended to use a JOIN over a sub query.

Sub-Queries  should only be used as a fallback solution when you cannot use a JOIN operation to achieve the above

Summary

• Subqueries are embedded queries inside another query. The embedded query is known as the inner query and the container query is known as the outer query.
• Sub queries are easy to use, offer great flexibility and can be easily broken down into single logical components making up the query which is very useful when Testing and debugging the queries.
• MySQL supports three types of subqueries, scalar, row and table subqueries.
• Scalar sub queries only return a single row and single column.
• Row sub queries only return a single row but can have more than one column.
• Table subqueries can return multiple rows as well as columns.
• Subqueries can also be used in INSERT, UPDATE and DELETE queries.
• For performance issues, when it comes to getting data from multiple tables, it is strongly recommended to use JOINs instead of subqueries. Sub queries should only be used with good reason.

Read More

MYSQL - ALTER, DROP, RENAME, MODIFY

WHAT IS THE ALTER COMMAND?

As the saying goes Change is the only constant

With time business requirements change as well. As business requirements change, Database designs need changing as well.

MySQL provides the ALTER function that helps us incorporate the changes to the already existing database design.

The alter command is used to modify an existing database, table, view or other database objects that might need to change during the life cycle of a database.

Let's suppose that we have completed our database design and it has been implemented. Our database users are using it and then they realize some of the vital information was left out in the design phase. They don't want to lose the existing  data but just want to incorporate the new information. The alter command comes in handy in such situations. We can use the alter command to change the data type of a field from say string to numeric, change the field name to a new name or even add a new column in a table.

Alter- syntax

The basic syntax used to add a column to an already existing table is shown below

ALTER TABLE `table_name` ADD COLUMN `column_name` `data_type`;

 HERE

• "ALTER TABLE `table_name`" is the command that tells MySQL server to modify the table named `table_name`.
• "ADD COLUMN `column_name` `data_type`" is the command that tells MySQL server to add a new column named `column_name` with data type `data_type'.

Let's suppose that Myflix has introduced online billing and payments. Towards that end, we have been asked to add a field for the credit card number in our members table. We can use the ALTER command to do that. Let's first look at the structure of the members table before we make any amendments. The script shown below helps us to do that.

SHOW COLUMNS FROM `members`;

Field
Type
Null
Key
Default
Extra
membership_number
int(11)
NO
PRI
NULL
auto_increment
full_names
varchar(350)
NO
 
NULL
 
gender
varchar(6)
YES
 
NULL
 
date_of_birth
date
YES
 
NULL
 
physical_address
varchar(255)
YES
 
NULL
 
postal_address
varchar(255)
YES
 
NULL
 
contact_number
varchar(75)
YES
 
NULL
 
email
varchar(255)
YES
 
NULL
 

 We can use the script shown below to add a new field to the members table.

ALTER TABLE `members` ADD COLUMN `credit_card_number` VARCHAR(25);

 Executing the above script in MySQL against the Myflixdb adds a new column named credit card number to the members table with VARCHAR as the data type. Executing the show columns script gives us the following results.

SHOW COLUMNS FROM `members`;

  

Field
Type
Null
Key
Default
Extra
membership_number
int(11)
NO
PRI
NULL
auto_increment
full_names
varchar(350)
NO
 
NULL
 
gender
varchar(6)
YES
 
NULL
 
date_of_birth
date
YES
 
NULL
 
physical_address
varchar(255)
YES
 
NULL
 
postal_address
varchar(255)
YES
 
NULL
 
contact_number
varchar(75)
YES
 
NULL
 
email
varchar(255)
YES
 
NULL
 
credit_card_number
varchar(25)
YES
 
 

As you can see from the results returned, credit card number has been added to the members table. The data contained in the members' data is not affected by the addition of the new column.

WHAT IS THE DROP COMMAND?

The DROP command is used to

1. Delete a database from MySQL server
2. Delete an object (like Table , Column)from a database.

Let's now look at practical examples that make use of the DROP command.

In our previous example on the Alter Command, we added a column named credit card number to the members table.

Suppose the online billing functionality will take some time and we want to DROP the credit card column

We can use the following script

ALTER TABLE `members` DROP COLUMN `credit_card_number`;

Executing the above script drops the column credit_card_number from the members table

 Let's now look at the columns in the members table to confirm if our column has been dropped.

SHOW COLUMNS FROM `members`;

Executing the above script in MySQL workbench against the myflixdb gives us the following results.

Field
Type
Null
Key
Default
Extra
membership_number
int(11)
NO
PRI
NULL
auto_increment
full_names
varchar(350)
NO
 
NULL
 
gender
varchar(6)
YES
 
NULL
 
date_of_birth
date
YES
 
NULL
 
physical_address
varchar(255)
YES
 
NULL
 
postal_address
varchar(255)
YES
 
NULL
 
contact_number
varchar(75)
YES
 
NULL
 
email
varchar(255)
YES
 
NULL
 

Notice that the credit card number has been dropped from the fields list.

DROP TABLE

The syntax to DROP a table from Database is as follow -

DROP TABLE `sample_table`;

Let'look at an example

DROP TABLE `categories_archive`;

Executing the above script deletes the table named ` categories_archive ` from our database.

WHAT IS THE RENAME COMMAND?

The rename command is used to change the name of an existing database object(like Table,Column) to a new name.

Renaming a table does not make it to lose any data is contained within it.

Syntax:-

The rename command has the following basic syntax.

RENAME TABLE `current_table_name` TO `new_table_name`;

Let's suppose that we want to rename the movierentals table to movie_rentals, we can use the script shown below to achieve that.

RENAME TABLE `movierentals` TO `movie_rentals`;

Executing the above script renames the table `movierentals` to `movie_rentals`.

We will now rename the movie_rentals table back to its original name.

RENAME TABLE `movie_rentals` TO `movierentals`;

CHANGE KEYWORD

Change Keywords allows you to

1. Change Name of Column
2. Change Column Data Type
3. Change Column Constraints

 Let's look at an example. The full names field in the members table is of varchar data type and has a width of 150.

SHOW COLUMNS FROM `members`;

Executing the above script in MySQL workbench against the myflixdb gives us the following results.

Field
Type
Null
Key
Default
Extra
membership_number
int(11)
NO
PRI
NULL
auto_increment
full_names
varchar(150)
NO
 
NULL
 
gender
varchar(6)
YES
 
NULL
 
date_of_birth
date
YES
 
NULL
 
physical_address
varchar(255)
YES
 
NULL
 
postal_address
varchar(255)
YES
 
NULL
 
contact_number
varchar(75)
YES
 
NULL
 
email
varchar(255)
YES
 
NULL
 

Suppose we want to

1. Change the field name from "full_names" to "fullname
2. Change it to char data type with a width of 250
3. Add a NOT NULL constraint

 We can accomplish this using the change command as follows -

ALTER TABLE `members` CHANGE COLUMN `full_names` `fullname` char(250) NOT NULL;

 Executing the above script in MySQL workbench against myflixdb and then executing the show columns script given above gives the following results.

Field
Type
Null
Key
Default
Extra
membership_number
int(11)
NO
PRI
NULL
auto_increment
fullnames
char(250)
NO
 
NULL
 
gender
varchar(6)
YES
 
NULL
 
date_of_birth
date
YES
 
NULL
 
physical_address
varchar(255)
YES
 
NULL
 
postal_address
varchar(255)
YES
 
NULL
 
contact_number
varchar(75)
YES
 
NULL
 
email
varchar(255)
YES
 
NULL
 

MODIFY KEYWORD

The MODIFY Keyword allows you to

1. Modify Column Data Type
2. Modify Column Constraints

In the CHANGE example above, we had to change the field name as well other details. Omitting the field name from the CHANGE statement will generate an error. Suppose we are only interested in changing the data type and constraints on the field without affecting the field name, we can use the MODIFY keyword to accomplish that.

The script below changes the width of "fullname" field from 250 to 50.

ALTER TABLE `members`MODIFY `fullname` char(50) NOT NULL;

Executing the above script in MySQL workbench against myflixdb and then executing the show columns script given above gives the following results shown below.

Field
Type
Null
Key
Default
Extra
membership_number
int(11)
NO
PRI
NULL
auto_increment
fullnames
char(50)
NO
 
NULL
 
gender
varchar(6)
YES
 
NULL
 
date_of_birth
date
YES
 
NULL
 
physical_address
varchar(255)
YES
 
NULL
 
postal_address
varchar(255)
YES
 
NULL
 
contact_number
varchar(75)
YES
 
NULL
 
email
varchar(255)
YES
 
NULL
 

AFTER KEYWORD

Suppose that we want to add a new column at a specific position in the  table.

We can use the alter command together with the AFTER keyword.

The script below adds "date_of_registration" just after the date of birth in the members table.

ALTER TABLE  `members` ADD  `date_of_registration` date NULL AFTER  `date_of_birth`;

Executing the above script in MySQL workbench against myflixdb and then executing the show columns script given above gives the following results shown below.

Field
Type
Null
Key
Default
Extra
membership_number
int(11)
NO
PRI
NULL
auto_increment
fullnames
char(50)
NO
 
NULL
 
gender
varchar(6)
YES
 
NULL
 
date_of_birth
date
YES
 
NULL
 
date_of_registration
date
YES
 
NULL
 
physical_address
varchar(255)
YES
 
NULL
 
postal_address
varchar(255)
YES
 
NULL
 
contact_number
varchar(75)
YES
 
NULL
 
email
varchar(255)
YES
 
NULL
 


Note: The Hilighted row is added after date_of_birth cloumn

Summary

• The alter command is used when we want to modify a database or any object contained in the database.
• The drop command is used to delete databases from MySQL server or objects within a database.
• The rename command is used to change the name of a table to a new table name.
• The Change keyword allows you to change a column name , data type and constraints
• The Modify Keyword allows you to modify a column data type and constraints
• The After keyword is used to specify position of a column in a table

Read More