Joining is an important part of SQL queries. Since MySQL is an RDMS, So it also supports JOIN. Here I am putting some knowledge about MySql joining. It is based on my understanding. Hoping you will get knowledge and enjoy. If you will want to share your knowledge then please share it in your comment. It will help to me improve my knowledge and understanding.
MySQLÂ Joins support Join for combining rows of two or more tables. That means, Join is used to fetch records from two or more tables using a single SELECT statement.
MySQL supports the following types of joins:-
1. INNER JOIN:- INNER JOIN fetches only the common record of two or more tables. It is also called simple join. The syntax will be:-
SELECT * FROM table1 as t1 INNER JOIN table2 as t2 ON t1. table1id = t2.table1id
Here we are seeing ON t1.table1id = t2.table1id, we can replace it by USING clause.
Now our syntax will be.
SELECT * FROM table1 as t1 INNER JOIN table2 as t2 USING(table1id )
For three table syntax:-
SELECT * FROM table1 as t1
INNER JOIN table2 as t2 ON t1. table1id = t2.table1id
INNER JOIN table3 as t3 ON t1.table1id = t3.table1id
2. LEFT JOIN:- LEFT JOIN is a part of outer JOIN, So we can write LEFT OUTER JOIN here OUTOR is optional. LEFT JOIN returns all records of the left table and only common records of the other tables. That means LEFT JOIN starts selecting rows from the left table. If a row from the left table does not have a matching row from the right table, the LEFT JOIN combines columns of rows from the left table with NULL values.
The syntax will be:-
SELECT * FROM table1 as t1 LEFT JOIN table2 as t2 ON t1. table1id = t2.table1id
Here we are seeing ON t1.table1id = t2.table1id, we can replace it by USING clause. Now our syntax will be.
SELECT * FROM table1 as t1 LEFT JOIN table2 as t2 USING(table1id )
For three table syntax:-
SELECT * FROM table1 as t1
LEFT JOIN table2 as t2 ON t1. table1id = t2.table1id
LEFT JOIN table3 as t3 ON t1.table1id = t3.table1id
3. RIGHT JOIN:- RIGHT JOIN is also a part of an outer join, So we can write it RIGHT OUTER JOIN here OUTOR is optional. RIGHT JOIN returns all records of the right tables and all common records of the remaining tables. That means RIGHT JOIN starts selecting rows from the right table. If a row from the right table does not have a matching row from the left table, the RIGHT JOIN combines columns of rows from the right table with NULL values.
The syntax will be:-
SELECT * FROM table1 as t1 RIGHT JOIN table2 as t2 ON t1. table1id = t2.table1id
Here we are seeing ON t1.table1id = t2.table1id, we can replace it by USING clause. Now our syntax will be.
SELECT * FROM table1 as t1 RIGHT JOIN table2 as t2 USING(table1id )
For three table syntax:-
SELECT * FROM table1 as t1
RIGHT JOIN table2 as t2 ON t1. table1id = t2.table1id
RIGHT JOIN table3 as t3 ON t1.table1id = t3.table1id
NOTE:- RIGHT JOIN and LEFT JOIN clauses are functionally equivalent, and they can replace each other as long as the table order is reversed.
NOTE:- USING clause we can use only when both tables exist same column name.
4. CROSS JOIN:- CROSS JOIN return all records of both tables. If we shall use WHERE close with CROSS JOIN then it will return a similar result as per INNER JOIN.
The syntax will be:-
SELECT * FROM table1 as t1
CROSS JOIN table2
As per the INNER JOIN result
SELECT * FROM table1 as t1
CROSS JOIN table2 as t2 WHERE t1. table1id = t2.table1id
5. NATURAL JOIN:-NATURAL JOIN performs the same task as per INNER JOIN. NATURAL JOIN performs in a specific condition When two tables have columns with the same name and same data type. In NATURAL JOIN no need to use ON and USING operators.
NATURAL JOIN can be used on more than two tables as well. The third table can have the common column or it can have any other columns from either of the tables.
We can use the WHERE clause in NATURAL JOIN.
NATURAL JOIN makes the SELECT query simpler with minimal use of conditions.
The syntax will be:-
SELECT * FROM table1 NATURAL JOIN table2
6. SELF JOIN:-
SELF JOIN to join the table itself. It is a regular join. That means it is useful for comparing records in the rows within a table.
The syntax will be:-
SELECT column_name(s) FROM table1 t1, table1 t2 WHERE condition
Read also:-
