MySQL SubQuery 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.

MySQL Index - Create, Add & Drop

 What are Index?

Nobody likes slow systems.
High system performance is of prime importance in almost all database systems .
Most businesses invest heavily in hardware so that data retrievals and manipulations can be faster.
But there is limit to hardware investments a business can make. 
Optimizing your database is a cheaper and better solution.
Towards this end we can use INDEXES.

• The slowness in the response time is usually due to the records being stored randomly in database tables.
• Search queries have to loop through the entire randomly stored records one after the other to locate the desired data.
• This results in poor performance databases when it comes to retrieving data from large tables
• Indexes come in handy in such situations. Indexes sort data in an organized sequential way.Think of an index as an alphabetically sorted list. It is easier to lookup names that have been sorted in alphabetical order than ones that are not sorted.
• INDEX's are created on the column(s) that will be used to filter the data.
• Using indexes on tables that are frequently updated can result in poor performance. This is because MySQL creates a new index block every time that data is added or updated in the table. Generally, indexes should be used on tables whose data does not change frequently but is used a lot in select search queries.

 

Create index basic syntax

Indexes can be defined in 2 ways
1.       At the time of table creation
2.       After table has been created
 
Example:
For our myflixdb we expect lots of searches to the database on full name.
We will add the "full_names" column to Index in a new table "members_indexed".
The script shown below helps us to achieve that.

CREATE TABLE `members_indexed` (
  `membership_number` int(11) NOT NULL AUTO_INCREMENT,
  `full_names` varchar(150) DEFAULT NULL,
  `gender` varchar(6) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `physical_address` varchar(255) DEFAULT NULL,
  `postal_address` varchar(255) DEFAULT NULL,
  `contact_number` varchar(75) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`membership_number`),INDEX(full_names)
) ENGINE=InnoDB;

Execute the above SQL script in MySQL workbench against the "myflixdb".  

Refreshing the myflixdb shows the newly created table named members_indexed.

"Note" members_indexed table has "full_names" in the indexes node.

As the members base expand and the number of records increases , search queries on the members_indexed table that use the WHERE and ORDER BY clauses will be much faster compared to the ones performed the members table without the index defined.
 

Add index basic syntax

The above example created the index when defining the database table. Suppose we already have a table defined and search queries on it are very slow. They take too long to return the results. After investigating the problem, we discover that we can greatly improve the system performance by creating INDEX on the most commonly used column in the WHERE clause.
 We can use following query to add index

CREATE INDEX id_index ON table_name(column_name);

Let's suppose that search queries on the movies table are very slow and we want to use an index on the "movie title" to speed up the queries, we can use the following script to achieve that.

CREATE INDEX `title_index` ON `movies`(`title`);

Executing the above query creates an index on the title field in the movies table.
This means all the search queries on the movies table using the "title" will be faster.
Search queries on other fields in the movies table will however still are slower compared to the ones based on the indexed field.

Note you can create indexes on multiple columns if necessary depending on the fields that you intend to use for your database search engine.
If you want to view the indexes defined on a particular table, you can use the following script to do that.

SHOW INDEXES FROM table_name;

Let's now take a look at all the indexes defined on the movies table in the myflixdb.

SHOW INDEXES FROM `movies`;

Executing the above script in MySQL workbench against the myflixdb gives us the following results shown below.
Note the primary and foreign keys on the table have already been indexed by MySQL. Each index has its own unique name and the column on which it is defined is shown as well.

Drop index basic syntax

The drop command is used to remove already defined indexes on a table.
There may be times when you have already defined an index on a table that is frequently updated. You may want to remove the indexes on such a table to improve the UPDATE and INSERT queries performance. The basic syntax used to drop an index on a table is as follows.

DROP INDEX `index_id` ON `table_name`;

Let's now look at a practical example.

DROP INDEX ` full_names` ON `members_indexed`;

Executing the above command drops the index with id ` full_names ` from the members_indexed table.

Summary

• Indexes are very powerful when it comes to greatly improving the performance of MySQL search queries.
• Indexes can be defined when creating a table or added later on after the table has already been created.
• You can define indexes on more than one column on a table.
• The SHOW INDEX FROM table_name is used to display the defined indexes on a table.
• The DROP command is used to remove a defined index on a given table.

