What is JOIN and Different types of JOIN Operations in MySQL?

Direct Switch to Contents

1. What is JOIN in MySQL?
   • Self Join
   • How did databases join? Mechanics behind it?
2. Types Of Joins
   • INNER JOIN
   • OUTER JOIN
   • LEFT JOIN
   • RIGHT JOIN
   • FULL JOIN
   • CROSS JOIN
3. CONCLUSION
4. FAQs

What is JOIN in MySQL?

A JOIN, therefore, is an operation that matches rows from one table to the rows in another. The matching is done in such a manner that the columns from both the tables placed side by side although they may have come from separate tables.

For example: Let’s create tables: CUSTOMERS, ORDERS, and EMPLOYEES as follows:
Table 1->CUSTOMERS ( cust_id [PK] , cust_fname, cust_lname, contact_person, phone, address, city,state, pin_code, country, sales_representive, credit_limit.

Table 2->ORDERS ( order_id [PK] , order_date, reqd_date, ship_date, status, other_info, cust_id [FK] );

Table 3->EMPLOYEES( emp_id [PK] , emp_fname, emp_lname, email, job_title);

We can join the table using a MySQL query to obtain the list of customers who have placed one or more orders with the list of dates the order has been placed:

SELECT customers.cust_fname, orders.order_id, orders.order_date
FROM customers
JOIN orders on customer.cust_id = orders.cust_id ;

SELF-JOIN:

It is a special case, table (base table, view, or joined table) can JOIN to itself in a self-join.
A self-join is joining a table to itself;
SQL instruction to combine data from two sets of data, the word relational is a key, it specifies that the DBMS is organized in such a way that there is clear relation defined between sets of data.
A QUERY can contain zero one or multiple JOINS.
For a programmer, JOIN statement is just to recognize or identify the two rows for joining in such a way that if the evaluated predicate is true, the combined row is then produced in the expected format, a set, or a temporary table.

JOINS aren’t used to exact matches. a common join pattern used to join the primary key of the one table to its foreign key.A common join pattern used to join the primary and foreign keys. Through that process, we break up dependencies within tables to eliminate update anomalies among other things, but in order to keep relationships, we introduce foreign keys.

These types of databases make it really easy to create tables of data and a facility to relate (join or combine) the data together.

Relational databases are usually normalized to eliminate duplication of information such as when entity types have one-to-many relationships.
For example, a department may be associates with a number of employees. Joining separate tables for department and Employee effectively creates another table which combines the information from both tables.

As requirements are cast into table designs, they are laid up against some best practices to minimize data quality issues. This process is normalization and it helps each table achieve singular meaning and purpose.

For instance, if I had a table containing all the students and their classes, then wanted to change a student’s name, I would have to change it multiple times, once for each class the student enrolled in.

How databases joined? Mechanics behind it?

It is pretty straightforward.
To perform a join you need two items: two tables and a join condition. The tables contain the rows to combine, and the join conditions the instructions to match rows together.

JOIN condition:
It is to be noted that a join is specified by an SQL SELECT statement with conditions in the FROM clause. The FROM clause takes all row in all the tables listed and forms a new table which contains all combination of the original rows.

In conclusion, The join operations can vary in form based upon the JOIN clauses you apply. This directly affects the output produces by the query. For example, we can retrieve query results by including only those rows that match or unmatched the rows from left, right, or both the table.

Types Of Joins

ANSI-standard SQL specifies five types of JOIN:

• INNER
• LEFT OUTER
• RIGHT OUTER
• FULL OUTER
• CROSS

So, this idea of using left, right, and both the tables determine the structure of the SQL query and the type of join used.

Let us have a quick introduction to five of them:
Let’s say we have two sets of data in our relational database: table A and table B with some sort of relation specified by primary & foreign key.

So, the most intuitive way to represent all possible logical relations is the Venn diagram.

                     ****||INNER||LEFT||RIGHT||FULL||****

THE INNER JOIN:

The simplest one- (A EQUI- natural) based on match data as per the equality condition specified in SQL.
Inner join returns records at the intersection of two tables (A & B). So, It returns rows when the join condition is met. If the join condition evaluates to true a row is returned otherwise it simply ignores the row. So, this is the most common Database join. A common scenario is to join the primary key of once table to the foreign key of another.

SELECT
customers.cust_fname, orders.order_id, orders.order_date
FROM
customers INNER JOIN orders
ON customer.cust_id = orders.cust_id;

Result:
cust_name order_id order_date
ABC Co. 123456 2005-06-20
ABC Co. 147893 2006-12-14

XYZ Ltd. 258963 2010-05-06

Therefore, Interestingly, we can also write an inner join SQL query using the WHERE clause to have the same effect as follows.

SELECT
customers.cust_fname, orders.order_id, orders.order_date
FROM
customers, orders
WHERE
customers.cust_id = orders.cust_id;

In this case, the join is done implicitly by the database optimizers. So, this type of query though functionally equivalent is highly discouraged due to its misleading nature as a simple SELECT query.

THE OUTER JOIN:

Quite similar to INNER JOIN;
In the outer join we retrieve all the rows from the left, right, or both of the tables regardless of matching rows in another table. So, Therefore, there are three types of outer joins: LEFT, RIGHT, BOTH/FULL as follows;

LEFT JOIN:

The result of a left outer join (or simply left join) for tables A and B always contains all rows of the “left” table (A), even if the join-condition does not find any matching row in the “right” table (B).
So, It returns a resulted table with the right matched data from the two tables and remains rows of the left table and null from the right table’s column.

SELECT
customers.cust_fname, orders.order_id, orders.order_date
FROM
customers LEFT OUTER JOIN orders
ON customer.cust_id = orders.cust_id;

However, NULL values are returned for the columns in the right table for the rows that do not match the JOIN condition.

RIGHT JOIN:

A right outer join (or right join) is just opposite or vice versa to LEFT JOIN.
It returns a resulted table with the left matched data and remaining rows of the left table’s column.
So, If there are no matches in the left table, return Null values for those columns.

SELECT
customers.cust_fname, orders.order_id, orders.order_date
FROM
customers RIGHT OUTER JOIN orders
ON customer.cust_id = orders.cust_id;

FULL JOIN:

This join returns all matched and unmatched rows from both the table concatenated together. However, For the columns from the unmatching join condition, NULL values are returned.

Note that:
In conclusion, Oracle and SQL Server do support the full-outer join. Although therefore, it can emulate one by combining left and right-outer join with UNION set operation.

CROSS JOIN:

A CARTESIAN PRODUCT!
Cross joins return all combinations of rows from each table.
It returns the Cartesian product of rows from the table in JOIN.
Therefore, if there are total M rows in Table A and there are N rows in Table B, a cross-product will consist of M x N rows.
In other words, it will produce rows that combine each row from the first table with each row from the second table.

SELECT
customers.cust_fname, orders.order_id, orders.order_date
FROM
customers, orders;
So, this simple statement creates a cross-product between two tables. The same logic applies if cross-join with more than two tables.

Conclusion:

In conclusion, JOINs are used to establish a master-detail relationship where one base table is used to create a relationship with one or more other sub-tables.
So, I’d like to finish here, a quick grasp on these would one to drive deep at this topic.

Have a look to MySQL Documentation Click Here.

FAQs

Learn From Basic | First Steps Lead To Mastering MySQL Database Learning

Previous Blog | What are the Functions in MySQL? Some Built-in Functions

                             ** HAPPY LEARNING **

Amit Gupta