visual representation of joins sql

Coding Horror

Programming and human factors, a visual explanation of sql joins.

I thought Ligaya Turmelle's post on SQL joins was a great primer for novice developers. Since SQL joins appear to be set-based, the use of Venn diagrams to explain them seems, at first blush, to be a natural fit. However, like the commenters to her post, I found that the Venn diagrams didn't quite match the SQL join syntax reality in my testing.

I love the concept, though, so let's see if we can make it work. Assume we have the following two tables. Table A is on the left, and Table B is on the right. We'll populate them with four records each.

Let's join these tables by the name field in a few different ways and see if we can get a conceptual match to those nifty Venn diagrams.

There's also a cartesian product or cross join , which as far as I can tell, can't be expressed as a Venn diagram:

This joins "everything to everything", resulting in 4 x 4 = 16 rows, far more than we had in the original sets. If you do the math, you can see why this is a very dangerous join to run against large tables.

• SQL SELECT Statement
• SQL ORDER BY and SQL LIMIT
• Using SQL WHERE Clause With Comparison Operators
• Using Logical Operators in WHERE clause
• SQL BETWEEN and SQL LIKE in WHERE Clause
• SQL IS NULL Operator in WHERE Clause
• SQL Data Types
• SQL CASE Clause in SQL Queries
• SQL GROUP BY Clause in a SELECT Query
• Aggregate Functions in SQL
• SQL Having Clause
• SQL Subqueries: The Super Powerful SQL Feature
• SQL DISTINCT Clause
• SQL Date and Time Functions and Data Types
• SQL String Functions
• SQL Correlated Subqueries Increase the Power of SQL
• SQL Window Functions
• SQL Window Function Examples
• Selecting Data From Multiple Tables: SQL JOINS
• Visual Representation of SQL JOINS
• Query Optimization
• SQL Indexes
• How to Compare Arrays in PostgreSQL
• How to Concatenate Strings in PostgreSQL
• How to Convert the Case of a String
• How to Create an Array
• How to Insert Array Data Into a Table
• How to Insert Data Into an Array
• How to Modify Arrays
• How to Query Arrays
• How to Replace Substrings
• How to Trim Strings
• How to Use string_agg()
• How to Use substring()
• How to Use substring() with Regular Expressions
• How to Insert
• How to Update
• How to Delete
• 2. Database Management
• How to Create a Table
• How to Drop a Table
• How to Rename a Table
• How to Truncate a Table
• How to Duplicate a Table
• How to Add a Column
• How to Drop a Column
• How to Rename a Column
• How to Add a Default Value to a Column
• How to Remove a Default Value From a Column
• How to Add a Not Null Constraint
• How to Remove a Not Null Constraint
• How to Create an Index
• How to Drop an Index
• How to Create a View
• How to Drop a View
• How to Alter Sequence
• 3. Dates And Time
• How to Exclude Current or Partial Weeks
• How to Use BETWEEN Correctly
• How to Query Date and Time
• How to Group by Time
• How to Round Timestamps
• How to Convert Local Time to UTC
• 4. Analysis
• How to Use Lateral Joins
• How to Use nullif()
• How to Get the First Row per Group
• How to Use generate_series to Avoid Gaps In Data
• How to Do Type Casting
• How to Write a Common Table Expression
• How to Import a CSV
• How to Compare Two Values When One Is Null
• How to Use Coalesce
• How to Write a Case Statement
• How to Query a JSON Column
• How to Use Filter Clause to Have Multiple Counts
• How to Calculate Cumulative Sum-Running Total
• How to Concatenate Strings in MS-SQL Server
• How to replace a Substring in a String
• How to Create a Table in MS-SQL
• How to Drop a Table in MS-SQL
• How to Rename a Table in MS-SQL
• How to Truncate a Table in MS-SQL
• How to Duplicate a Table in MS-SQL
• How to Add a Column in MS-SQL
• How to Drop a Column in MS-SQL
• How to Rename a Column in MS-SQL
• How to Add a Default Value to a Column in MS-SQL
• How to Remove a Default Value From a Column in MS-SQL
• How to Add a Not Null Constraint in MS-SQL
• How to Remove a Not Null Constraint in MS-SQL
• How to Create an Index in MS-SQL
• How to Drop an Index in MS-SQL
• How to Create a View in MS-SQL
• How to Drop a View in MS-SQL
• How to Alter Sequence in MS-SQL
• How to Query Date and Time in MS-SQL
• How to Extract a Component From a Datetime
• How to Do Type Casting in MS-SQL
• How to Concatenate Strings in MySQL
• How to Trim a String
• How to use group_concat()
• How to do you Use a substring()
• How to use insert into statement
• How to use Update statement in SQL
• How to use Delete SQL Statement
• How to Create a Table in MySQL
• How to Drop a Table in MySQL
• How to Rename a Table in MySQL
• How to Truncate a Table in MySQL
• How to Duplicate a Table in MySQL
• How to Add a Column in MySQL
• How to Drop a Column in MySQL
• How to Rename a Column in MySQL
• How to Add a Default Value to a Column in MySQL
• How to Remove a Default Value From a Column in MySQL
• How to Add a Not Null Constraint in MySQL
• How to Remove a Not Null Constraint in MySQL
• How to Create an Index in MySQL
• How to Drop an Index in MySQL
• How to Create a View in MySQL
• How to Drop a View in MySQL
• How to Alter Sequence in MySQL
• How to Query Date and Time in MySQL
• How to Do Type Casting in MySQL
• How to Concatenate Strings in Oracle
• How to Use listagg()
• How to Concatenate Strings in BigQuery
• How to use substring() function
• How to Create a Database in BigQuery
• How to Create a Table in BigQuery
• How to Drop a Table in BigQuery
• How to Rename a Table in BigQuery
• How to Truncate Table in BigQuery
• How to Duplicate a Table in BigQuery
• How to Add a Column in BigQuery
• How to Drop a Column in BigQuery
• How to Add a Default Value to a Column in BigQuery
• How to Add a Not Null Constraint in BigQuery
• How to Remove a Not Null Constraint in BigQuery
• How to Create a View in BigQuery
• How to Drop a View in BigQuery

