Database Joins: An Interactive Exploration

Rarely will you find a database with all the information you need in a single table. In most cases, data is spread across multiple tables inside the database, requiring you to combine them to extract meaningful insights. This is where SQL joins come into play. Joins connect data from multiple tables through related columns, enabling you to perform complex queries and analyses. By using a common column—often a foreign key—you can effectively determine how rows are combined.

This document presents an interactive web application that visualizes different types of SQL joins to help you understand how they work and when to use them.

Key Concepts

  1. INNER JOIN: Returns only the matching rows from both tables.
  2. LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
  3. RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
  4. FULL OUTER JOIN: Returns all rows from both tables, with NULL values where there’s no match.
  5. CROSS JOIN: Returns the Cartesian product of both tables (all possible combinations).
  6. SEMI JOIN: Returns rows from the left table where there is a match in the right table, without duplicating rows from the left table.
  7. ANTI JOIN: Returns rows from the left table where there is no match in the right table.

Employees and Departments Database Demo

Consider a database with two tables: Employees and Departments. The Employees table contains information about employees, including their employee ID, name, and department ID. The Departments table contains information about departments in the company, including their department ID and name. The two tables are related by the departmentID column in the Employees and Departments tables. The departmentID column in the Employees table is a foreign key that references the ID column in the Departments table, establishing a relationship between the two tables.

We’ve populated these tables with sample data to demonstrate different types of joins. The data includes employees from various departments, including some without a department assignment, and departments that may not have any employees. This setup allows us to explore different join types and their effects on the resulting dataset.

Try out the following actions:

  • Toggle between different join types to see how they affect the result.
  • Modify the data in the Employees and Departments tables to observe the changes in the join result.

While exploring the different join types, pay attention to the following:

  • The number of rows in the result set.
  • The columns included in the result set.
  • The values in the result set and how they are populated.
  • The impact of NULL values on the result set.

If you encounter any issues or want to start fresh, you can reset the demo to its initial state by clicking the “Reset Demo” button.

Open in a new tab

Important

When specifying the join conditions between tables, you can use either the ON clause or the WHERE clause. The choice between the two can affect the result set, especially when dealing with NULL values. In particular, we have:

  1. WHERE clause: Filters records after the join, potentially eliminating NULL results from the non-matching rows of the preserved table.
  2. ON clause: Filters before joining, preserving NULL results for non-matching rows from the outer table.

Conclusion

Through the interactive app, we explored different types of SQL joins on the Employees and Departments tables. The focus of our joins was on the relationship between employees and departments, where the departmentID column served as the common key. By experimenting with different join types, we hope you saw how they affect the result set and how they can be used to combine. In particular, you may have noticed the following:

  • INNER JOIN: Only returns rows with matching department IDs in both tables.
  • LEFT JOIN: Includes all employees, even those without a department assignment.
  • RIGHT JOIN: Includes all departments, even those without any employees.
  • FULL OUTER JOIN: Combines all rows from both tables, filling in NULL values where there’s no match.
  • CROSS JOIN: Generates all possible combinations of employees and departments.
  • SEMI JOIN: Filters employees based on the existence of a matching department.
  • ANTI JOIN: Filters employees based on the non-existence of a matching department.