Top 6 Joins and Subqueries in SQL for Powerful Data Insights

Mastering Joins and Subqueries in SQL

As we advance in SQL, the ability to work across multiple tables and build complex queries becomes essential. Joins allow you to combine data from multiple tables, creating richer, more insightful views of your data, while subqueries help break down complex queries into simpler components, making them easier to manage and understand.

In our last blog, Advanced SQL Grouping and Filtering Techniques, we explored essential concepts like Grouping and Aggregating Data with GROUP BY and Filtering Groups with the HAVING Clause. These techniques help you organize and filter data for meaningful analysis within a single table. Building on these skills, we’re ready to dive into joins and subqueries, enabling querying across multiple tables and creating powerful data manipulations.

This next step in SQL mastery will equip you to handle more complex data scenarios, transforming your SQL capabilities for real-world applications.

Understanding JOINs in SQL

In SQL, a JOIN is used to combine rows from two or more tables based on a common column between them. Joins are essential for analyzing data across multiple tables, enabling a comprehensive view of relationships within your database. Each type of join serves a unique purpose, depending on how you want to structure and align the data.

In this section, we’ll set up sample tables and illustrate each join type with practical examples to help you understand their purpose and usage.

Setting Up the Tables for Join Examples

To demonstrate how joins work, we’ll use two sample tables: Employees and Departments. These tables allow us to showcase various join scenarios, such as retrieving employees with assigned departments, identifying unassigned employees, and listing departments that may not currently have employees.

Creating the “Employees” Table:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    department_id INTEGER
);

Creating the “Departments” Table:

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(50)
);

Inserting Sample Data into “Employees”:

INSERT INTO employees (name, department_id) VALUES
('Alice', 1),
('Bob', 2),
('Charlie', 3),
('David', NULL);  -- David has no assigned department

Inserting Sample Data into “Departments”:

INSERT INTO departments (department_name) VALUES
('HR'),       -- department_id = 1
('IT'),       -- department_id = 2
('Finance'),  -- department_id = 3
('Marketing'); -- department_id = 4

Sample Tables

After setting up the tables, we’ll have the following data:

Table 1: Employees

id	name	department_id
1	Alice	1
2	Bob	2
3	Charlie	3
4	David	NULL

Table 2: Departments

department_id	department_name
1	HR
2	IT
3	Finance
4	Marketing

These tables will serve as the foundation for our join examples. With them, we’ll explore different types of joins—Inner, Left, Right, Full, and Cross Join—to see how SQL can combine data across tables and produce insightful results.

Types of JOINs

Full Outer Join

A Full Outer Join returns all rows from both tables. If there isn’t a match between rows, the result set will contain NULL values for columns of the unmatched table. This type of join is particularly useful when you need a complete view of both tables, including rows that don’t have a corresponding match in the other table.

Query:

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

Result:

name	department_name
Alice	HR
Bob	IT
Charlie	Finance
David	NULL
NULL	Marketing

In this result:

• Alice, Bob, and Charlie have matched records in both tables, displaying their corresponding departments.
• David is an employee without an assigned department, so the department_name column shows NULL.
• Marketing is a department with no employees assigned, so the name column shows NULL.

This example highlights the flexibility of Full Outer Joins in capturing all data from both tables, filling in gaps with NULL values when matches aren’t found.

Inner Join

An Inner Join combines rows from both tables where there is a match between the specified columns. In this case, we’re matching rows from the employees and departments tables based on the department_id. If there isn’t a matching department_id, the row is excluded from the result.

Query:

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

Result:

name	department_name
Alice	HR
Bob	IT
Charlie	Finance

In this result:

• Only employees with a matching department_id in the departments table are displayed.
• David, who does not have an assigned department (department_id is NULL), is excluded from the result.
• The Marketing department, which has no employees assigned, is also excluded.

The Inner Join is ideal for scenarios where you only want rows with corresponding data in both tables, giving you a focused view of records with a direct relationship.

Left Join

A Left Join retrieves all rows from the left table (in this case, employees) and the matched rows from the right table (departments). If there isn’t a matching row in the right table, the result will include NULL values for the right table’s columns. This join type is useful when you want to see all records from one table, even if they don’t have corresponding data in the other.

Query:

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

Result:

name	department_name
Alice	HR
Bob	IT
Charlie	Finance
David	NULL

In this result:

• Alice, Bob, and Charlie have matching department names displayed based on their department_id.
• David, who doesn’t have an assigned department_id, is included in the results, with the department_name column showing NULL.

The Left Join is ideal for cases where you want to include all records from the left table, even if there’s no matching data in the right table, providing a comprehensive view of the primary dataset.

Right Join

A Right Join retrieves all rows from the right table (in this case, departments) and the matched rows from the left table (employees). If there isn’t a matching row in the left table, the result will include NULL values for the left table’s columns. This join type is useful when you want to see all records from one table (departments), even if they don’t have corresponding entries in the other table (employees).

Query:

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

Result:

name	department_name
Alice	HR
Bob	IT
Charlie	Finance
NULL	Marketing

In this result:

• Alice, Bob, and Charlie have matching department names displayed based on their department_id.
• Marketing, a department with no employees assigned, is included in the results with the name column showing NULL.

The Right Join is ideal for cases where you want to include all records from the right table, even if there’s no matching data in the left table, providing a complete view of the department data and highlighting departments with no current employees.

Cross Join Example

A Cross Join returns the Cartesian product of two tables, which means it pairs every row in the first table with every row in the second table. This join type doesn’t require a matching condition and is commonly used when you need all possible combinations between two datasets.

Query:

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

Result:

