SQL Joins Explained with Examples

SQL JOINs are widely used for combining tables. Often times in databases, we have to combine 2-3 tables to get the complete information which makes JOINs one of the most important concepts in SQL. JOINs can look confusing initially but once we understand the basics and practice with a few examples, we start getting a hold of them. Whether you’re a beginner or an experienced developer, mastering SQL JOINs is crucial for everyone.

Basically in JOINs, we combine tables based on  a related / common column. In this article, we will explain all the different types of SQL JOINs with simple examples to help you understand the concept quickly and easily.

What are SQL JOINs?

As stated above, SQL JOINs are used to combine tables based on a common column in them. This allows us to understand the data with complete picture, gather insights and write fabulous reports. Suppose there are 2 tables with one common column, we may want to combine them so that we have one complete table instead of 2 incomplete tables.

---

Why Are SQL Joins Important?

• JOINs help in removing redundant data by forming a combined table with all the important information, instead of having multiple tables with incomplete information.
• We can retrieve data from multiple tables in a structured and systematic way.
• They improve query efficiency by filtering and combining only necessary data.

---

Practical Example to Understand SQL JOINS –

Let’s understand with the help of practical examples. Suppose we have these two tables:

Notice that ‘DepartmentID’ is common in both the tables. Now, let’s understand different types of joins using these tables.

1. INNER SQL JOIN

An INNER JOIN returns only the records that have common / matching values in both tables. We will combine both the tables on DepartmentID column as it is common in both the tables. As we can see, DepartmentID – 101, 102, 103 are common in both the tables but 104 is only in Table 2. Now let’s run the query and see what happens.

Syntax of INNER SQL JOIN:

SELECT Employees.EmployeeID, Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Output:

EmployeeID	Name	DepartmentName
1	Alice	HR
2	Bob	IT
3	Charlie	Finance

Explanation:

• The INNER JOIN only returns employees who have a matching DepartmentID in the Departments table.
• Employees without a department (like David) are excluded.
• The “Marketing” department is also excluded because no employee belongs to it.

2. LEFT SQL JOIN (or LEFT OUTER JOIN)

A LEFT JOIN returns all records from the left table (Employees) and the corresponding matched records from the right table (Departments). NULL values will be returned in case there is no match. Let’s understand this with below query –

Syntax of LEFT SQL JOIN:

SELECT Employees.EmployeeID, Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Output:

EmployeeID	Name	DepartmentName
1	Alice	HR
2	Bob	IT
3	Charlie	Finance
4	David	NULL

Explanation:

• Here, the LEFT JOIN ensured that all employees from the employee table are included in the result. Why? Because it is the left table.
• Since David has no department assigned to him, NULL is returned for David in the DepartmentName column.

3. RIGHT SQL JOIN (or RIGHT OUTER JOIN)

A RIGHT JOIN returns all records from the right table (Departments) and the corresponding matched records from the left table (Employees). If there is no match, NULL values are returned. Think of RIGHT JOIN as the exact opposite of LEFT JOIN. See the below example –

Syntax of RIGHT SQL JOIN:

SELECT Employees.EmployeeID, Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Output:

EmployeeID	Name	DepartmentName
1	Alice	HR
2	Bob	IT
3	Charlie	Finance
NULL	NULL	Marketing

Explanation:

• Since all departments are included, the “Marketing” department appears even though no employee belongs to it. Again, this is because we ran the RIGHT JOIN.

4. FULL SQL JOIN (or FULL OUTER JOIN)

A FULL JOIN returns all records from both tables, filling in NULLs for missing matches. Simply speaking, it takes everything from both the tables and combines them.

Syntax of FULL SQL JOIN:

SELECT Employees.EmployeeID, Employees.Name, Departments.DepartmentName
FROM Employees
FULL JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Output:

EmployeeID	Name	DepartmentName
1	Alice	HR
2	Bob	IT
3	Charlie	Finance
4	David	NULL
NULL	NULL	Marketing

Explanation:

• The FULL JOIN includes all employees and all departments and assigned NULL for Marketing department since there was no match for it.

5. CROSS SQL JOIN

A CROSS JOIN returns the Cartesian product of the two tables. Meaning, it matches every row of the first table with every row of the second table. This is not as frequently used as the four JOINs explained above.

Syntax of CROSS SQL JOIN:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
CROSS JOIN Departments;

Output:

As we can see, each employee was matched with every department, returning 4 × 4 = 16 rows.)

