Lesson 5	INNER and OUTER joins
Objective	Understand what INNER and OUTER joins are.

SQL INNER JOIN statement

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

How does the syntax of the 1) inner join differentiate itself from the 2) outer join?

In SQL, the syntax for Inner Join and Outer Join, while similar in structure, fundamentally differs in how they handle the matching and inclusion of rows from the joined tables. Understanding these differences is crucial for any SQL practitioner.

Inner Join Syntax:
- The Inner Join selects rows from both tables as long as there is a match in both tables.
- Syntax:
```
SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;
```
- In this syntax, `table1` and `table2` are the tables being joined, `common_column` is the column they have in common.
- Only the rows with matching values in the `common_column` from both tables are included in the result set.
- Rows from either table that do not have a corresponding match in the other table are excluded from the results.
Outer Join Syntax:
- Outer Joins are of three types: Left Outer Join, Right Outer Join, and Full Outer Join. They are used to select rows from one table with matching rows from another. If there are no matches, the result set still includes rows from one or both tables (depending on the type of Outer Join), with NULLs in place of missing values.
- Left Outer Join (or Left Join):
  - Syntax:
```
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;
```
  - This syntax returns all rows from `table1` (left table), and the matched rows from `table2` (right table). If there is no match, the result is NULL on the side of `table2`.
- Right Outer Join (or Right Join):
  - Syntax:
```
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;
```
  - Opposite of the Left Join, this returns all rows from `table2`, with the matching rows from `table1`. If there's no match, the result is NULL on the side of `table1`.
- Full Outer Join (or Full Join):
  - Syntax:
```
SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;
```
  - This combines the effects of both Left and Right Joins. It returns all rows when there is a match in either `table1` or `table2`. Where there's no match, the result set will have NULL values for every column of the table that lacks a matching row.

In summary, the Inner Join is used when you need to return only the rows with matching values in both tables. In contrast, Outer Joins (Left, Right, or Full) are employed when you also need to include rows that do not have matching counterparts in one or both tables, filling in the gaps with NULL values where necessary. Understanding these differences is essential for effective data retrieval and manipulation in SQL.

SQL INNER JOIN Syntax:

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

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

Note: An INNER JOIN is the same as JOIN.

Build SQL Statements based on table information

Early on in this course, we investigated what it meant to have a normalized database and its associated tables. Although normalization is not an SQL statement, it is something that you use when you query a table with SQL. You need to understand the mechanics as they relate to how to use the information in the table and how to build your SQL statements. Joins, specifically the INNER and OUTER join approaches are much the same thing. An INNER join is the most common type of join. It simply returns the rows that match the WHERE clause, and entries that do not have matches will not be included in the results. An OUTER join will show you all of the rows from one or both of the tables, regardless of whether those rows match rows in the other table. In the next lesson, we will look at three types of OUTER joins:

LEFT,
RIGHT, and
FULL joins.