<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:

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:

departments table:

Using a left join to combine these tables:

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

Result:

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:

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:

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

Result:

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.