Understanding SQL Joins with Practical Examples (Beginner Friendly Guide)

In real world databases, data is rarely stored in a single spreadsheet. Instead it is organized across multiple related tables. SQL provides powerful tools such as joins to combine and analyze this data efficiently.

What is SQL Joins.

SQL joins are used to combine data from two or more tables based on a related column between them. In most relational databases, information is stored across multiple tables and joins allow us to bring this data together for analysis.

SQL joins are useful in:

• Matching records using common columns such as IDs or keys
• Create meaningful results from separate tables
• Retrieve related data stored across multiple tables
• Improve data analysis by combining related information

By using joins allows analysts to work with connected datasets instead of isolated tables, making it easier to uncover insights and answer business questions.

Types of SQL Joins

SQL joins are categorized into different types based on how data in the different tables are matched and combined.

• Inner join
• Left join (left outer join)
• Right join (Right outer join)
• Full outer join
• Cross join
• Self join
• Natural join

INNER JOIN

An INNER JOIN is used to return only the rows that have matching values in both tables being joined. If a record exists in one table but does not a corresponding match in the other table, it will not appear in the result.

In simple terms, INNER JOIN shows only data that exists in both tables.
This works by comparing a common column (usually an ID or Key) between the two tables and returning the rows where the values match.

Below we have the general syntax for an INNER JOIN.

syntax:
SELECT table_1.column_1, table_2.column_2, ...
FROM table_1 INNER JOIN table_2
ON table_1.matching_column = table_2.matching_column;

Example:
First we view the contents of the tables:

select * from employees e ;

select * from departments d ;

Now join the employees table with the departments table.

--> Join the employees table to department table

SELECT employees.name, departments.department_name 
FROM employees INNER JOIN departments 
ON employees.employee_id = departments.department_id ;

In this example, we use the department_id column, which acts as a unique identifier (Primary Key/Foreign Key relationship), to connect the two tables.

The result will return employees along with their department names, but only for employees whose department_id exists in both tables.

NOTE:

INNER JOIN focuses on the intersection of two tables, meaning only the records that match in both tables are included in the result set.

LEFT JOIN (LEFT OUTER JOIN)

A LEFT JOIN, also called a LEFT OUTER JOIN, returns all rows from the left table and the matching rows from the right table. If there is no matching record in the right table, the results will still include the row from the left table, but the columns from the right table will contain NULL values.

In simple terms, LEFT JOIN keeps everything from the left table and only the matching data from the right table

Unlike INNER JOIN, which only returns matching rows from both tables, LEFT JOIN keeps all rows from the left table even if no match exists in the right table.

Here is the general syntax from a LEFT JOIN.

SELECT table_1.column_1, table_2.column_2, ...
FROM table_1
LEFT JOIN table_2
ON table_1.matching_column = table_2.matching_column;

Example
First, we view the contents of the tables:

SELECT * FROM employees;

SELECT * FROM departments;

Now we perform the LEFT JOIN:

--> LEFT JOIN the employees table to departments table

SELECT employees.name, departments.department_name, employees.salary
FROM employees
LEFT JOIN departments
ON employees.department_id  = departments.department_id;

Below is a snippet image of how the query in our example will turn out;

Explanation

• The query returns all employees from the employees table.
• For the employees with a matching department_id in the department table, the department name is displayed.
• For employees with no matching department, the department_name column will show NULL as of the case with Eve in the example above.

NOTE:

A LEFT JOIN returns all records from the left table and the matching records from the right table. If no match is found, NULL values are returned for columns from the right table.

RIGHT JOIN (RIGHT OUTER JOIN)

A RIGHT JOIN, also called a RIGHT OUTER JOIN, returns all rows from the right table and the matching rows from the left table. If there is no matching record in the left table, the result will still include the row from the right table, but columns from the left table will contain NULL values.

In simple terms, RIGHT JOIN keeps everything from the right table and only the matching data from the left table.

Here is the general syntax from a RIGHT JOIN.

SELECT table_1.column_1, table_2.column_2, ...
FROM table_1
RIGHT JOIN table_2
ON table_1.mathing_column = table_2.matching_column;

Example
First, we view the contents of the tables:

SELECT * FROM employees;

SELECT * FROM departments;

Now we perform the RIGHT JOIN:

--> RIGHT JOIN the employees table to departments table

SELECT employees.name, departments.department_name, employees.salary
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id ;

Below we have an image showing how our query turns out.

Explanation
The query returns all departments from the departments table because it is the right table in the join.

• If an employee belongs to a department, the employee name and salary will be displayed.
• If a department has no employee assigned, the employee columns will contain NULL values as in the above example.

NOTE:

A RIGHT JOIN returns all records from the right table and the matching records from the left table. If no match is found, NULL values are returned for columns from the left table.

FULL OUTER JOIN

A FULL OUTER JOIN returns all rows from both tables, combining the results of a LEFT JOIN and a RIGHT JOIN. If arow from one table does not have a matching row in the other table, the missing side will contain NULL values.

In simple terms, FULL OUTER JOIN keeps everything from both tables and fills in NULL where there is no match.

Here is a general syntax for a FULL OUTER JOIN:

