When working with databases, it is often necessary to retrieve data from multiple tables. This can be achieved by using different types of joins in SQL Server. In this article, we will explore the concepts of INNER JOIN and LEFT JOIN and how they can be used to join multiple tables.

INNER JOIN

The INNER JOIN is used to combine rows from two or more tables based on a related column between them. It returns only the rows that have matching values in both tables.

Let’s consider an example. We have a database with tables for employees, calls, and call outcomes. We want to list all calls with their start time and end time, along with the outcome and the first and last name of the employee who made the call. We can achieve this by using INNER JOIN as shown in the following query:

SELECT employee.first_name, employee.last_name, call.start_time, call.end_time, call_outcome.outcome_text
FROM employee
INNER JOIN call ON call.employee_id = employee.id
INNER JOIN call_outcome ON call.call_outcome_id = call_outcome.id
ORDER BY call.start_time ASC;

This query will join the employee, call, and call_outcome tables based on the specified join conditions and return the desired result.

LEFT JOIN

The LEFT JOIN is used to combine rows from two or more tables based on a related column between them. It returns all the rows from the left table and the matching rows from the right table. If there are no matching rows in the right table, NULL values are returned.

Let’s consider another example. We have tables for countries, cities, and customers. We want to list all countries and the customers related to these countries, including countries without any related cities or customers. We can achieve this by using LEFT JOIN as shown in the following query:

SELECT country.country_name_eng, city.city_name, customer.customer_name
FROM country
LEFT JOIN city ON city.country_id = country.id
LEFT JOIN customer ON customer.city_id = city.id;

This query will join the country, city, and customer tables based on the specified join conditions and return the desired result. It will include all countries, even those without any related cities, and all cities, even those without any customers.

Conclusion

Understanding how to join multiple tables in SQL Server is essential for retrieving meaningful data from databases. The INNER JOIN and LEFT JOIN are powerful tools that allow us to combine rows from different tables based on related columns. By using these joins appropriately, we can retrieve the desired results and gain valuable insights from our data.