SQL joins without Venn diagrams

After learning the SQL basics, you’ll be faced with your first big learning curve: JOINs. There is a high likelihood that you won’t find everything you need in a single table and will have to learn when to join.

Venn diagrams are used in many JOIN guides to explain what they mean, but I prefer something more visual.

INNER JOIN
LEFT JOIN
CROSS JOIN
UNION
UNION ALL

INNER JOIN

Use an INNER join, or just a JOIN, when you want to find the match between two tables.

You need to have a column on both tables that you join ON, and that’s where the match happens.

Any results where there is not a match are discarded.

select
  o.order_item, 
  i.inventory_item
from 
  orders o
inner join inventory i 
on o.order_item = i.inventory_item

select

o.order_item,

i.inventory_item

from

orders o

inner join inventory i

on o.order_item = i.inventory_item

joins sql server query join — Inner Join

LEFT JOIN

Use a LEFT JOIN when you want to find a match between two tables, but also show a NULL where there is no match from the right table. A RIGHT JOIN does the same but in reverse.

Like the INNER JOIN, you need a column to join ON. Unlike the INNER JOIN, a NULL is used to show there is no match between the two tables.

select
  o.order_item,  -- this is the left table
  i.inventory_item -- this is the right table
from 
  orders o
left join inventory i 
on o.order_item = i.inventory_item

select

o.order_item, -- this is the left table

i.inventory_item -- this is the right table

from

orders o

left join inventory i

on o.order_item = i.inventory_item

CROSS JOIN

A CROSS JOIN joins everything with everything. There is no need to provide a column to join on, and it can result in a very big data set, (and a really big image so you’ll have to use your imagination when reviewing the image below).

Proceed with caution.

select
  o.order_item, 
  i.inventory_item
from 
  orders o
cross join inventory i

select

o.order_item,

i.inventory_item

from

orders o

cross join inventory i

cross join sql server query — Cross Join

UNION

While a JOIN combines columns horizontally a UNION combines rows vertically. This is technically a set operator rather than a JOIN but as we are talking about combining datasets this is a good opportunity to introduce it.

Using a UNION combines the result of two queries into one column and removes duplicates.

If your query has multiple columns, they need to be in the same order to complete the UNION.

select *
from
 orders
union
select *
 from
inventory

select *

from

orders

union

select *

from

inventory

UNION ALL

The UNION ALL combines the results of two queries the same as a UNION but keeps the duplicates in the result.

select *
from
 orders
union all
select *
from
 inventory

select *

from

orders

union all

select *

from

inventory

The best way to get your head around JOINs is to start using them. If you aren’t working with a SQL database already, check out SQLZoo or Hacker Rank to experiment with JOINs.

Photo by Dmitry Demidov from Pexels