# <SQL>Left Join v.s. inner join

In SQL, a join operation is used to combine rows from two or more tables based on a related column between them. There are several types of join operations, and two of the most commonly used are the left join and the inner join.

**Left Join**

A left join returns all rows from the left table and the matched rows from the right table. If no match is found, NULL values are returned for columns from the right table. The syntax for a left join is as follows:

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

**Example:**

Consider two tables, `employees` and `departments`:

`employees` table:

| **emp\_id** | **emp\_name** | **dept\_id** |
| --- | --- | --- |
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Carol | 10 |
| 4 | Dave | NULL |

`departments` table:

| **dept\_id** | **dept\_name** |
| --- | --- |
| 10 | HR |
| 20 | Finance |
| 30 | IT |

Using a left join to combine these tables:

```sql
SELECT employees.emp_name, departments.dept_name
FROM employees
LEFT JOIN departments
ON employees.dept_id = departments.dept_id;
```

Result:

| **emp\_name** | **dept\_name** |
| --- | --- |
| Alice | HR |
| Bob | Finance |
| Carol | HR |
| Dave | NULL |

As you can see, all rows from the `employees` table are included, and the corresponding `dept_name` values from the `departments` table are included where a match is found. If there is no match, NULL values are returned.

**Inner Join**

An inner join returns only the rows where there is a match in both tables. The syntax for an inner join is:

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

Using the same tables as before, the inner join query would look like this:

```sql
SELECT employees.emp_name, departments.dept_name
FROM employees
JOIN departments
ON employees.dept_id = departments.dept_id;
```

Result:

| **emp\_name** | **dept\_name** |
| --- | --- |
| Alice | HR |
| Bob | Finance |
| Carol | HR |

Notice that the inner join only returns the rows where there is a match in both tables. In this case, the employee "Dave" is not included in the result since there is no matching `dept_id` in the `departments` table.

In summary, the main difference between a left join and an inner join is that a left join includes all rows from the left table and any matching rows from the right table (with NULL values if there is no match), while an inner join returns only the rows where there is a match in both tables.