MySQL UNION

 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

First Application using MySQL and PHP

What is PHP?

PHP is a general purpose server side scripting language that we can use to develop dynamic web sites and applications. PHP only needs to be installed on the web server that will host the web application and client applications can access the server resources via web browsers. The diagram shown below illustrates the basic architecture of a PHP web application.

Why PHP?

You have obviously head of a number of programming languages out there; you may be wondering why we would want to use PHP over other languages to develop our video library application? PHP just like MySQL is open source and free, you don't need to pay for you to get PHP; all you have to do is download it. Packages such as XAMPP come bundled already with a web server, MySQL and PHP among others. This is also unbelievably free. This makes PHP a cost effective choice compared to other scripting languages such as CFML or ASP.
Other benefit that you get with PHP is that it's a server side scripting language; this means you only need to install it on the server and client computers requesting for the resources from the server do not need to have PHP installed; only a web browser would be enough.
PHP also has in built support for working hand in hand with MySQL; this doesn't mean you can't use PHP with other database management systems.
PHP is cross platform which means you can deploy your application on a number of different operating systems such as windows, Linux, Mac OS etc.

PHP and MySQL

PHP and MySQL are like two different sides of the same coin. Just like MySQL has built in functions for data manipulations, so does PHP has built in functions for connecting to MySQL server and manipulating the data in the database. Let's now look at some of PHP functions that allow us to manipulate MySQL databases
MySQL connect

$dh = mysql_connect(servername,username,password);

HERE

• "mysql_connect" is the PHP built in function for connecting to MySQL database
• "servername" is the name of the server running MySQL server.
• "username" is the name of the user that we will use to authenticate ourselves when connecting to the server.
• "password" is the password that we will use to authenticate ourselves when connecting to the server.

PHP Data Object [PDO]
Alternatively we can also use the PHP Data Object PDO to connect to MySQL server. It has the following basic syntax.

$dbconn = new PDO(mysql:host=servername;dbname=databasename,username,password);

HERE

• "dbconn" is the database connection object that we can use to manipulate our database.
• "new PDO (...)" creates an instance of the PDO class.
• "mysql:host=servername;" is the name of the server running MySQL server.
• "dbname=databasename" is the name of the database to be manipulated from PHP.
• "username,password" are login credentials we will use to authenticate ourselves.

As you can see from the above database connection examples, not only is PHP simple to use but it's also flexible. The diagram shown below illustrates how the client application, PHP powered web server and MySQL interact.
 

Introducing Myflix Video Library Application

Assuming we have acquired knowledge of the PHP language combined with what we just learnt in the MySQL tutorial series, we can write the web based application in PHP that runs on top of the MySQL database to provide our Myflix database users with a Visual Graphical User Interface for interactions.

Myflix Video Library Screenshots

The screenshots below show how the video library application running on top of Myflixdb would look like.

 
Members listing window shown above

 
Movie details editing window shown above

Summary

• MySQL is a powerful open source database engine that can meet the requirements of both stand-alone as well as client server applications.
• PHP is a general purpose scripting language that can be used to develop client applications that run on top of MySQL databases.
• Equipping yourself with both MySQL and PHP knowledge is double blessing.

MySQL JOINS: INNER, OUTER, LEFT, RIGHT, CROSS

 What are JOINS?

Joins help retrieving data from two or more database tables. 

The tables are mutually related using primary and foreign keys.

Note: JOIN is the most misunderstood topic amongst SQL leaners. For sake of simplicity and ease of understanding , we will be using a new Database to practice sample.  As shown below

id	first_name	last_name	movie_id
1	Adam	Smith	1
2	Ravi	Kumar	2
3	Susan	Davidson	5
4	Jenny	Adrianna	8
6	Lee	Pong	10

id	title	category
1	ASSASSIN'S CREED: EMBERS	Animations
2	Real Steel(2012)	Animations
3	Alvin and the Chipmunks	Animations
4	The Adventures of Tin Tin	Animations
5	Safe (2012)	Action
6	Safe House(2012)	Action
7	GIA	18+
8	Deadline 2009	18+
9	The Dirty Picture	18+
10	Marley and me	Romance

 

