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

MySQL Joins Tutorial; Through this tutorial, i am going to show you types of joins in mysql and how to use MySQL JOINS with the help of examples.

MySQL JOINS are used to retrieve data from multiple tables. A MySQL JOIN is performed whenever two or more tables are joined in a SQL statement.

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

There are four types of joins in MySQL; as follows:

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

MySQL INNER JOIN

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 of MySQL INNER JOIN

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

Example of MySQL INNER JOIN

See the mysql inner join example; as follows:

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

MySQL LEFT JOIN

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 of MySQL LEFT JOIN

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

Example of MySQL LEFT JOIN

See the mysql left join example; as follows:

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;

MySQL Right JOIN

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 of MySQL Right JOIN

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

Example of MySQL Right JOIN

See the mysql right join example; as follows:

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;

MySQL Self Join

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.

Example of MySQL Self Join

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;

Conclusion

MySQL Joins tutorial, you have learned how to many types of joins in mysql and how to use mysql joins with the help of examples.

Recommended MySQL Tutorials