Explanation:

• If N employees and M departments exist, the result will have N × M rows.

6. BONUS: SELF SQL JOIN

SELF JOIN is used to join a table with itself. Hence, this is useful for hierarchical data like employee-manager relationships.

Example of SELF SQL JOIN:

Consider below table where we have employee ID, name and Manager’s employee ID-

EmployeeID	Name	ManagerID
1	Alice	NULL
2	Bob	1
3	Charlie	1
4	David	2

Advanced Concepts in SQL Joins

The JOINs discussed till now are the most frequently used JOINs. They cover the basics very well. Thus, let’s discuss some advances concepts that can help us understand JOINs in more details.

---

1. Multiple SQL Joins in a Single Query

Specially in real world practical scenarios, our data is spread across multiple tables. Hence, we need to join more than two tables to get the required insights.

Example: Joining Three Tables

Let’s suppose we have an additional Salaries table in addition to the two input tables discussed above (Employees and Department):

Salaries Table

Now, if we want to retrieve employee names, their department names, and salaries, we can use multiple joins as below:

Output:

2. Using SQL Joins with Aggregate Functions

Similarly, we can use aggregate functions like COUNT(), SUM(), AVG(), and MAX() with joins to get required reports.

Example: Counting Employees in Each Department

Output:

Explanation:

• The COUNT() function is counting the number of employees in each department.

•  The Marketing department has a count of 0 since no employees belong to it.

---

3. SQL Joins vs. Subqueries: Which One to Use?

We can use both joins and subqueries to fetch data from multiple tables. However, it is important to know when to use one over the other.

Using a SQL JOIN:

Using a Subquery:

When to Use Joins?

• When we need to retrieve data from multiple tables efficiently.

• When we need to run the queries faster (joins are usually faster than subqueries).

When to Use Subqueries?

• When the query logic is simple or when we need to filter data based on a specific condition.

• When we need to calculate a single value (e.g., fetching the highest salary).

---

4. Performance Optimization in SQL Joins

Joins can consume lot of resources if we do not optimize correctly. Thus, here are some best practices to improve query performance:

a. Use Indexing

Creating an index on the columns which is used in the JOIN condition certainly improves performance.

b. Avoid Using SELECT *

Fetching all columns using SELECT * is surely not a good practice because it slows down queries. Instead, specify only the required columns.

c. Use INNER JOIN Instead of FULL JOIN (If Possible)

FULL JOIN can be slow since it retrieves all records from both tables. If you only need matching records, use INNER JOIN instead.

d. Filter Data Before Joining

Using WHERE clauses before joining reduces the number of rows processed, leading to improved performance.

---

5. Real-Life Applications of SQL Joins

SQL joins are significantly used in real-world scenarios across different industries. Thus, let’s look at some examples:

a. E-commerce (Online Shopping Platforms like Amazon)

• E-commerce websites get a lot of data everyday. Hence, they may have to frequently combine Customers, Orders, and Products tables to analyze purchase trends.

• Example: “Show all customers who bought ‘Laptop’ in the last month.”

b. Banking and Finance

• Joining Accounts, Transactions, and Customers to track account activity.

• Example: “Get the last 5 transactions of every customer.”

c. Healthcare and Hospitals

• Combining Patients, Doctors, and Appointments tables to similarly manage hospital records.

• Example: “List all patients who visited a specific doctor.”

d. Social Media Platforms

• Joining Users, Posts, and Likes tables to generate engagement reports.

• Example: “Find the most liked post in the last 24 hours.”

Conclusion

---

Obviously, it is of paramount importance to understand SQL JOINs for working with databases efficiently. Hence, let’s quickly summarize what we learned –

JOIN Type	Description
INNER JOIN	Returns only matching / common records from both tables.
LEFT JOIN	Returns all records from the left table and corresponding matching records from the right table.
RIGHT JOIN	Returns all records from the right table and corresponding matching records from the left table.
FULL JOIN	Returns all records from both tables, with NULLs for unmatched rows.
CROSS JOIN	Returns the Cartesian product of both tables.
SELF JOIN	Joins a table with itself for hierarchical relationships.

We also discussed advanced JOINs for experienced coders. By mastering these joins, you’ll certainly be able to query databases effectively and hence generate insightful reports. Furthermore, try these queries in your SQL database and see the results yourself!

Also check out these articles on SQL –

SQL Common Interview Questions and Answers

Advance SQL Queries