SELECT table_1.column_1, table_2.column_2, ...
FROM table_1
FULL OUTER JOIN table_2
ON table_1.matching_column = table_2.matching_column;

Example
We can view the contents in our tables (optional):

SELECT * FROM employees;

SELECT * FROM departments;

Now we can perform the FULL OUTER JOIN:

--> FULL OUTER JOIN the employees table to departments table

SELECT employees.name, departments.department_name, employees.salary
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.department_id;

Below we have an image showing our query's output.

Explanation
The query returns all records from both the employees and departments tables.

• If an employee belongs to a department, the employee's information and department name are displayed.
• If an employee has no matching department, the department_name column will show NULL.
• If a department has no employees assigned, the employee related columns(name, salary) will show NULL.

In summary, the result contains three types of rows:
Matching rows from both tables
Employees without departments
Departments without employees

NOTE:

A FULL OUTER JOIN returns records from both tables. When there is no match between the tables, the result set fills the missing side with NULL values.

CROSS JOIN

A CROSS JOIN returns the Cartesian product of two tables. This means that each row from first table is combined with every row from the second table.

Cartesian product is the result of ** combining every row from one table with every row from another table**.

So CROSS JOIN takes each row in the first table and pair it with every row from the second table, producing all possible combinations of rows

The general syntax of CROSS JOIN:

SELECT table_1.column_1, table_2.column_2, ...
FROM table_1
CROSS JOIN table_2;

Now, unlike other joins, CROSS JOIN does not require a matching condition ON clause.

We can view the contents of the tables (optional):

SELECT * FROM employees;
SELECT * FROM departments;

We can now perform the CROSS JOIN:

SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;

Below we have two images, the first one is the CROSS JOIN query and the second one is the results we get after running the query.

Explanation
In this query, every employee is combined with every department.

For example, if the employees table has 3 rows and the departments table has 4 rows, the result will contain:

3 x 4 = 12 rows

So, each employee will appear once for every department as we have seen in the query result image 2 above.
This happens because CROSS JOIN creates all possible combinations between the two tables.

NOTE:

A `CROSS JOIN returns the Cartesian product of two tables, meaning every row from the first table is combined with every row from the second table.

NATURAL JOIN

A NATURAL JOIN is a type of SQL join that automatically joins two tables based on column with the same name and compatible data types in both tables.

Unlike other joins, you do not need to explicitly specify the join condition using an ON clause. The database automatically detects the common columns and uses them to perform the join.

In simple terms, `NATURAL JOIN matches rows from two tables using columns that share the same name.

NATURAL JOIN general syntax is;

SELECT column_1, column_2, ...
FROM table_1 
NATURAL JOIN table_2;

Example
In our two tables employees and departments we have a common column department_id.

Image for employees table;

Image for departments table;

We perform NATURAL JOIN to our two tables

SELECT name, department_name
FROM employees 
NATURAL JOIN departments;

Below is an image showing the results of our query

Explanation
Both tables contain the column department_id, which has the same name and compatible data type.
When the NATURAL JOIN is executed, SQL automatically uses this column to match rows between the two tables.
The result will include only rows where the department_id exists in both tables.

• Eve is not included because she has no department_id.
• The Finance department is not included because no employee belongs to it.

NOTE:

A NATURAL JOIN automatically joins tables using columns with the same name in both tables and returns only matching rows.

SELF JOIN

A SELF JOIN is a type of join where a table is joined with itself. This is useful when you want to compare or relate rows within the same table.
In self join, the same table is treated as two separate tables by using table aliases, allowing you to compare records within that table.

In simple terms, a SELF JOIN allows rows in a table to be related to other rows in the same table.

The general syntax of SELF JOIN;

SELECT a.column_name, b.column_name
FROM table_name a
JOIN table_name b
ON a.common_column = b.common_column;

In this case, a and b are aliases representing two instances of the same table.

Example
Let us consider an employees table where each employee may have a manager. The manager is also listed in the same table.

To find each employee and their manager, we can use a SELF JOIN.

SELECT  
    e.name AS employee,
    m.name AS manager
FROM employees e 
LEFT JOIN employees m
ON e.manager_id = m.manager_id ;

Below is an image to show the above query and the output.

Explanation
In the query;

• The employees table is referenced twice.
• e represents the employee
• m represents the manager

The join condition:
e.manager_id = m.employee_id
matches each employee with their corresponding manger.

• Alice and Diana have no manager, so the value is NULL.
• Other employees are matched with their respective managers.

NOTE:

A `SELF JOIN joins a table with itself, allowing relationships between rows within the same table to be analyzed.

Common use Cases for a SELF JOIN
SELF JOIN are useful when working with hierarchical or relational data,such as:

• Employees and their managers
• Organizational structures
• Parent child relationships
• Comparing rows within the same dataset

Conclusion

SQL joins are essential for working with relational databases because data is usually stored across multiple related tables. By using different types of joins, we can combine and analyze this data effectively.

In this article we covered:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN
• CROSS JOIN
• NATURAL JOIN
• SELF JOIN

Each join type serves a different purpose depending on how the data should be matched and returned.

Understanding SQL joins is a foundational skill for anyone working with databases, data analysis, backend development, or data engineering.