For some of the students, the idea of database tables and different type of joins can be quite confusing to them. But it is really not that complex, if you understand the core idea behind it.
Here are simple explanation of the different type of SQL joins. But wait, what is a join?
SQL joins are used to combine rows from two or more tables. It basically define the rules on how to combine the rows.
Inner Join – the simplest join
It joins the rows from both table when the join criteria are met:
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
The results can be graphically represented below:
This means that you will get the rows that the ON condition is met. In this case all rows from both table where column_name from table1 equals to the column_name from table2.
Left Join
Returns all rows from the left table, with the matching rows in the right table.
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
Right Join
Returns all rows from the right table, with the matching rows in the left table.
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
Full Outer Join
Returns all rows from the left table and from the right table.
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
