SQL types (including CROSS JOIN and FULL JOIN)

18 June 2023

Hello! Today, I want to tackle a topic that seems to befuddle many beginner developers but is absolutely crucial in daily work with databases. I’m talking about JOIN types in SQL. And not only the basic ones – we will have a look even at cross join!

A JOIN operation allows you to combine records from different tables based on certain criteria. Imagine having two puzzles – a JOIN is how these puzzles are assembled together.

INNER JOIN

Let’s start with the most popular one – INNER JOIN. This operation returns only those records that have matching values in both tables. It’s like making friends at a party only with those you know from both work and school.

Example query:

SELECT 
  Orders.order_id
  ,Customers.customer_name
FROM Orders
INNER JOIN Customers ON Orders.customer_id = Customers.customer_id;

In this case, only those orders (Orders) for which we have a corresponding customer (Customers) will be returned.

LEFT JOIN (and RIGHT JOIN)

Next on the list is LEFT JOIN, which returns all records from the left table and matching records from the right table. If there’s no match, the result is NULL.

Example query:

SELECT 
  Orders.order_id
  ,Customers.customer_name
FROM Orders
LEFT JOIN Customers ON Orders.customer_id = Customers.customer_id;

In this case, all orders will be returned, even those for which we don’t have a corresponding customer.

RIGHT JOIN works on the same principle, just in the opposite direction – it returns all records from the right table and the matching records from the left.

SELECT 
  Orders.order_id
  ,Customers.customer_name
FROM Orders
RIGHT JOIN Customers ON Orders.customer_id = Customers.customer_id;

FULL OUTER JOIN

FULL OUTER JOIN is like a bit of a desperado, who invites everyone to the party regardless of whether he knew them before or not. This operation returns all records when there’s a match in either the left or right table.

Example query:

SELECT 
  Orders.order_id
  ,Customers.customer_name
FROM Orders
FULL OUTER JOIN Customers ON Orders.customer_id = Customers.customer_id;

In this case, all orders and all customers will be returned, regardless of whether there’s a customer for the order or an order for the customer.

CROSS JOIN

Lastly, we have CROSS JOIN, which is a kind of Cartesian product operation. Each record from the first table is combined with each record from the second table. It’s like throwing a party where everyone talks to everyone – sounds like a blast, but it’s probably going to end in chaos!

Example query:

SELECT 
  Orders.order_id
  ,Customers.customer_name
FROM Orders
CROSS JOIN Customers;

In this case, each order will be combined with each customer, regardless of whether this customer actually placed that order or not.

That’s all for today! I hope I’ve managed to convey the basic differences between the types of JOINs in SQL. Remember that practice makes perfect, so get to it – may the power of JOINs be with you!

That’s all on this topic. Analyze in peace!

Did you like this article 🙂?
Share it on Social Media 📱
>>> You can share it on LinkedIn and show that you learn something new every day.
>>> You can throw it on Facebook – and perhaps help a friend of yours who is looking for this.
>>> And remember to bookmark this page, you never know if it won’t come handy in in the future.

You prefer to watch 📺 – no problem
>>> Subscribe and watch my English channel on YouTube.