Aug
MySQL Joins – Understand Inner and Outer Joins Query
JOINS in MySQL
Joins mean combining and executing two or more tables and getting the tables’ output with a single query. To use joins, tables should be connected by a related column between them called a foreign key. Joins reduce the execution time of queries in MySQL. It improves the performance of the database. It executes faster than a normal query.
Tables are mainly joined on the basis of primary keys and foreign keys. One table has a primary key, and another table has a foreign key that has a primary key in the first table. By using the primary key and foreign key, we establish the relationship between the two tables. These foreign keys and primary keys help us use joins between two or more tables. Joins give you 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, let’s understand this interesting topic with some simple examples.
TYPE OF JOINS: JOINS are of two types:
1) INNER JOIN
2) OUTER JOIN: OUTER JOIN is 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 the 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 the 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 returns full data/records from both tables, which is common or not common in both 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 with its 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, it’s really very helpful in complex relational database structures.
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 your website development project and need help, feel free to contact us.