MySQL Joins – Understand Inner and Outer Joins Query
18
Aug

MySQL Joins – Understand Inner and Outer Joins Query

JOINS in MySQL

Joins means to combine and execute two or more tables and get output of those tables by single query. To use joins, tables should be connected by a related column between them called foreign key. Joins reduce the execution time of query in MySQL. It improves the performance of database.  It executes faster than the normal query execute.

Tables are mainly joined on the basis of primary key and foreign key. One table has a primary key and another table has a foreign key which has a primary key in first table. By using primary key and foreign key, we make the relationship between two tables. These foreign key and primary key helps us to use joins between two or more tables. Joins gives exactly the data you want from any number of tables by writing just one query. It reduces the burden of queries on the database.

So, lets understand this interesting topic by some simple examples.

TYPE OF JOINS : JOINS are of two types:

1) INNER JOIN

2) OUTER JOIN : OUTER JOIN are of three types:

2.1) LEFT OUTER JOIN OR LEFT JOIN

2.2) RIGHT OUTER JOIN OR RIGHT JOIN

2.3) FULL OUTER JOIN OR FULL JOIN

1) INNER JOIN: INNER JOIN returns common data/records from two or more tables. We can use multiple INNER JOIN on Multiple tables in one query.

Example:

SELECT UD.UniversityName, CD.CollegeName, ED.Percentage, ED.StudenName from educationdetails ED
INNER JOIN universitydetails UD
ON ED.UniversityID = UD.UniversityID
INNER JOIN collegedetails CD
ON ED.CollegeID = CD.CollegeID
WHERE ED.ID = 7;

2.1) LEFT OUTER JOIN OR LEFT JOIN: LEFT JOIN returns common data/records from two or more tables and get left table full data/records. We can use LEFT JOIN at that time also if I don’t have any common data/records. So, it takes only LEFT table data/records.

Example:

SELECT UD.UniversityName, CD.CollegeName, ED.Percentage, ED.StudenName, ED.PassingYear 
from educationdetails ED
LEFT JOIN universitydetails UD
ON ED.UniversityID = UD.UniversityID
INNER JOIN collegedetails CD
ON ED.CollegeID = CD.CollegeID
WHERE ED.ID = 7;

2.2) RIGHT OUTER JOIN OR RIGHT JOIN: RIGHT JOIN returns common data/records from two or more tables and get right table full data/records. We can use RIGHT JOIN at that time also if I don’t have any common data/records. So, It takes only RIGHT table data/records.

Example:

SELECT UD.UniversityName, UD.UniversityCode, CD.CollegeName from universitydetails UD
RIGHT JOIN collegedetails CD
ON UD.CollegeID = CD.CollegeID
WHERE UD.UniversityID = 7;

2.3) FULL OUTER JOIN OR FULL JOIN: FULL JOIN return full data/records from both the table which is common or not common in both the tables.

Example:

SELECT UD.UniversityName, UD.UniversityCode, UD.UniversityAddress, CD.CollegeName, CD.CollegeCode, CD.CollegeAddress 
from universitydetails UD
FULL JOIN collegedetails CD
ON UD.CollegeID = CD.CollegeID
WHERE UD.UniversityID = 7;

USE JOINS IN CAKEPHP 3

Cakephp provides us their own function to execute join queries:

Example:

$this->University->find('all')
->hydrate(false)
->join(['College' => [
'table' => 'collegedetails',
'type' => 'INNER', 
'fields' => ['UniversityName','UnversityCode','CollegeName']
'conditions' => ['College.CollegeID = University.CollegeID'],
]])
->where('UniversityID'=>7)->toArray();
Example:

$this->University->find('all')
->hydrate(false)
->join(['College' => [
'table' => 'collegedetails',
'type' => 'LEFT', 
'fields' => ['UniversityName','UnversityCode','UniversityAddress','CollegeName']
'conditions' => ['College.CollegeID = University.CollegeID'],           
]])
->where('UniversityID'=>7)->toArray();

That’s all about JOINS, its really very helpful in complex relational database structure.

In this blog, you learnt about join in MySQL. Joins are not used only for two tables but it can also be used for multiple tables. If you are going to implement in your website development project and need help, feel free to contact us.

share
Get your Mobile App Development for Tourism

Get your Mobile App Development for Tourism

previous-blog-arrowPrevious
Firebase Realtime Database - Installation and Setup

Firebase Realtime Database - Installation and Setup

next-blog-arrowNext