Understanding Joins in SQL

You’ve learned to query a single table like a pro. But what if you want to combine data from multiple tables? That’s where joins come in. Joins allow you to connect related tables based on a common column, giving you the complete picture.

Let’s dive into joins step by step using a scenario and examples, sticking to SQL Server syntax for consistency.

​

Scenario

Imagine your online store now has two tables:

Products Table:

Orders Table:

We’ll use these tables to explore joins.

​

What is a Join?

​

Types of Joins

SQL Server supports several types of joins:

1. INNER JOIN: Returns rows with matching values in both tables.
2. LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right table. Missing matches are filled with NULL.
3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and matching rows from the left table. Missing matches are filled with NULL.
4. FULL JOIN (or FULL OUTER JOIN): Returns all rows when there’s a match in either table. Missing values are filled with NULL.
5. CROSS JOIN: Returns the Cartesian product of both tables (every combination of rows).

Let’s explore these step by step.

​

INNER JOIN: Matching Rows Only

Suppose you want to find out the products that have been ordered along with the quantity.

SELECT p.product_name, o.quantity, o.order_date  
FROM products p  
INNER JOIN orders o  
ON p.product_id = o.product_id;  

Result:

Here, only rows with matching product_id values in both tables are returned.

​

LEFT JOIN: Include All Rows from the Left Table

What if you want to see all products, even those that haven’t been ordered? Use a LEFT JOIN.

SELECT p.product_name, o.quantity, o.order_date  
FROM products p  
LEFT JOIN orders o  
ON p.product_id = o.product_id;  

Result:

This includes rows from products even if there’s no matching product_id in orders.

​

RIGHT JOIN: Include All Rows from the Right Table

Similarly, if you want to see all orders, even if the products are missing in the products table, use a RIGHT JOIN.

SELECT p.product_name, o.quantity, o.order_date  
FROM products p  
RIGHT JOIN orders o  
ON p.product_id = o.product_id;  

Result:

​

FULL JOIN: All Rows from Both Tables

To get all rows from both tables, use a FULL JOIN.

SELECT p.product_name, o.quantity, o.order_date  
FROM products p  
FULL JOIN orders o  
ON p.product_id = o.product_id;  

Result:

This will include rows that have no match in either table, filling in NULL values where necessary.

​

CROSS JOIN: Every Combination

A CROSS JOIN produces a combination of every row in the first table with every row in the second table. We have 5 rows in products table and orders table. So we will get the result with 25 rows.

SELECT p.product_name, o.order_date  
FROM products p  
CROSS JOIN orders o;  

Result (Truncated for brevity):

​

Summary

Joins are the cornerstone of working with relational databases. Whether you’re matching rows with INNER JOIN, including unmatched data with LEFT JOIN or RIGHT JOIN, or diving deep with FULL JOIN, mastering these concepts will unlock the full potential of SQL.

In the next article, we’ll cover subqueries—queries within queries—to extract even more nuanced insights from your data. Keep practicing!