Joins in MySQL


Share

A SQL join statement is used to combine data or rows from two or more tables based on a common field between them. A Joins is a means for combing field from two table by using values comman to each.


There are four types of joins:-


INNER JOINS

In the inner joins select all rows from both of tables as the condition satisfier. This create result set by combing all rows from the tables.

Syntax of INNER JOINS:-

SELECT table.column1, table1.column2, table2.column1...FROM table1 INNER JOIN
table2 ON table1.matching_col=table2.matching_col;

Example:-

SELECT students.name, students.age, college.name FROM students INNER JOIN
college ON students.name=college.name;

LEFT JOINS

This joins return all the rows of the table on the left side of the join and matching rows for the table on the right side of join.

Syntax of LEFT JOINS:-

SELECT table.column1, table1.column2, table2.column1...FROM table1 LEFT JOIN
table2 ON table1.matching_col=table2.matching_col;

Example:-

SELECT students.name, students.age, college.name FROM students LEFT JOIN
college ON students.name=college.name;

RIGHT JOINS

The right joins is similar to left joins when return all the rows of the table on the right side of join.

Syntax of RIGHT JOINS:-

SELECT table.column1, table1.column2, table2.column1...FROM table1 RIGHT JOIN
table2 ON table1.matching_col=table2.matching_col;

Example:-

SELECT students.name, students.age, college.name FROM students RIGHT JOIN
college ON students.name=college.name;

FULL JOINS

Full joins combing the result of both left and right joins.

Syntax of FULL JOINS:-

SELECT table.column1, table1.column2, table2.column1...FROM table1 FULL JOIN
table2 ON table1.matching_col=table2.matching_col;

Example:-

SELECT students.name, students.age, college.name FROM students FULL JOIN
college ON students.name=college.name;