Types of joins

 
Cross JOIN
Cross JOIN is a simplest form of JOINs which matches each row from one database table to all rows of another.
In other words it gives us combinations of each row of first table with all records in second table.
Suppose we want to get all member records against all the movie records, we can use the script shown below to get our desired results.

SELECT * FROM `movies` CROSS JOIN `members`

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

id
title
 
id
first_name
last_name
movie_id
1
ASSASSIN'S CREED: EMBERS
Animations
1
Adam
Smith
1
1
ASSASSIN'S CREED: EMBERS
Animations
2
Ravi
Kumar
2
1
ASSASSIN'S CREED: EMBERS
Animations
3
Susan
Davidson
5
1
ASSASSIN'S CREED: EMBERS
Animations
4
Jenny
Adrianna
8
1
ASSASSIN'S CREED: EMBERS
Animations
6
Lee
Pong
10
2
Real Steel(2012)
Animations
1
Adam
Smith
1
2
Real Steel(2012)
Animations
2
Ravi
Kumar
2
2
Real Steel(2012)
Animations
3
Susan
Davidson
5
2
Real Steel(2012)
Animations
4
Jenny
Adrianna
8
2
Real Steel(2012)
Animations
6
Lee
Pong
10
3
Alvin and the Chipmunks
Animations
1
Adam
Smith
1
3
Alvin and the Chipmunks
Animations
2
Ravi
Kumar
2
3
Alvin and the Chipmunks
Animations
3
Susan
Davidson
5
3
Alvin and the Chipmunks
Animations
4
Jenny
Adrianna
8
3
Alvin and the Chipmunks
Animations
6
Lee
Pong
10
4
The Adventures of Tin Tin
Animations
1
Adam
Smith
1
4
The Adventures of Tin Tin
Animations
2
Ravi
Kumar
2
4
The Adventures of Tin Tin
Animations
3
Susan
Davidson
5
4
The Adventures of Tin Tin
Animations
4
Jenny
Adrianna
8
4
The Adventures of Tin Tin
Animations
6
Lee
Pong
10
5
Safe (2012)
Action
1
Adam
Smith
1
5
Safe (2012)
Action
2
Ravi
Kumar
2
5
Safe (2012)
Action
3
Susan
Davidson
5
5
Safe (2012)
Action
4
Jenny
Adrianna
8
5
Safe (2012)
Action
6
Lee
Pong
10
6
Safe House(2012)
Action
1
Adam
Smith
1
6
Safe House(2012)
Action
2
Ravi
Kumar
2
6
Safe House(2012)
Action
3
Susan
Davidson
5
6
Safe House(2012)
Action
4
Jenny
Adrianna
8
6
Safe House(2012)
Action
6
Lee
Pong
10
7
GIA
18+
1
Adam
Smith
1
7
GIA
18+
2
Ravi
Kumar
2
7
GIA
18+
3
Susan
Davidson
5
7
GIA
18+
4
Jenny
Adrianna
8
7
GIA
18+
6
Lee
Pong
10
8
Deadline(2009)
18+
1
Adam
Smith
1
8
Deadline(2009)
18+
2
Ravi
Kumar
2
8
Deadline(2009)
18+
3
Susan
Davidson
5
8
Deadline(2009)
18+
4
Jenny
Adrianna
8
8
Deadline(2009)
18+
6
Lee
Pong
10
9
The Dirty Picture
18+
1
Adam
Smith
1
9
The Dirty Picture
18+
2
Ravi
Kumar
2
9
The Dirty Picture
18+
3
Susan
Davidson
5
9
The Dirty Picture
18+
4
Jenny
Adrianna
8
9
The Dirty Picture
18+
6
Lee
Pong
10
10
Marley and me
Romance
1
Adam
Smith
1
10
Marley and me
Romance
2
Ravi
Kumar
2
10
Marley and me
Romance
3
Susan
Davidson
5
10
Marley and me
Romance
4
Jenny
Adrianna
8
10
Marley and me
Romance
6
Lee
Pong
10

 

INNER JOIN

