MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS

MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS

MySQL Joins tutorial; Through this tutorial, we would love to share with you how to use MySQL JOINS with the help of examples.

JOINS can be used in MySQL with the Select Statement. It is used to get data from many database tables. When we want to get the records from two or more database tables, then we need to use MySQL JOINS at that time.

MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS

Basically MySQL Provide three types of Join INNER JOIN, LEFT JOIN, RIGHT JOIN & SELF JOIN.

  • INNER JOIN MySQL
  • LEFT JOIN MySQL
  • RIGHT JOIN MySQL
  • SELF JOIN MySQL

INNER JOIN MySQL

In MySQL INNER JOIN clause selects records if the given column values matching in both tables. MySQL INNER JOIN is used to fetch data from multiple tables.

Syntax

 SELECT table1.column, table2.column 
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

INNER JOIN MySQL Example

If we want to select data from two country and city, then we can run the following query (Inner Join) :-

MySQL INNER JOIN
 SELECT city, country
 FROM city
 INNER JOIN country ON
 city.country_id = country.country_id;

LEFT JOIN MySQL

The MySQL LEFT JOIN clause returns all rows from the left table, even if there are no matches in the right table, The result is NULL from the right side.

Syntax

 SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

LEFT JOIN MySQL Example

Let’s return a list of all users table. And if the users shares the same last name with an actor table :-

LEFT JOIN MYSQL
SELECT 
     u.user_id, 
     u.first_name, 
     u.last_name,
     a.autor_id,
     a.first_name,
     a.last_name
 FROM users u
 LEFT JOIN actor a 
 ON u.last_name = a.last_name
 ORDER BY u.last_name;

Right JOIN MySQL

The MySQL Right JOIN clause returns all rows from the right table, even if there are no matches in the right table, The result is NULL from the right side.

Syntax

 SELECT column_name(s)
FROM table1
Right JOIN table2
ON table1.column_name = table2.column_name;

Right JOIN MySQL Example

Let’s return a list of all users table. And if the users shares the same last name with an actor table :-

MySQL RIGHT JOIN
SELECT 
     u.user_id, 
     u.first_name, 
     u.last_name,
     a.autor_id,
     a.first_name,
     a.last_name
 FROM users u
 RIGHT JOIN actor a 
 ON u.last_name = a.last_name
 ORDER BY a.last_name;

Self Join MySQL

The MySQL self join is used to join a table to itself when using a join clause.

MySQL self join is useful when you want to combine the records in a table with other records in the same table.

Self Join MySQL Example

SELECT 
     a.customer_id, 
     a.first_name, 
     a.last_name, 
     b.customer_id,
     b.first_name, 
     b.last_name 
 FROM customer a
 INNER JOIN customer b
 ON a.last_name = b.first_name;

Note

MySQL Self join can also use with left join and right join

Conclusion

In this MySQL joins tutorial- we have learned how to many types of joins in mysql and how to use mysql joins with the help of examples.

Recommended MySQL Tutorials

AuthorAdmin

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Python, Javascript, JQuery, Laravel, Livewire, Codeigniter, Node JS, Express JS, Vue JS, Angular JS, React Js, MySQL, MongoDB, REST APIs, Windows, Xampp, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL and Bootstrap from a starting stage. As well as demo example.

One reply to MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS

  1. Pretty! This was a really wonderful article. Thanks for supplying these details.

Leave a Reply

Your email address will not be published. Required fields are marked *