SQL Tutorial Home

The examples in this lesson show several SQL JOIN operations and Venn diagrams to show the result sets of records expected for each JOIN type. A non-equality JOIN expression is also shown.

Sample Tables

There are two sample tables, man and woman, with three attributes each: first_name , last_name and marriage_id . You can JOIN both tables using the marriage_id column.

Table Woman

In the SQL JOINS lesson you learned the JOIN clause needs a shared column appearing in both tables to combine them. In the sample tables the column is marriage_id .

Here is a representation of the tables in Venn diagrams:

Here is a query to obtain the men and women who are married:

Here are the resulting records:

For the second SQL JOIN example, suppose you want to obtain the names of all women, married or not. In addition, for women who are married you want the name of the men.

Here is the query to obtain the results:

The query to obtain all women is a LEFT JOIN . You know from the SQL JOINS lesson that LEFT JOIN returns all records from the left side table regardless if they have a match in the right side table.

Here are the resulting records, with NULL set by LEFT JOIN where there is not a match:

You can leverage the NULL assigned to records from the Man table and create a query to obtain Women who are not married .

Here is the query:

Here are the resulting records

SQL FULL JOIN Operator

The SQL FULL JOIN operator returns all matching records from both tables whether the other table matches or not. FULL JOIN and FULL OUTER JOIN are the same.

Using the SQL FULL JOIN operator obtains the records from both tables. NULL is assigned where there is not a matching record:

Obtaining All The Records from Both Tables

In this example, you want to obtain all of the records:

You can reuse the previous query, without the IS NULL comparison for marriage_id :

Here is the result:

There are some cases not covered in this lesson. Most of these cases are symmetric with other covered cases. For example, there was not as example for unmarried men:

The unmarried women example is the mirror image case:

SQL JOIN Using Non-Equality Expressions

Here is a final JOIN example using non-equality expressions.

Suppose you run a shop and your database has a product table with the columns: product_id , name , price and stock .

For the holidays, you want to create a special offer with a bundle of two products, but you need more data to decide which products to include. You want a query to obtain all the possible candidate pairs of products for the bundle meeting your criteria:

• Products in stock with more than 100 units
• Combined price of both products in the range $200 to $350

Here is the query to obtain all the possible product pairs:

Here are the results:

If you do not use the condition P1.product_id > P2.product_id you have even more candidate pairs, including pairs with the same product on both sides and duplicated pairs like <Product1 , Product2 > and <Product2 , Product1> .

Closing Words

This lesson presented visual representations to illustrate the resulting datasets of the JOIN operator. You also learned an interesting query using a JOIN with a non equality condition. Keep going, learn SQL and increase your skills!

IN THIS PAGE

SQL Join types explained visually

Last modified: December 09, 2019

Merging two data sets using SQL or SQL tools can be accomplished through JOINS. A JOIN is a SQL instruction in the FROM clause of your query that is used to identify the tables you are querying and how they should be combined.

Primary and foreign keys

Typically in a  relational database , data is organized into various tables made of attributes (columns) and  records  (rows). In each table there exist a column that is the  primary key  which is a column where each entry uniquely represents a single row in that table. This is usually the ID (short for identifier) column. A column in a table that establishes an association with another table’s primary key via shared values is called a  foreign key . Foreign keys are also typically titled IDs but prepended with the name of the referenced table.

