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

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

Direct Switch to Contents

  1. What is JOIN in MySQL?
  2. Types Of Joins
  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.

MySQL Joins
                     ****||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.

Full-outer-join

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.

mysql-cross-join

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

1. What is the importance of joins in SQL?

The relationship between tables is an important component & by using SQL JOIN this relationship enables us to tie data in one table to data in another table to retrieve a meaningful result.
JOIN are combining columns or two forms one or more tables by using common value. It creates a set that can be served as a table or used as it is.

2. How JOIN can be used in MySQL?

JOIN is a keyword in SQL. Minimum required condition for joining table (n-1), where n is no. of tables. A fewer number of tables generally result in a more efficient and manageable query.
A table can also join to itself called as self-join

3. Why do we need JOIN in MySQL?

MySQL (structured query language) JOIN operates in relational algebra, by using common values in two or more tables we can combine records in a database to make it easy to read and stitch the database back together. SQLite and other databases such as Microsoft SQL-server and MYSQL are relational databases. 

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 **
RELATED ARTICLES

5 Best Laptops Under 60000 Suitable To Your Profession

Today we live in the era of the internet in which laptops and mobile phones play an important role. So if you...

5 Best Inverters to Buy This Year | How to Choose the Right Inverter

We all know inverters are useful for getting backup of electricity, but fewer people know about all features and types. If you...

Top Selling Best Budget Smartphones That You Can Buy This Year

Are you planning to buy a smartphone? Or Looking for a budget smartphones with the best features?  Almost everyone is stuck with...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

5 Best Laptops Under 60000 Suitable To Your Profession

Today we live in the era of the internet in which laptops and mobile phones play an important role. So if you...

5 Best Inverters to Buy This Year | How to Choose the Right Inverter

We all know inverters are useful for getting backup of electricity, but fewer people know about all features and types. If you...

Top Selling Best Budget Smartphones That You Can Buy This Year

Are you planning to buy a smartphone? Or Looking for a budget smartphones with the best features?  Almost everyone is stuck with...

Top 10 Network Marketing Companies in India | Future of MLM Business

Everybody knows about marketing that it is an essential part of a business. It is essential to promote our product to others...

Recent Comments