MySQL Joins- Tutorialpath

MySQL Joins Example

Welcome to all our readers on our website at www.tutorialpath.com. As you all know that here on our website we will provide you lots of information related to the latest and newly introduced technology which help you to get updated with the latest tech. Here you will get all the useful information which is required in this tech world. As you know technology is one of the trending part in this world. So, here again we came up with a latest and new tech information which is related to the process of creating MYSQL JOIN. With the help of this article you will get to know the complete process of how to create MYSQL JOIN in a step by step manner. So, simply have a look to this article and grab all the useful information which is going to be very helpful for you.

What are Joins?

Joins are the most important thing in relational databases. They are used to join two or more different tables on a point in which both the tables match the same value and property. There are four easy ways to join two or more tables:

1. Inner Join
2. Left Join
3. Right Join

Note :Here The Person is Left Table and Orders is Right Table

Inner join

if there is any common key(primary and foreign key) in both tables then the
join so performed is called inner join.

Query

Output

Left join

left join returns all rows from left table even if there is no match in the right table.

Query

RIGHT OUTER JOIN

Another type of join is called a MySQL RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the RIGHT OUTER JOIN in MySQL is:

Example

Here is an example of a MySQL RIGHT OUTER JOIN:

This RIGHT OUTER JOIN example would return all rows from the orders table and only those rows from the suppliers table where the joined fields are equal.

If a supplier_id value in the orders table does not exist in the suppliers table, all fields in the suppliers table will display as <null> in the result set.

Let’s look at some data to explain how RIGHT OUTER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:

Outer join(Full Join)

Outer join displays all rows from left table and right table. if there are rows in left table which does not matches with the rows in right table , those rows will also be displayed and vice versa.

It can detect records having no match in joined table. It returns NULL values for records of joined table if no match is found.

Query

Output

 

Why should we use joins?

Now you may think, why we use JOINs when we can do the same task running queries. Especially if you have some experience in database programming you know we can run queries one by one, use output of each in successive queries. Of course, that is possible. But using JOINs, you can get the work done by using only a one query with any search parameters. On the other hand MySQL can achieve better performance with JOINs as it can use Indexing. Simply use of single JOIN query instead running multiple queries do reduce server overhead. Using multiple queries instead that leads more data transfers between MySQL and applications (software).

Conclusion:

So, as of now we had shared all the useful information related to MySQL Joins which will going to very helpful for you. This article will help you to understand all the details and steps in a proper manner. Also, if you like this article then simply share this article with others as well so that they also understand all the details and information easily. On the other side, if you find any error in this article or if you have any query related to this article then simply drop a comment in the comment section below so that we will get to know that what issue you are facing and also we can reply to your query instantly.