This concept is applied when combining two or more tables together using a JOIN. In the example below, we have two tables: User Table (Table 1) and Event Table (Table 2). We want to join the two tables together to get user data alongside their events data. A real-life example of this would be if you had data from a CRM tool like Salesforce containing users who are paid customers (Table 1) and an events analytics tool like Mixpanel that tracks all the users that have performed an action in your product (Table 2).

Notice that between the two tables there is a common column (dimension) highlighted in green, User ID. In the User Table, the ID column is the user ID and it’s the primary key for that table whereas, in the Event Table, the User_ID column is the foreign key since that column refers to the ID column in the Users table. We can use this relationship to join the two tables together to get the user and events information in one table.

Meet the joins

There are three common ways you can join any two or more tables together we’ll talk about first:  Outer Join ,  Inner Join , and  Left Join . Using the example  User  and  Event  tables above, let’s look at some examples of joins…

Let’s say you want to have a table that contains all your user and event table data together.

You would use an  Outer Join  to join the tables together. An outer join combines the columns from all tables on one or more common dimension when possible, and includes all data from all tables.

For a more detailed look at the  Outer Join click here .

What if you want to have a table that contains only users that have done an action?

You would use an  Inner Join  to join the tables together. An inner join combines the columns on a common dimension (the first N columns) when possible, and only includes data for the columns that share the same values in the common N column(s). In the example, the User ID would be the common dimension used for the inner join.

For a more detailed look at the  Inner Join click here .

Now, what if you want to have a table that contains all the users’ data and only actions that those users have done? Actions performed by other users not in the users table should not be included?

You would use a  Left Join  to join the tables together. A left join combines the columns on a common dimension (the first N columns) when possible, returning all rows from the first table with the matching rows in the consecutive tables. The result is NULL in the consecutive tables when there is no match. In this case, we would make the User Table the first (left table) to use for the left join.

For a more Detailed look at the  Left Join click here .

Union and Cross Join

In addition to these common join types, there are some methods which will result in additional rows in your output table as well as more columns. Two of these join types are called  Union  and  Cross Join . These join types probably wouldn’t be as appropriate for our example tables above, but for the sake of this article we can still use them to see how these joins function. A  Union Join  will stack tables on top of each other resulting in new rows.

For a more detailed look at the  Union Join click here .

A good use case for this would be if you’re looking to combine two tables by appending them rather than joining them. A  Cross Join  would result in a table with all possible combinations of your tables’ rows together. This can result in enormous tables and should be used with caution.

For a more detailed look at the  Cross Join click here .

Cross Joins will likely only be used when your tables contain single values that you want to join together without a common dimension.

Summary cheat sheet

Written by:  Tim Miller Reviewed by:  Matt David

Understanding SQL Joins with Real-World Examples

Learn about different types of SQL joins with practical examples and real-world use cases. This guide covers INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and CROSS JOIN with detailed explanations.

Introduction -->  

SQL joins are a crucial part of relational database management, enabling the combination of data from multiple tables based on related columns. Understanding different types of joins is essential for efficient data querying and manipulation. This guide will walk you through various SQL joins with practical examples and real-world use cases, including INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and CROSS JOIN.

Sample Dataset -->  

We’ll use the following sample data for all the examples:

Types of Joins -->  

1. inner join -->  .

Definition: Returns records that have matching values in both tables. Useful for combining related data from two tables.

Example Query:

Explanation and Usefulness:

• This join retrieves customers who have placed orders.
• Use Case 1: Generating reports that show which customers have made purchases.
• Use Case 2: Identifying active customers for customer relationship management.
• Use Case 3: Determining customer purchase patterns for sales analysis.

2. LEFT JOIN (or LEFT OUTER JOIN) -->  

Definition: Returns all records from the left table (Customers), along with matching records from the right table (Orders). If there is no match, the result will contain NULL values for columns from the right table.

• Lists all customers, showing their orders if they have any.
• Use Case 1: Identifying customers who have not placed any orders.
• Use Case 2: Targeting customers who haven’t purchased recently for marketing campaigns.
• Use Case 3: Providing a complete overview of customer interactions, including those with no recent activity.

3. RIGHT JOIN (or RIGHT OUTER JOIN) -->  

Definition: Returns all records from the right table (Orders), along with matching records from the left table (Customers). If there is no match, the result will contain NULL values for columns from the left table.

• Lists all orders, showing customer details if available.
• Use Case 1: Auditing orders that have no associated customer records.
• Use Case 2: Cleaning and maintaining data integrity by identifying orphaned orders.
• Use Case 3: Providing insights into orders from customers who might not be in the current customer list, useful for updating customer databases.

4. FULL JOIN (or FULL OUTER JOIN) -->  

