MySQL JOINS

Inner JOIN, Left JOIN, Right JOIN

1. MySQL JOINS

2. What are JOINS?
Joins help retrieving data from two or more database tables.
The tables are mutually related using primary and foreign keys.

3. MySQL Cross Join
Cross JOIN is a simplest form of JOINs which matches each row from one database table to all
rows of another.
In other words it gives us combinations of each row of first table with all records in second table.
Cross Join
All rows from both tables

4. MySQL Cross Join
All mentioned bellow lines will produce the same result:
SELECT* FROMtable_1 CROSSJOINtable_2;
OR
SELECTtable_1.id, table_1.greeting, table_2.id, table_2.question FROMtable_1 CROSSJOINtable_2;
OR
SELECTtable_1.id, table_1.greeting, table_2.id, table_2.question FROMtable_1 , table_2;

5. MySQL Cross Join
id greeting
1 hello
2 hi
id question
1 Howare you doing?
2 Areyou there?
table_1 table_2
SELECT* FROMtable_1 CROSSJOINtable_2;
Result
1
2
1
2

6. MySQL Inner Join
The inner JOIN is used to return rows from both tables that satisfy the given condition.
The inner JOIN is same as JOIN clause, combining rows from two or more tables.
Inner Join
Only matching rows

7. MySQL Inner Join
Both lines will produce the same result:
SELECT* FROMtable_1INNERJOINtable_2ONtable_1.id = table_2.id;
OR
SELECT* FROMtable_1 JOINtable_2 ONtable_1.id = table_2.id;;

8. MySQL Inner Join
id greeting
1 hello
2 hi
id question
1 Howare you doing?
2 Areyou there?
table_1 table_2
SELECT* FROMtable_1 INNERJOINtable_2 ONtable_1.id = table_2.id;
Result

9. MySQL Outer Join
MySQL does not support outer join.

10. MySQL Left Join
The LEFT JOIN returns all the rows from the table on the left even if no matching rows
have been found in the table on the right.
Where no matches have been found in the table on the right, NULL is returned
Left Join
All rows from the left table

11. MySQL Left Join
Both lines will produce the same result:
SELECT* FROMtable_1LEFTJOINtable_2ONtable_1.id = table_2.id;
OR
SELECT* FROMtable_1LEFT OUTER JOINtable_2 ONtable_1.id = table_2.id;;

12. MySQL Left Join
id greeting
1 hello
2 hi
3 hey
id question
1 Howare you doing?
2 Areyou there?
table_1 table_2
SELECT* FROMtable_1 LEFTJOINtable_2 ONtable_1.id = table_2.id;
Result

13. MySQL Right Join
The RIGHT JOIN returns all the columns from the table on the right even if no matching
rows have been found in the table on the left.
Where no matches have been found in the table on the left, NULL is returned.
Right Join
All rows from the right table

14. MySQL Right Join
Both lines will produce the same result:
SELECT* FROMtable_1RIGHTJOINtable_2ONtable_1.id = table_2.id;
OR
SELECT* FROMtable_1RIGHT OUTER JOIN table_2 ONtable_1.id = table_2.id;;

15. MySQL Right Join
id greeting
1 hello
2 hi
id question
1 Howare you doing?
2 Areyou there?
3 Are you ready?
table_1 table_2
SELECT* FROMtable_1 RIGHTJOINtable_2 ONtable_1.id = table_2.id;
Result

16. MySQL Right Join with USING clause
id question
1 Howare you doing?
2 Areyou there?
3 Are you ready?
table_1 table_2
SELECT* FROMtable_1 RIGHTJOINtable_2 USING(id);
Result
id greeting
1 hello
2 hi

17. Why we use JOINs
Using JOINs, you can get the work done by using only a one query with any search
parameters.
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).
Further it requires more data manipulations in application end also.