The inner JOIN is used to return rows from both tables that satisfy the given condition.
Suppose , you want to get list of members who have rented movies together with titles of movies rented by them. You can simply use an INNER JOIN for that, which returns rows from both tables that satisfy with given conditions.

 

SELECT members.`first_name` , members.`last_name` , movies.`title`
FROM members ,movies
WHERE movies.`id` = members.`movie_id`

Executing the above script give

 

first_name
last_name
title
Adam
Smith
ASSASSIN'S CREED: EMBERS
Ravi
Kumar
Real Steel(2012)
Susan
Davidson
Safe (2012)
Jenny
Adrianna
Deadline(2009)
Lee
Pong
Marley and me

Note the above results script can also be written as follows to achieve the same results.

SELECT A.`first_name` , A.`last_name` , B.`title`
FROM `members`AS A
INNER JOIN `movies` AS B
ON B.`id` = A.`movie_id`

Outer JOINs

MySQL Outer JOINs return all records matching from both tables .
It can detect records having no match in joined table. It returns NULL values for records of joined table if no match is found.
Sounds Confusing ? Let's look into an example -

LEFT JOIN

Assume now you want to get titles of all movies together with names of members who have rented them. It is clear that some movies have not being rented by any one. We can simply use LEFT JOIN for the purpose.

 
The LEFT JOIN returns all the rows from the table on the left even if no matching rows have been found in the table on the right. Where no matches have been found in the table on the right, NULL is returned.
 

SELECT A.`title` , B.`first_name` , B.`last_name`
FROM `movies` AS A
LEFT JOIN `members` AS B
ON B.`movie_id` = A.`id`

Executing the above script in MySQL workbench gives.You can see that in the returned result which is listed below that for movies which are not rented, member name fields are having NULL values. That means no matching member found members table for that particular movie.

 

title
first_name
last_name
ASSASSIN'S CREED: EMBERS
Adam
Smith
Real Steel(2012)
Ravi
Kumar
Safe (2012)
Susan
Davidson
Deadline(2009)
Jenny
Adrianna
Marley and me
Lee
Pong
Alvin and the Chipmunks
NULL
NULL
The Adventures of Tin Tin
NULL
NULL
Safe House(2012)
NULL
NULL
GIA
NULL
NULL
The Dirty Picture
NULL
NULL

Note: Null is returned for non-matching rows on right

 

RIGHT JOIN

RIGHT JOIN is obviously the opposite of LEFT JOIN. The RIGHT JOIN returns all the columns from the table on the right even if no matching rows have been found in the table on the left. Where no matches have been found in the table on the left, NULL is returned.
In our example,  let's assume that you need to get names of members and movies rented by them. Now we have a new member who has not rented any movie yet

SELECT  A.`first_name` , A.`last_name`, B.`title`
FROM `members` AS A
RIGHT JOIN `movies` AS B
ON B.`id` = A.`movie_id`

 
Executing the above script in MySQL workbench gives the following results.

first_name
last_name
title
Adam
Smith
ASSASSIN'S CREED: EMBERS
Ravi
Kumar
Real Steel(2012)
Susan
Davidson
Safe (2012)
Jenny
Adrianna
Deadline(2009)
Lee
Pong
Marley and me
NULL
NULL
Alvin and the Chipmunks
NULL
NULL
The Adventures of Tin Tin
NULL
NULL
Safe House(2012)
NULL
NULL
GIA
NULL
NULL
The Dirty Picture

Note: Null is returned for non-matching rows on left

"ON" and "USING" clauses

In above JOIN query examples, we have used ON clause to match the records between table.
USING clause can also be used for the same purpose. The difference with USING is it needs to have identical names for matched columns in both tables.
 
In "movies" table so far we used its primary key with the name "id". We referred to same in  "members" table with the name "movie_id".
 
Let's rename "movies" tables "id" field to have the name "movie_id". We do this in order to have identical matched field names.

ALTER TABLE `movies` CHANGE `id` `movie_id` INT( 11 ) NOT NULL AUTO_INCREMENT;

Next let's use USING with above LEFT JOIN example.

SELECT A.`title` , B.`first_name` , B.`last_name`
FROM `movies` AS A
LEFT JOIN `members` AS B
USING ( `movie_id` )