Definition: Returns all records when there is a match in either left or right table records. The result will contain NULL values for columns from the table that does not have the match.

• Combines all customers and orders, including those without matches in the other table.
• Use Case 1: Generating comprehensive reports that include all customers and orders, highlighting gaps.
• Use Case 2: Ensuring no data is lost during data migration processes.
• Use Case 3: Useful for auditing and reconciliation processes to ensure all data points are accounted for.

5. CROSS JOIN -->  

Definition: Returns the Cartesian product of the two tables, i.e., all possible combinations of rows. Useful when you need to combine all pairs of rows from two tables.

• Generates all possible combinations of products and stores.
• Use Case 1: Creating product availability listings for each store.
• Use Case 2: Planning marketing and promotion by showing which products can be promoted in which stores.
• Use Case 3: Allocating inventory to ensure each store has a plan for stocking each product.

Visual Representation of SQL Joins -->  

To better understand how each type of SQL join works, here are some visual representations using Venn diagrams:

Performance Tips for Using Joins -->  

Optimizing the performance of SQL joins is crucial for handling large datasets efficiently:

• Example: If you frequently join

the Customers and Orders tables on CustomerID , create an index on the CustomerID column in both tables.

Avoid Unnecessary Joins: Only join tables that are necessary for your query to reduce complexity and improve performance.

• Example: If you only need customer names and order dates, avoid joining additional tables that are not required.

Understand Execution Plans: Use your database’s execution plan feature to analyze and optimize your queries. Execution plans show how the database executes a query and can help identify bottlenecks.

• Example: In SQL Server, you can use the EXPLAIN statement to see the execution plan for your query.

Use Proper Join Conditions: Ensure that join conditions are properly specified to avoid cartesian products and unintended results.

• Example: Double-check that your join conditions accurately reflect the relationships between tables.

Minimize Data Transfer: Select only the columns you need to reduce the amount of data transferred and processed.

• Example: Instead of SELECT * , specify the exact columns: SELECT Customers.CustomerName, Orders.OrderDate .

Common Pitfalls -->  

Avoiding common pitfalls can help ensure the accuracy and performance of your SQL queries:

Handling NULL Values: Be cautious with NULL values as they can affect the join results. Ensure your join conditions handle NULLs appropriately.

• Example: When using an INNER JOIN, rows with NULL values in the join column will be excluded from the result set.

Duplicate Records: Ensure that joining columns are unique to avoid duplicate records in the result set.

• Example: If the Orders table has multiple entries for the same CustomerID , an INNER JOIN with the Customers table will result in multiple rows for that customer.

Correct Join Order: The order of joins can affect the performance and result of your query. Make sure to join tables in the correct sequence.

• Example: Joining a large table to a small table first can reduce the amount of data processed in subsequent joins.

Ambiguous Column Names: When joining multiple tables, column names can become ambiguous. Use table aliases to clearly identify columns.

• Example: SELECT C.CustomerName, O.OrderDate FROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID .

Cheat Sheet -->  

Here’s a summary table of the different types of SQL joins, their descriptions, and use cases:

Conclusion -->  

Understanding different types of SQL joins is essential for efficient data querying and manipulation. Each type of join serves a unique purpose and can be applied in various real-world scenarios to solve specific problems and gain valuable insights from data.

Browse Tutorials

“talk is cheap. show me the code.” ― linus torvalds, sql joins - visual representation, you are here.

Visualization of sql joins is very helpful when you are not wrinting sql every day. This example uses MySQL joins, but same applies to any SQL language (postgresql, mssql, etc...). Visual data and query examples should make the subject perfectly clear or you can bookmark it as a good reference. So here it is, the visual representation of sql (mysql) joins.

Equi-Join, Join and Inner Join

• SELECT * FROM table_a , table_b WHERE table_a.id = table_b.id
• SELECT * FROM table_a JOIN table_b ON table_a.id = table_b.id
• SELECT * FROM table_a INNER JOIN table_b ON table_a.id = table_b.id

Left and Right Outer Joins in MySQL

• SELECT * FROM table_b LEFT OUTER JOIN table_a ON table_b.id = table_a.id
• SELECT * FROM table_b RIGHT OUTER JOIN table_a ON table_b.id = table_a.id

Left and Right Outer Joins without Inner

• SELECT * FROM table_a LEFT OUTER JOIN table_b ON table_a.id = table_b.id WHERE table_b.id IS null
• SELECT * FROM table_a RIGHT OUTER JOIN table_b ON table_a.id = table_b.id WHERE table_a.id IS null

Full Outer Join

Full outer join with inner.

• SELECT * FROM table_a LEFT OUTER JOIN table_b ON table_a.id = table_b.id
• SELECT * FROM table_a RIGHT OUTER JOIN table_b ON table_a.id = table_b.id