Let's talk about SQL Joins

SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them. The most common type of join is: SQL INNER JOIN (simple join). SQL INNER JOIN return all rows from multiple tables where the join condition is met.

Let's look at a selection from the "Orders" table:

OrderID	CustomerID	OrderDate
10308	2	1996-09-18
10309	37	1996-09-19
10310	77	1996-09-20

Then, have a look at a selection from the "Customers" table:

CustomerID	CustomerName	ContactName	Country
1	Robert	Roberto	Italy
2	Jimmy	Jimmy Boy	Mexico
3	Angelina	Angela Gucci	Mexico

Notice that the "CustomerID" column in the "Orders" table refers to the customer in the "Customers" table. The relationship between the two tables above is the "CustomerID" column. Then, if we run the following SQL statement (that contains an INNER JOIN):

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

it will produce something like this:

OrderID	CustomerName	OrderDate
10308	Robert	9/18/1996
10365	Angelina	11/27/1996
10383	Around the Horn	12/16/1996
10355	Around the Horn	11/15/1996
10278	Akiko	8/12/1996

SQL INNER JOIN keyword 

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.
INNER JOIN Syntax:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

or:

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

NOTE! INNER JOIN is the same as JOIN.

SQL LEFT JOIN keyword

The LEFT JOIN keyword returns all rows from the left table, with the matching rows in the right table. The result is NULL in the right side when there is no match.
SQL LEFT JOIN Syntax:

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

or:

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

NOTE! In some databases LEFT JOIN is called LEFT OUTER JOIN.

SQL RIGHT JOIN keyword

The RIGHT JOIN keyword returns all rows from the right table, with the matching rows in the left table. The result is NULL in the left side when there is no match.
SQL RIGHT JOIN Syntax:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

or:

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

NOTE! In some databases RIGHT JOIN is called RIGHT OUTER JOIN.