Apart from using ON and USING with JOINs you can use many other MySQL clauses like GROUP BY, WHERE and even functions like SUM, AVG, etc.
 

Why should we use joins?

Now you may think, why we use JOINs when we can do the same task running queries. Especially if you have some experience in database programming you know we can run queries one by one, use output of each in successive queries. Of course, that is possible. But using JOINs, you can get the work done by using only a one query with any search parameters. On the other hand MySQL can achieve better performance with JOINs as it can use Indexing. Simply use of single JOIN query instead running multiple queries do reduce server overhead. Using multiple queries instead that leads more data transfers between MySQL and applications (software). Further it requires more data manipulations in application end also.
It is clear that we can achieve better MySQL and application performances by use of JOINs.
 

Summary

• JOINS allow us to combine data from more than one table into a single result set.
• JOINS have better performance compared to sub queries
• INNER JOINS only return rows that meet the given criteria.
• OUTER JOINS can also return rows where no matches have been found. The unmatched rows are returned with the NULL keyword.
• The major JOIN types include Inner, Left Outer, Right Outer, Cross JOINS etc.
• The frequently used clause in JOIN operations is "ON". "USING" clause requires that matching columns be of the same name.
• JOINS can also be used in other clauses such as GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS etc.

Views in MySQL

 Create, Join & Drop with Examples 

 What are views?

Simply put, VIEWS are virtual tables .By virtual, we mean, the tables do not store any data of their own but display data stored in other tables.
In other words, VIEWS are nothing but SELECT Queries.

Views syntax

Let's now look at the basic syntax used to create a view in MySQL.

CREATE VIEW `view_name` AS SELECT statement;

WHERE

• "CREATE VIEW `view_name`" tells MySQL server to create a view object in the database named `view_name`
• "AS SELECT statement" is the SQL statements to be packed in the views. It can be a SELECT statement can contain data from one table or multiple tables.

Let's now create our first view using the "myflixdb" we will create a simple view that restricts the columns seen in the members table.
 Suppose authorization requirements  state that the accounts department can only see member's number , name and gender from the member's table. To achieve this e can create a VIEW -

CREATE VIEW `accounts_v_members` AS SELECT `membership_number`,`full_names`,`gender` FROM `members`;

Executing the above script in MySQL workbench against the myflixdb and expanding the views node in the database explorer gives us the following results.

Note the accounts_v_members object is now visible in the database views objects. Let's now execute a SELECT statement that selects all the fields from the view.

SELECT * FROM `accounts_v_members`;

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

membership_number
full_names
gender
1
Janet Jones
Female
2
Janet Smith Jones
Female
3
Robert Phil
Male
4
Gloria Williams
Female
5
Leonard Hofstadter
Male
6
Sheldon Cooper
Male
7
Rajesh Koothrappali
Male
8
Leslie Winkle
Male
9
Howard Wolowitz
Male

Only the authorized columns for accounts department have been returned. Other details found in the members table have been hidden .
If we want to see the SQL statements that make up a particular view, we can use the script shown below to do that.

SHOW CREATE VIEW `accounts_v_members`;

Executing the above script gives you the view name and the SQL SELECT statements used to create the view.

Joins and views

Let's now look at a fairly complex example which involves multiple tables and uses joins.
We will package the JOIN created that gets information from three (3) tables namely members, movies and movie rentals. Below is the script that helps us to achieve that.

CREATE VIEW `general_v_movie_rentals` AS SELECT mb.`membership_number`,mb.`full_names`,mo.`title`,mr.`transaction_date`,mr.`return_date` FROM `movierentals` AS mr INNER JOIN `members` AS mb ON mr.`membership_number` = mb.`membership_number` INNER JOIN `movies` AS mo ON mr.`movie_id` = mo.`movie_id`;

Executing the above scripts creates the view named general_v_movie_rentals in our myflixdb

Let's now select all the fields from a table named general_v_movie_rentals.

SELECT * FROM `general_v_movie_rentals`;

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

membership_number
full_names
title
transaction_date
return_date
1
Janet Jones
Pirates of the Caribean 4
20-06-2012
28-06-2012
1
Janet Jones
Forgetting Sarah Marshal
22-06-2012
25-06-2012
3
Robert Phil
Forgetting Sarah Marshal
22-06-2012
25-06-2012
2
Janet Smith Jones
Forgetting Sarah Marshal
21-06-2012
24-06-2012
3
Robert Phil
X-Men
23-06-2012
28-06-2012

