SQL Joins 101

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:

SQL Inner Join

 

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;

SQL Left Join

 

 

 

 

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;

SQL Right Join

 

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;

SQL Full Join

16,620 total views, 1 views today

No Comments

Post a Comment