name	department_name
Alice	HR
Alice	IT
Alice	Finance
Alice	Marketing
Bob	HR
Bob	IT
Bob	Finance
Bob	Marketing
Charlie	HR
Charlie	IT
Charlie	Finance
Charlie	Marketing
David	HR
David	IT
David	Finance
David	Marketing

In this result, each employee is paired with every department, resulting in all possible combinations of employee-department pairs.

Use Case: While Cross Joins are less commonly used, they’re helpful when generating all possible pairings between two sets, such as matching each employee with every potential project or every product with every promotion for comparison purposes.

Self Joins for Advanced Data Analysis

A Self Join is used when you need to join a table with itself. This is particularly useful for comparing rows within the same table, such as identifying employees who share the same department or finding relationships within a single dataset. Self Joins are helpful in scenarios where related data exists within the same table rather than across multiple tables.

Example: Find employees who work in the same department.

Query:

SELECT e1.name AS "Employee 1", e2.name AS "Employee 2", e1.department_id
FROM employees e1
INNER JOIN employees e2 ON e1.department_id = e2.department_id
WHERE e1.id <> e2.id;

Result:

Employee 1	Employee 2	department_id
Alice	Bob	1
Alice	Charlie	1
Bob	Alice	1
Bob	Charlie	1
Charlie	Alice	1
Charlie	Bob	1

In this result:

• The query pairs each employee with others in the same department by joining the employees table to itself.
• The WHERE e1.id <> e2.id condition ensures that an employee isn’t matched with themselves.

Use Case: Self Joins are particularly useful for tasks like identifying team members in the same department, finding people with the same manager, or analyzing hierarchical data within a single table. This approach enables advanced analysis and comparisons that may not be possible with simple joins.

Using UNION and UNION ALL for Merging Results

The UNION and UNION ALL operators are used to combine the results of two or more SELECT statements. They enable merging datasets with similar columns, whether from different tables or the same table. The primary difference between the two operators is that UNION removes duplicates, while UNION ALL keeps all rows, including duplicates.

UNION: Removing Duplicates

The UNION operator combines result sets while removing duplicate rows. It’s ideal for merging data when you only want unique entries in the final output.

Example: Retrieve all unique names from the employees and departments tables, combining employee names and department names without duplicates.

Query:

SELECT name AS "Name" FROM employees
UNION
SELECT department_name FROM departments;

Result:

Name
Alice
Bob
Charlie
David
HR
IT
Finance
Marketing

In this result:

• Each name appears only once, whether it’s from the employees or departments table, ensuring uniqueness in the merged list.

UNION ALL: Including Duplicates

The UNION ALL operator combines results without removing duplicates, making it useful when you need to retain all values, including any repeated ones.

Example: Combine the list of employee names and department names, including duplicates.

Query:

SELECT name AS "Name" FROM employees
UNION ALL
SELECT department_name FROM departments;

Result:

Name
Alice
Bob
Charlie
David
HR
IT
Finance
Marketing

In this result:

• All entries from both tables are displayed, including any duplicates if there were common names between employees and departments.

Use Case:

• UNION is beneficial when merging lists while ensuring unique entries, like combining customer and supplier names without overlap.
• UNION ALL is preferred when duplicates are needed, such as consolidating all transactions, including repeated values, for comprehensive analysis.

With UNION and UNION ALL, SQL offers versatile tools for merging datasets, allowing for seamless data integration in reporting and analysis. For a deeper dive into the differences and applications of these operators, read this resource: UNION vs UNION ALL.

Working with Subqueries

A Subquery is a query nested within another query, allowing you to perform more advanced filtering, calculations, and data manipulation. Subqueries enable you to break down complex operations into simpler, manageable steps. They can be used in various parts of a main query, including the WHERE, FROM, and SELECT clauses.

Subquery in WHERE Clause

Using a subquery in the WHERE clause allows you to filter results based on values derived from another query.

Example: Retrieve employees who earn more than the average salary.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

In this example:

• The subquery (SELECT AVG(salary) FROM employees) calculates the average salary.
• The main query then retrieves employees with a salary above this average.

Subquery in FROM Clause

Subqueries in the FROM clause are often used to create temporary tables (also called derived tables) for additional processing in the main query.

Example: Find the average salary of each department using a subquery.

SELECT department_name, avg_salary
FROM (SELECT department_id, AVG(salary) AS avg_salary
      FROM employees
      GROUP BY department_id) AS department_averages
INNER JOIN departments ON department_averages.department_id = departments.department_id;

In this example:

• The inner subquery calculates the average salary for each department.
• The main query joins this result with the departments table to display the department names alongside their average salaries.

Subquery in SELECT Clause

A subquery in the SELECT clause can be used to calculate a value for each row, based on related data. This is especially helpful for comparisons within groups.

Example: Display each employee’s salary and how it compares to the department average.

SELECT name, salary,
       (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = employees.department_id) AS department_avg
FROM employees;

Result:

name	salary	department_avg
Alice	75000	75000
Bob	55000	55000
Charlie	60000	60000

In this result:

• Each employee’s salary is compared to the average salary of their respective department.
• The subquery (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = employees.department_id) calculates the department average for each employee, allowing for a row-by-row comparison.

Subqueries add depth to SQL queries by enabling complex calculations and conditions based on related data. By leveraging subqueries in different clauses, you can perform advanced analysis and gain insights that would be challenging with simple joins or filters alone.

Conclusion

Mastering joins and subqueries in SQL opens up powerful capabilities for working with relational data. By understanding and applying different types of joins, you can combine information across multiple tables, creating a more comprehensive view of your data. Subqueries further enhance SQL’s versatility, allowing you to break down complex queries into manageable parts and enabling advanced filtering and calculations. Together, these tools make SQL an invaluable asset for tackling real-world data analysis challenges, equipping you to extract deeper insights and improve decision-making.

FAQs