Note we didn't have to write the complex JOIN query to get information about members, movies and movie rental details. We simply used the view in a regular SELECT statement as any other ordinary table. The view can be called from anywhere in the application system running on top of the myflixdb.

Dropping views

The DROP command can be used to delete a view from the database that is no longer required. The basic syntax to drop a view is as follows.

DROP VIEW ` general_v_movie_rentals `;

Why use views?
You may want to use views primarily for following 3 reasons

• Ultimately , you will use your SQL knowledge , to create applications , which will use a  database for data requirements. It's recommended that you use VIEWS of the original table structure in your application instead of using the tables themselves. This ensures that when you make changes to your database structure, your applications will not break.
• VIEWS increase re-usability. You will not have to create complex queries involving joins repeatedly. All the complexity is converted into a single line of query use VIEWS. Such condensed code will be easier to integrate in your application. This will eliminates chances of typos and your code will be more readable.
• VIEWS help in data security. You can use views to show only authorized information to users and hide sensitive data like credit card numbers.

Summary

• Views are virtual tables; they do not contain the data that is returned. The data is stored in the tables referenced in the SELECT statement.
• Views improve security of the database by showing only intended data to authorized users. They hide sensitive data.
• Views make life easy as you do not have write complex queries time and again.
• It's possible to use INSERT, UPDATE  and DELETE on a VIEW. These operations will change the underlying tables of the VIEW.  The only consideration is that VIEW should contain all NOT NULL columns of the tables it references. Ideally, you should not use VIEWS for updating.

MySQL IS NULL & IS NOT NULL

In SQL Null is both a value as well as a keyword. Let's look into NULL value first -

Null as a Value

In simple terms, NULL is simply a place holder for data that does not exist. When performing insert operations on tables, they will be times when some  field values will not be available. 
In order to meet the requirements of true relational database management systems, MySQL uses NULL as the place holder for the values that have not been submitted. The screenshot below shows how NULL values look in database.

Let's now look at some of the basics for NULL before we go further into the discussion.

• NULL is not a data type - this means it is not recognized as an "int", "date" or any other defined data type.
• Arithmetic operations involving NULL always return NULL for example, 69 + NULL = NULL.
• All aggregate functions affect only rows that do not have NULL values.

Let's now demonstrate how the count function treats null values.  Let's see the current contents of the members table-

SELECT * FROM `members`;

Executing the above script gives us the following results

 

membership_ number
full_ names
gender
date_of_ birth
physical_ address
postal_ address
contact_ number
email
1
Janet Jones
Female
21-07-1980
First Street Plot No 4
Private Bag
0759 253 542
janetjones@yagoo.cm
2
Janet Smith Jones
Female
23-06-1980
Melrose 123
NULL
NULL
jj@fstreet.com
3
Robert Phil
Male
12-07-1989
3rd Street 34
NULL
12345
rm@tstreet.com
4
Gloria Williams
Female
14-02-1984
2nd Street 23
NULL
NULL
NULL
5
Leonard Hofstadter
Male
NULL
Woodcrest
NULL
845738767
NULL
6
Sheldon Cooper
Male
NULL
Woodcrest
NULL
976736763
NULL
7
Rajesh Koothrappali
Male
NULL
Woodcrest
NULL
938867763
NULL
8
Leslie Winkle
Male
14-02-1984
Woodcrest
NULL
987636553
NULL
9
Howard Wolowitz
Male
24-08-1981
SouthPark
P.O. Box 4563
987786553
lwolowitz[at]email.me

Let's count all members who have updated their contact_number

SELECT COUNT(contact_number)  FROM `members`;

Executing the above query gives us the following results.

 

COUNT(contact_number)
7

Note: Values that are NULL have not been included

What is NOT?

The NOT logical operator is used to test for Boolean conditions and returns true if the condition is false. The NOT operator returns false if the condition been tested is true

Condition	NOT Operator Result
True	False
False	True

 

Why use NOT null?

