Implementing FULL Join in MySQL

In the previous articles we saw what inner joins, left joins and right joins are and how they are used. Full joins are simplest of all the joins. Full join retrieves all the records from both the right and left tables irrespective of the similar values in the common column. Consider the example of patient and department table. The Full join will retrieve all the patient records and all the department records, if patient doesnt have a corresponding department, the fields for department columns will remain empty. Vice versa will be the case for department, records. An important point to note here is that unlike MS SQLSERVER and some of the other databases, MySQL doesnt support FULL JOIN directly. Rather, we take right join and left join and then take union of both.

This might sound complex at first. Lets again have a look at our patient and department table.

Patient Table

 

patient_id patient_fname patient_lname patient_age dep_id
1 Mike Getting 20 3
2 Sara Taylor 25 2
3 Vince James 52 9
4 Shawn Been 45 6
5 Lara Bran 43 7
6 Fred Vicks 8 5
7 Pam Beesly 38 8

 

Department Table

 

dep_id dep_name dep_capacity
2 Neurology 200
3 Cardiology 300
4 Nephrology 200
5 Children 100
6 Pythology 400
7 Optician 500

 

Let’s implement full join on above two tables and see what result do we get. Have a look at the following examples.

 

   



Implementing RIGHT JOIN



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Implementing RIGHT JOIN

$query = "SELECT patient.patient_fname, patient.patient_age, department.dep_name ".
    "FROM patient LEFT JOIN department ".
    "ON patient.dep_id = department.dep_id ".
    "UNION ".
    "SELECT patient.patient_fname, patient.patient_age, department.dep_name ".
    "FROM patient RIGHT JOIN department ".
    "ON patient.dep_id = department.dep_id ";
     
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]. " - Age: " . $row["patient_age"]. " - Department: " . $row["dep_name"]. "
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

The output of the above code will look like this.

First Name: Mike – Age: 20 – Department: Cardiology
First Name: Sara – Age: 25 – Department: Neurology
First Name: Vince – Age: 52 – Department:
First Name: Shawn – Age: 45 – Department: Pythology
First Name: Lara – Age: 43 – Department: Optician
First Name: Fred – Age: 8 – Department: Children
First Name: Pam – Age: 38 – Department:
First Name: – Age: – Department: Nephrology


It can be seen that all the records have been retrieved from both the patient and department tables and the fields have been left empty where there isnt any record found in the corresponding tables.

RIGHT JOIN In MySQL

In the previous articles we saw what inner joins and left joins are how they are used. Inner joins retrieve data from multiple tables for only those records where there is a common value in a specific field. However, right joins are opposite of left joins. When right join is used for retrieving data from multiple tables, all the records from the right table are retrieved while only those records from the left table are retrieved where there is a common value in the column on which Join is being implemented.

This might sound complex at first. Lets again have a look at our patient and department table.

Patient Table

 

patient_id patient_fname patient_lname patient_age dep_id
1 Mike Getting 20 3
2 Sara Taylor 25 2
3 Vince James 52 9
4 Shawn Been 45 6
5 Lara Bran 43 7
6 Fred Vicks 8 5
7 Pam Beesly 38 8

 

Department Table

 

dep_id dep_name dep_capacity
2 Neurology 200
3 Cardiology 300
4 Nephrology 200
5 Children 100
6 Pythology 400
7 Optician 500

 

You can see from the ‘Patient’ and ‘Department’ tables that they both have dep_id field common, so now if we use RIGHT join to retrieve data from both the columns, all the records from the department table will be retrieved and only those records from patient table will be retrieved where there exist common values for dep_id in both the ‘Patient’ and ‘Department’ tables. Have a look at the following examples.

 

   


Implementing RIGHT JOIN



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Implementing RIGHT JOIN

$query = "SELECT patient.patient_fname, patient.patient_age, department.dep_name ".
    "FROM patient RIGHT JOIN department ".
    "ON patient.dep_id = department.dep_id";
     
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]. " - Age: " . $row["patient_age"]. " - Department: " . $row["dep_name"]. "
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

The output of the above code will look like this.

