Skip to main content
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_idproduct_namecategorypricestock
1Wireless MouseElectronics120025
2Yoga MatFitness80050
3Bluetooth SpeakerElectronics250015
4Water BottleFitness300100
5SmartwatchElectronics500010
Orders Table:
order_idproduct_idorder_datequantity
10112025-01-012
10222025-01-025
10332025-01-021
10452025-01-032
10542025-01-0310
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:
  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:
product_namequantityorder_date
Wireless Mouse22025-01-01
Yoga Mat52025-01-02
Bluetooth Speaker12025-01-02
Smartwatch22025-01-03
Water Bottle102025-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_namequantityorder_date
Wireless Mouse22025-01-01
Yoga Mat52025-01-02
Bluetooth Speaker12025-01-02
Water Bottle102025-01-03
Smartwatch22025-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_namequantityorder_date
Wireless Mouse22025-01-01
Yoga Mat52025-01-02
Bluetooth Speaker12025-01-02
Smartwatch22025-01-03
Water Bottle102025-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_namequantityorder_date
Wireless Mouse22025-01-01
Yoga Mat52025-01-02
Bluetooth Speaker12025-01-02
Smartwatch22025-01-03
Water Bottle102025-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_nameorder_date
Wireless Mouse2025-01-01
Wireless Mouse2025-01-02
Wireless Mouse2025-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!