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:

product_id	product_name	category	price	stock
1	Wireless Mouse	Electronics	1200	25
2	Yoga Mat	Fitness	800	50
3	Bluetooth Speaker	Electronics	2500	15
4	Water Bottle	Fitness	300	100
5	Smartwatch	Electronics	5000	10

Orders Table:

order_id	product_id	order_date	quantity
101	1	2025-01-01	2
102	2	2025-01-02	5
103	3	2025-01-02	1
104	5	2025-01-03	2
105	4	2025-01-03	10

We’ll use these tables to explore joins.

What is a Join?

A join connects two or more tables by matching rows using a common column, typically a primary key in one table and a foreign key in the other.

Types of Joins

SQL Server supports several types of joins:

INNER JOIN: Returns rows with matching values in both tables.
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.
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.
FULL JOIN (or FULL OUTER JOIN): Returns all rows when there’s a match in either table. Missing values are filled with NULL.
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:

product_name	quantity	order_date
Wireless Mouse	2	2025-01-01
Yoga Mat	5	2025-01-02
Bluetooth Speaker	1	2025-01-02
Smartwatch	2	2025-01-03
Water Bottle	10	2025-01-03

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:

product_name	quantity	order_date
Wireless Mouse	2	2025-01-01
Yoga Mat	5	2025-01-02
Bluetooth Speaker	1	2025-01-02
Water Bottle	10	2025-01-03
Smartwatch	2	2025-01-03

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:

product_name	quantity	order_date
Wireless Mouse	2	2025-01-01
Yoga Mat	5	2025-01-02
Bluetooth Speaker	1	2025-01-02
Smartwatch	2	2025-01-03
Water Bottle	10	2025-01-03

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:

product_name	quantity	order_date
Wireless Mouse	2	2025-01-01
Yoga Mat	5	2025-01-02
Bluetooth Speaker	1	2025-01-02
Smartwatch	2	2025-01-03
Water Bottle	10	2025-01-03

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 rows.

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

Result (Truncated for brevity):

product_name	order_date
Wireless Mouse	2025-01-01
Wireless Mouse	2025-01-02
Wireless Mouse	2025-01-03
…

Use CROSS JOIN sparingly, as the result grows exponentially.

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!

​Scenario

​What is a Join?

​Types of Joins

​INNER JOIN: Matching Rows Only

​LEFT JOIN: Include All Rows from the Left Table

​RIGHT JOIN: Include All Rows from the Right Table

​FULL JOIN: All Rows from Both Tables

​CROSS JOIN: Every Combination

​Summary

Scenario

What is a Join?

Types of Joins

INNER JOIN: Matching Rows Only

LEFT JOIN: Include All Rows from the Left Table

RIGHT JOIN: Include All Rows from the Right Table

FULL JOIN: All Rows from Both Tables

CROSS JOIN: Every Combination

Summary