First Name: Mike – Age: 20 – Department: Cardiology
First Name: Sara – Age: 25 – Department: Neurology
First Name: Shawn – Age: 45 – Department: Pythology
First Name: Lara – Age: 43 – Department: Optician
First Name: Fred – Age: 8 – Department: Children
First Name: – Age: – Department: Nephrology


It can be seen that all the records have been retrieved from department table while  only those columns have been retrieved from patient table where there was a common value for dep_id column in both the tables.

LEFT JOIN in MySQL

In the last article we saw what inner joins are how they are used. Inner joins retrieve data from multiple tables for only those records where there is a common value in a specific field. However, Left join is slightly different. When left join is used for retrieving data from multiple tables, all the records from the Left tables are retrieved while only those records from the right table are retrieved where there is a common value in the column on which Join is being implemented.

This might sound complex at first. Lets again have a look at our patient and department table.

Patient Table

 

patient_id patient_fname patient_lname patient_age dep_id
1 Mike Getting 20 3
2 Sara Taylor 25 2
3 Vince James 52 9
4 Shawn Been 45 6
5 Lara Bran 43 7
6 Fred Vicks 8 5
7 Pam Beesly 38 8

 

Department Table

 

dep_id dep_name dep_capacity
2 Neurology 200
3 Cardiology 300
4 Nephrology 200
5 Children 100
6 Pythology 400
7 Optician 500

 

You can see from the ‘Patient’ and ‘Department’ tables that they both have dep_id field common, so now if we use LEFT join to retrieve data from both the columns, all the records from the patient table will be retrieved and only those records from department table will be retrieved where there exist common values for dep_id in both the ‘Patient’ and ‘Department’ tables. Have a look at the following examples.

 

   


Implementing LEFT JOIN



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Implementing LEFT JOIN

$query = "SELECT patient.patient_fname, patient.patient_age, department.dep_name ".
    "FROM patient LEFT JOIN department ".
    "ON patient.dep_id = department.dep_id";
     
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]. " - Age: " . $row["patient_age"]. " - Department: " . $row["dep_name"]. "
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

The output of the above code will look like this.

First Name: Mike – Age: 20 – Department: Cardiology
First Name: Sara – Age: 25 – Department: Neurology
First Name: Vince – Age: 52 – Department:
First Name: Shawn – Age: 45 – Department: Pythology
First Name: Lara – Age: 43 – Department: Optician
First Name: Fred – Age: 8 – Department: Children
First Name: Pam – Age: 38 – Department:


It can be seen that all the records have been retrieved from patient table while  only those columns have been retrieved from department table where there was a common value for dep_id column in both the tables.

INNER JOIN in MySQL

Till now, we have been retrieving data from a single table. However, in relational databases such as MySQL tables are related with each other via some common field. Take an example of a ‘patient’ and ‘department’ table in the hospital database. A patient is admitted to a specific department of the hospital on the other hand a department can have many patients admitted in it. In such cases, the relational between the two columns is defined via some common field.

For instance, the relationship between patient and department table can be established by adding a dep_id column in both the tables. This dep_id column will serve as primary key for the department table, and for the patient table this dep_id will serve as foreign key.

Now, let’s come to our topic, which is JOIN in MySQL. JOIN statement is used to retrieve data simultaneously from two or more than two tables based on some common field between them. Suppose, we have patient and department tables which have following data.

 

Patient Table

 

patient_id patient_fname patient_lname patient_age dep_id
1 Mike Getting 20 3
2 Sara Taylor 25 2
3 Vince James 52 9
4 Shawn Been 45 6
5 Lara Bran 43 7
6 Fred Vicks 8 5
7 Pam Beesly 38 8

 

Department Table

 

dep_id dep_name dep_capacity
2 Neurology 200
3 Cardiology 300
4 Nephrology 200
5 Children 100
6 Pythology 400
7 Optician 500

 

You can see from the ‘Patient’ and ‘Department’ tables that they both have dep_id field common, so now if we use INNER join to retrieve data from both the columns, only those records will be retrieved where there exist common values for dep_id in both the ‘Patient’ and ‘Department’ tables. Have a look at the following examples.

 




Implementing INNER JOIN



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Implementing INNER JOIN

