INNER JOIN
The INNER JOIN keyword selects records that have matching values in both tables. The INNER JOIN keyword is equal to JOIN.
| OrderID | CustomerID | OrderDate |
|---|---|---|
| 10308 | 2 | 1996-09-18 |
| 10309 | 37 | 1996-09-19 |
| 10310 | 77 | 1996-09-20 |
| CustomerID | CustomerName | ContactName |
|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders |
| 2 | Centro comercial Moctezuma | Francisco Chang |
| 3 | Ernst Handel | Roland Mendel |
INNER JOIN Example
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
| OrderID | CustomerName |
|---|---|
| 10308 | Centro comercial Moctezuma |
| 10309 | Ernst Handel |
| 10310 | Island Trading |
Note that the INNER JOIN keyword returns all records when there is at least one match in both tables.
LEFT JOIN
The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
| OrderID | CustomerID | OrderDate |
|---|---|---|
| 10308 | 2 | 1996-09-18 |
| 10309 | 37 | 1996-09-19 |
| 10310 | 77 | 1996-09-20 |
| CustomerID | CustomerName | ContactName |
|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders |
| 2 | Centro comercial Moctezuma | Francisco Chang |
| 3 | Ernst Handel | Roland Mendel |
LEFT JOIN Example
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
| OrderID | CustomerName |
|---|---|
| 10308 | Centro comercial Moctezuma |
| 10309 | Ernst Handel |
| 10310 | NULL |
Note that the LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side when there is no match.
RIGHT JOIN
The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side when there is no match.
| OrderID | CustomerID | OrderDate |
|---|---|---|
| 10308 | 2 | 1996-09-18 |
| 10309 | 37 | 1996-09-19 |
| 10310 | 77 | 1996-09-20 |
| CustomerID | CustomerName | ContactName |
|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders |
| 2 | Centro comercial Moctezuma | Francisco Chang |
| 3 | Ernst Handel | Roland Mendel |
RIGHT JOIN Example
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
| OrderID | CustomerName |
|---|---|
| 10308 | Centro comercial Moctezuma |
| 10309 | Ernst Handel |
| NULL | Ernst Handel |
FULL JOIN
The FULL JOIN keyword returns all records when there is a match in either left (table1) or right (table2) table records.
| OrderID | CustomerID | OrderDate |
|---|---|---|
| 10308 | 2 | 1996-09-18 |
| 10309 | 37 | 1996-09-19 |
| 10310 | 77 | 1996-09-20 |
| CustomerID | CustomerName | ContactName |
|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders |
| 2 | Centro comercial Moctezuma | Francisco Chang |
| 3 | Ernst Handel | Roland Mendel |
FULL JOIN Example
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
FULL JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
| OrderID | CustomerName |
|---|---|
| 10308 | Centro comercial Moctezuma |
| 10309 | Ernst Handel |
| 10310 | Island Trading |
| NULL | Laughing Bacchus Winecellars |
| NULL | Magazzini Alimentari Riuniti |
Note that the FULL JOIN keyword returns all records when there is a match in either left (table1) or right (table2) table records. The result is NULL from the side where there is no match.