Understanding SQL Joins

There are three types of joins in SQL queries: Inner Join, Left Join, and Right Join. Very often, people are getting confused on how to use them properly. In this article, we will show you, in a simple and clear way, the difference between each, so you could handle your database queries more easily and efficiently.

Inner Join

An INNER JOIN combines rows from two or more tables based on a common field (usually a foreign key relationship). It creates a new virtual table containing only the rows with matching values in both tables. If there’s no match for a record in either table, it is excluded from the result.

Syntax:

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

Suppose we have two tables: Products and Categories. We want to retrieve product details along with their corresponding category names.

SELECT Products.ProductID, Products.ProductName, Categories.CategoryName
FROM: Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;

Result:

ProductID	ProductName	CategoryName
1	Chais	Beverages
2	Chang	Beverages
3	Aniseed Syrup	Condiments

Note: The INNER JOIN keyword returns only rows with a match in both tables. If a product has no CategoryID or if the CategoryID is not present in the Categories table, that record won’t appear in the result. You can also use INNER JOIN to combine three or more tables as long as they have relationships.

Left Join

The LEFT JOIN retrieves all records from the left table. Also includes matching records from the right table. If there’s no match for a record in the right table, it still displays the row from the left table with NULL values for the right table’s columns.

Syntax:

SELECT columns
FROM left_table
LEFT JOIN right_table ON join_condition;

Consider for example two tables: Employee_Data and Department_Data

SELECT Employee_Data.emp_name, Employee_Data.emp_dept, Department_Data.location_name
FROM Employee_Data
LEFT JOIN Department_Data ON Employee_Data.emp_dept = Department_Data.department_name;

Result:

emp_name	emp_dept	location_name
Gaurav	HR	Building 1
Anjali	IT	Building 2
Akshada	Finance	NULL
Amit	IT	Building 2

Right Join

The RIGHT JOIN retrieves all records from the right table. Includes matching records from the left table. If there’s no match for a record in the left table, it displays the row from the right table with NULL values for the left table’s columns.

Syntax:

SELECT columns
FROM left_table
RIGHT JOIN right_table ON join_condition;

Example:

SELECT Employee_Data.emp_name, Employee_Data.emp_dept, Department_Data.location_name
FROM Employee_Data
RIGHT JOIN Department_Data ON Employee_Data.emp_dept = Department_Data.department_name;

Result:

emp_name	emp_dept	location_name
Gaurav	HR	Building 1
Anjali	IT	Building 2
NULL	NULL	Marketing/Sales Building 3