$query = "SELECT patient.patient_fname, patient.patient_age, department.dep_name ".
    "FROM patient INNER JOIN department ".
    "ON patient.dep_id = department.dep_id";
     
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]. " - Age: " . $row["patient_age"]. " - Department: " . $row["dep_name"]. "
"; } } else { echo "0 results"; } $connection->close(); ?>

 

Download the code

To select data from multiple table, you have to prefix the table name with the column you want to retrieve using the dot operator. The output of the above code will look like this.

 

First Name: Mike – Age: 20 – Department: Cardiology
First Name: Sara – Age: 25 – Department: Neurology
First Name: Shawn – Age: 45 – Department: Pythology
First Name: Lara – Age: 43 – Department: Optician
First Name: Fred – Age: 8 – Department: Children

 

You can see that only those columns have been retrieved where there was a common value for dep_id column in both the tables. The record for Vince and Pam has not been retrieved because the values for their dep_id column did not exist in the dep_id column of department table.

Implementing Check on Table Columns

In the last article, we saw how we can specify default value for a column and in case if that column is left empty while data insertion, the default value was entered for that column. In this article, we shall see how we can implement checks on specific columns of a table.

A check is basically a limit on the values that can be stored in the column. For instance you can specify that a specific column can only contain a value between 100 and 200 hundred etc. The syntax of CHECK statement is as follows:


CREATE TABLE table_name
(
column1 int NOT NULL,
column2 varchar(255) NOT NULL,
column3 int DEFAULT 100
CHECK (column1>0)
)

The above code is imposing a check in column1 of the table by specifying that it can only contain a value greater than 0. Let’s see a more real world example. We shall create a patient table with values patient_id, patient_fname, patient_lname, patient_age and dep_id. We shall implement a check on patient_age column that it should contain a value greater than 0. Have a look at the following example.




Using MySQLi for Check Implementation on columns



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 

$query = "Use Hospital";
$connection->query($query);
    
// Implementing Check on Columns
$query = "CREATE TABLE Patient (
patient_id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
patient_fname VARCHAR(50) NOT NULL,
patient_lname VARCHAR(50) NOT NULL,
patient_age INT(10),
dep_id INT(10),
CHECK (patient_age > 0)
);";
if ($connection->query($query) === TRUE) {
    echo "Successful table creation";
} else {
    echo "Unable to create table" . $connection->error;
}

$connection->close();
?>
    



Download the code

DEFAULT Statement in MySQL

While creating a table in MySQL, you can do a lot more than just specifying the number of columns, their data type and if they hold null values. One option is to specify the default value for a specific column if that column is left empty when the record was inserted. In MySQL, the DEFAULT clause is used for this purpose. The syntax of the DEFAULT clause is as follows:


CREATE TABLE table_name
(
column1 int NOT NULL,
column2 varchar(255) NOT NULL,
column3 int DEFAULT 100
)

In the above code, the column3 will have default value of 100 if no value is specified for this column when the record was inserted.

Now, lets move towards a more practical example. Let us create a new table named ‘department’, the table will have three columns, dep_id, dep_name and dep_capacity. The third column i.e. dep_capacity will have a default value of 100. Have a look at the following example.




Using DEFAULT statement in MySQL Table



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 

$query = "Use Hospital";
$connection->query($query);
    
// Using Default Statement in MySQL
$query = "CREATE TABLE Department (
dep_id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
dep_name VARCHAR(50) NOT NULL,
dep_capacity INT(10) DEFAULT 100
);";
if ($connection->query($query) === TRUE) {
    echo "Successful table creation";
} else {
    echo "Unable to create table" . $connection->error;
}

$connection->close();
?>
        




Download the code

If you run the above code, a table named department will be created in the hospital database. Now, if you insert some data into this department table and leave the value for dep_capacity column as empty, you will see that the default value of 100 is assigned to the column. You can verify this by selecting all records via select statement.

INSERT INTO SELECT in MySQL

Data stored in a database table can be destroyed or corrupted due to any reason. Therefore, it is convenient to take preemptive measures such as creating backup of data stored in a database table. In MySQL this is done by using INSERT INTO statement in conjunction with SELECT The following syntax is used for copying data from one table to the other in MySQL:


"INSERT INTO 'newtable' SELECT * FROM `oldtable`"