There will be cases when we will have to perform computations on a query result set and return the values. Performing any arithmetic operations on columns that have the NULL value returns null results. In order to avoid such situations from happening, we can employ the use of the NOT NULL clause to limit the results on which our data operates.

NOT NULL Values

Let's suppose that we want to create a table with certain fields that should always be supplied with values when inserting new rows in a table. We can use the NOT NULL clause on a given field when creating the table.
The example shown below creates a new table that contains employee's data. The employee number should always be supplied

CREATE TABLE `employees`(
  employee_number int NOT NULL,
  full_names varchar(255) ,
  gender varchar(6)
);

Let's now try to insert a new record without specifying the employee name and see what happens.

INSERT INTO `employees` (full_names,gender) VALUES ('Steve Jobs', 'Male');

Executing the above script in MySQL workbench gives the following error -

NULL Keywords

NULL can also be used as a keyword when performing Boolean operations on values that include NULL. The "IS/NOT" keyword is used in conjunction with the NULL word for such purposes. The basic syntax when null is used as a keyword is as follows

`comlumn_name'  IS NULL
`comlumn_name' NOT NULL

HERE

• "IS NULL" is the keyword that performs the Boolean comparison. It returns true if the supplied value is NULL and false if the supplied value is not NULL.
• "NOT NULL" is the keyword that performs the Boolean comparison. It returns true if the supplied value is not NULL and false if the supplied value is null.

Let's now look at a practical example that uses the NOT NULL keyword to eliminate all the column values that have null values.
Continuing with the example above , suppose we need details of members whose contact number is not null . We can execute a query like

SELECT * FROM `members` WHERE contact_number IS NOT NULL;

Executing the above query gives only records where contact number is not null.
Suppose we want member records where contact number is null. We can use following query

SELECT * FROM `members` WHERE contact_number IS NULL;

 

Executing the above query gives member details whose contact number is NULL

 

membership_ number
full_names
gender
date_of_birth
physical_address
postal_address
contact_ number
email
1
Janet Jones
Female
21-07-1980
First Street Plot No 4
Private Bag
0759 253 542
janetjones@yagoo.cm
3
Robert Phil
Male
12-07-1989
3rd Street 34
NULL
12345
rm@tstreet.com
5
Leonard Hofstadter
Male
NULL
Woodcrest
NULL
845738767
NULL
6
Sheldon Cooper
Male
NULL
Woodcrest
NULL
976736763
NULL
7
Rajesh Koothrappali
Male
NULL
Woodcrest
NULL
938867763
NULL
8
Leslie Winkle
Male
14-02-1984
Woodcrest
NULL
987636553
NULL
9
Howard Wolowitz
Male
24-08-1981
SouthPark
P.O. Box 4563
987786553
lwolowitz[at]email.me

Comparing null values

 
Three-value logic - performing Boolean operations on conditions that involve NULL can either return "Unknown", "True" or "False".
For example, using the "IS NULL" keyword when doing comparison operations involving NULL can either return true or false. Using other comparison operators returns "Unknown"(NULL).
Suppose you compare number five with 5

SELECT 5 =5;

The query result is 1 which means TRUE

 

5 =5
1

Let's do the same operation with NULL

SELECT NULL = NULL;

 

  

NULL = NULL
NULL

Let's look at another example

SELECT 5 > 5;

  

5 > 5
0

The query result is 0 which means FALSE
Let's look at same example using NULL

SELECT NULL > NULL;

 

  

NULL > NULL
NULL

Lets use the IS NULL keyword

SELECT 5 IS NULL;

  

5 IS NULL
0

The query result is 0 which is FALSE

SELECT NULL IS NULL;

  

NULL IS NULL
1

The query result is 1 which is TRUE

Summary

• NULL is a value place holder for optional table fields.
• MySQL treats the NULL value differently from other data types. The NULL values when used in a condition evaluates to the false Boolean value.
• The NOT logical operate is used to test for Boolean values and evaluates to true if the Boolean value is false and false if the Boolean value is true.
• The NOT NULL clause is used to eliminate NULL values from a result set
• Performing arithmetic operations on NULL values always returns NULL results.
• The comparison operators such as [, =, etc.] cannot be used to compare NULL values.