SQL Practice Online

How to Learn SQL JOIN Types Explained with Visualization

By Cristian G. Guasch • Updated: 03/10/24 • 3 min read

How to Learn SQL JOIN Types Explained with Visualization

If you’re delving into the world of SQL, one concept that’s crucial to grasp is the idea of joins. I’m here to guide you through this important aspect of SQL, explaining not only what they are but how to use them effectively with visualization using Venn diagrams.

Chat with SQL Databases using AI

AskYourDatabase allows you to chat with your SQL & NoSQL databases to gain insights, visualize data, design table schemas, and data anlysis. Compatible with MySQL, PostgreSQL, MongoDB, and SQL Server.

Start Free Trial

Joins in SQL allow us to combine rows from two or more tables based on a related column between them. This means we can pull together data from different sources, painting a fuller picture with our results.

Imagine we have two tables: A and B.

Table A       Table B
id name       id  name
-- ----       --  ----
1  Hacker     1   Peaches
2  Dog        2   Hacker
3  Coder      3   MacGyver
4  Pizza      4   Coder

Now, I will help you join these tables by the name field in different ways. I hope this will help you conceptualize the matches with Venn diagrams.

There are several types of joins in SQL:

  • INNER JOIN: This returns records that have matching values in both tables.
  • LEFT (OUTER) JOIN: It gives all records from the left table and matched records from the right one.
  • RIGHT (OUTER) JOIN: The opposite of Left Join; it provides every record from the right table and matched ones from the left side.
  • FULL (OUTER) JOIN: It’ll offer up all records when there is a match in either left or right table.

INNER JOIN

SQL example:

SELECT * FROM TableA A
INNER JOIN TableB B ON
A.key = B.key

Table results:

id name       id  name
-- ----       --  ----
1  Hacker     2   Hacker
3  Coder      4   Coder

FULL OUTER JOIN

SQL example:

SELECT * FROM TableA A
FULL OUTER JOIN TableB B ON
A.key = B.key

Table results:

id name       id   name
-- ----       --   ----
1  Hacker     2    Hacker
2  Dog        null null
3  Coder      4    Coder
4  Pizza      null null
null null     1    Peaches
null null     3    MacGyver

SQL example:

SELECT * FROM TableA A
FULL OUTER JOIN TableB B ON
A.key = B.key WHERE A.key IS NULL
OR B.key IS NULL

Table results:

id name       id   name
-- ----       --   ----
2  Dog        null null
4  Pizza      null null
null null     1    Peaches
null null     3    MacGyver
Chat with SQL Databases using AI

AskYourDatabase allows you to chat with your SQL & NoSQL databases to gain insights, visualize data, design table schemas, and data anlysis. Compatible with MySQL, PostgreSQL, MongoDB, and SQL Server.

Start Free Trial

LEFT JOIN

SQL example:

SELECT * FROM TableA A
LEFT JOIN TableB B ON
A.key = B.key

Table results:

id  name       id    name
--  ----       --    ----
1   Hacker     2     Hacker
2   Dog        null  null
3   Coder      4     Coder
4   Pizza      null  null

SQL example:

SELECT * FROM TableA A
LEFT JOIN TableB B ON
A.key = B.key WHERE B.key IS NULL

Table results:

id  name       id     name
--  ----       --     ----
2   Dog        null   null
4   Pizza      null   null
Chat with SQL Databases using AI

AskYourDatabase allows you to chat with your SQL & NoSQL databases to gain insights, visualize data, design table schemas, and data anlysis. Compatible with MySQL, PostgreSQL, MongoDB, and SQL Server.

Start Free Trial

Related articles