MySQL Tutorial Point – We would love to share with you how to use MySQL JOINS with example. Basically MySQL Provide following types of Join INNER JOIN, LEFT JOIN, RIGHT JOIN & SELF JOIN.
In this blog post we will discuss about What is MySQL join? Type of Joins etc
Here are the different types of the JOINs in SQL:
- INNER JOIN: Returns records that have matching values in both tables
- LEFT JOIN: Returns all records from the left table, and the matched records from the right table
- RIGHT JOIN: Returns all records from the right table, and the matched records from the left table
- FULL JOIN: Returns all records when there is a match in either left or right table
- CROSS JOIN: This join produces a result where the number of rows in the first table gets multiplied with the rows in the second table.
- SELF JOIN: Self-join is a regular join and here the table joins with itself only.
MySQL Joins
JOINS can be used in MySQL with the Select Statement. It is used to get data from two or more 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. The tables are mutually related using primary and foreign keys.
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) :-
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 :-
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 :-
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;
Cross Join MySQL
This join produces a result where the number of rows in the first table gets multiplied with the rows in the second table. This kind of result is called the Cartesian Product. If we use the WHERE clause with this join, then this will work as an inner join.
Cross Join MySQL Example
SELECT c.first_name, c.last_name, company.name FROM customer c CROSS JOIN company;
Note
MySQL Self join can also use with left join and right join
Conclusion
In this MySQL tutorial point – we have described about mysql joins with example.