The above code will copy all the data from the oldtable to the newtable. It is important to note here that the schema of both newtable and oldtable should be same. Let’s create backup of the data in our patient table by copying the data into a new table named patient2 which exists in the same database. To do so in mysqli, simply pass the query for copying data to the query function of the mysqli object. Have a look at the following example.




Backing UP Data in Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Copying data from one table to the other.

$query = "INSERT INTO `patient2` SELECT * FROM `patient`";
$output = $connection->query($query);

$connection->close();
?>
      



Download the code

GROUP BY and HAVING Clauses in MySQL

The GROUP BY clause is used to group the output of the aggregate functions with respect to some specific column. Consider a scenario where you want to group all the patients with same age into one group and then get the count of all the patient groups with respect to age. In such scenarios you can use GROUP BY statements. The syntax of GROUP BY statement is as follows.

SELECT column, AggFunc(column)
FROM table
GROUP BY column;

In the following example, we shall first group all the patients with same age into one group. This will give us multiple groups with respect to age. After that we shall apply Count aggregate function on each group to get the total number of patient in each group. The number of rows returned will be equal to number of groups. Each row will have two columns: One for the age and the other for the number of patients having that age. Have a look at the following code.




Using GROUP BY Function in Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using GROUP BY Function

$query = "SELECT patient_age, COUNT(patient_fname) as 'tpatients' ".
"FROM Patient ".
"GROUP BY patient_age";

$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "Age " . $row["patient_age"]." Total Patients " . $row["tpatients"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

HAVING Clause

With simple MySQL queries, WHERE statement is used. However, with GROUP BY clause, HAVING statement is used to filter data. For instance, if we want to group all the patients with age greater than 35, we can extend the previous example to include HAVING statement as follows.




Using GROUP BY with HAVING Clause in Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using GROUP BY with HAVING Clause

$query = "SELECT patient_age, COUNT(patient_fname) as 'tpatients' ".
"FROM Patient ".
"GROUP BY patient_age ".
"HAVING patient_age > 35";

$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "Age " . $row["patient_age"]." Total Patients " . $row["tpatients"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

Using COUNT Function in MySQL

This article explains how we can count number of values in a specific column where the values meet a specific criteria. To do so, the COUNT function is used in MySQL

The syntax of the COUNT function is as follows

SELECT COUNT(column)
FROM table
WHERE condition1, condition2 ....;

To number of all the values that meet specific criteria using COUNT function in MySQLi, pass the query for finding COUNT to the query function of the mysqli object. The following example counts the number of values for patient_age column where value is greater than or equal to 40.




Using COUNT Function in Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using COUNT Function

$query = "SELECT COUNT(patient_age) ".
"FROM Patient ".
"WHERE patient_age >= 40";

$output = $connection->query($query);
$count = $output->fetch_row();

echo "Total number of retrieved rows is ". $count[0];

$connection->close();
?>
       



Download the code

In the above example the result returned by the query is stored in the first index of the array returned by the fetch_row() method. If you remove the where clause in the above query, COUNT will return total number of all the values in the patient_age column.

UCASE and LCASE Functions in MySQL

This article explains how we can change the case of values from any column from the MySQL table. To convert all the column values to uppercase the UCASE() function is used. Similarly to convert values to lowercase, the LCASE function is used.

The UCASE() function

The syntax of UCASE function is as follows:

SELECT UCASE(column)
FROM table;

To convert all the values in a specific column of any table to uppercase using UCASE in MySQLi, pass the query for changing case to the query function of the mysqli object. The following example converts all values in patient_fname column to uppercase




Using UCASE Function in Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using UCASE Function

$query = "SELECT UCASE(patient_fname) ".
"FROM Patient ";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "Patient Firstname " . $row["UCASE(patient_fname)"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

The LCASE() function

The syntax of LCASE function is as follows:

SELECT LCASE(column)
FROM table;

To convert all the values in a specific column of any table to lowercase using LCASE in MySQLi, pass the query for changing case to the query function of the mysqli object. The following example converts all values in patient_fname column to lowercase




Using LCASE Function in Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using LCASE Function

$query = "SELECT LCASE(patient_fname) ".
"FROM Patient ";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "Patient Firstname " . $row["LCASE(patient_fname)"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

Copyright 2005-2016 KnowledgeHills. Privacy Policy. Contact .