Learn to combine data from multiple tables using SQL JOIN operations
So far, we've been working with a single table at a time. But one of the most powerful aspects of relational databases is their ability to store related data in separate tables and then combine that data when needed using JOINs.
This approach has several advantages:
In this lesson, we'll learn how to use SQL's JOIN operations to combine data from multiple tables. We'll explore different types of joins, from the basic INNER JOIN to more specialized operations like LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. We'll also see how to join more than two tables and how to combine joins with other SQL operations like WHERE, GROUP BY, and ORDER BY.
These joining techniques are fundamental to working with relational databases and will enable you to answer complex questions by bringing together data from different parts of your database.
Before diving into joins, let's review some core concepts of relational databases:
Relational databases store data in multiple tables that are connected through relationships. These relationships are defined by matching values in columns (usually primary and foreign keys).
For example, in an e-commerce database:
customers table might store customer information (name, address, etc.)orders table might store order information (date, status, etc.)orders table would have a customer_id column that references the id column in the customers tableThis creates a one-to-many relationship: one customer can have many orders.
Database normalization is the process of organizing tables to:
In a normalized database, data is split across multiple related tables rather than being stored in a single, large table. This is where JOINs become essential – they allow you to recombine this data when needed.
A JOIN clause combines rows from two or more tables based on a related column between them.
1SELECT columns
2FROM table1
3JOIN table2 ON table1.column_name = table2.column_name;The ON clause specifies the condition for matching rows between the tables.
Think of joins as Venn diagrams between two tables:
For clarity and brevity, it's common to use table aliases, especially when joining multiple tables:
1SELECT o.order_id, c.customer_name
2FROM orders o
3JOIN customers c ON o.customer_id = c.customer_id;Here, o is an alias for the orders table, and c is an alias for the customers table.
The INNER JOIN returns rows when there is a match in both tables. This is the most common type of join.
1SELECT columns
2FROM table1
3INNER JOIN table2 ON table1.column_name = table2.column_name;Note: The keyword INNER is optional; simply writing JOIN defaults to an inner join.
Consider these two tables:
customers
1| customer_id | customer_name | email |
2|-------------|---------------|-------------------|
3| 1 | John Smith | [email protected] |
4| 2 | Jane Doe | [email protected] |
5| 3 | Bob Johnson | [email protected] |orders
1| order_id | customer_id | order_date | amount |
2|----------|-------------|------------|--------|
3| 101 | 1 | 2023-01-15 | 150.00 |
4| 102 | 1 | 2023-02-03 | 75.50 |
5| 103 | 2 | 2023-02-10 | 200.00 |
6| 104 | 4 | 2023-02-15 | 125.75 |To find all orders with customer information:
1SELECT o.order_id, c.customer_name, o.order_date, o.amount
2FROM orders o
3INNER JOIN customers c ON o.customer_id = c.customer_id;Result:
1| order_id | customer_name | order_date | amount |
2|----------|---------------|------------|--------|
3| 101 | John Smith | 2023-01-15 | 150.00 |
4| 102 | John Smith | 2023-02-03 | 75.50 |
5| 103 | Jane Doe | 2023-02-10 | 200.00 |Note that:
You can use multiple conditions in the ON clause:
1SELECT p.product_id, p.product_name, o.order_id
2FROM products p
3INNER JOIN order_items oi ON p.product_id = oi.product_id AND oi.quantity > 1
4INNER JOIN orders o ON oi.order_id = o.order_id;This query finds all products that were ordered in quantities greater than 1.
A LEFT JOIN returns all rows from the left table and matching rows from the right table. If there's no match, NULL values are returned for the right table's columns.
1SELECT columns
2FROM table1
3LEFT [OUTER] JOIN table2 ON table1.column_name = table2.column_name;The keyword OUTER is optional.
Using the same customers and orders tables:
1SELECT c.customer_id, c.customer_name, o.order_id, o.amount
2FROM customers c
3LEFT JOIN orders o ON c.customer_id = o.customer_id;Result:
1| customer_id | customer_name | order_id | amount |
2|-------------|---------------|----------|--------|
3| 1 | John Smith | 101 | 150.00 |
4| 1 | John Smith | 102 | 75.50 |
5| 2 | Jane Doe | 103 | 200.00 |
6| 3 | Bob Johnson | NULL | NULL |Note that:
A common use of LEFT JOIN is to find records in the left table that have no matching records in the right table:
1SELECT c.customer_id, c.customer_name
2FROM customers c
3LEFT JOIN orders o ON c.customer_id = o.customer_id
4WHERE o.order_id IS NULL;This returns customers who have never placed an order:
1| customer_id | customer_name |
2|-------------|---------------|
3| 3 | Bob Johnson |A RIGHT JOIN returns all rows from the right table and matching rows from the left table. If there's no match, NULL values are returned for the left table's columns.
1SELECT columns
2FROM table1
3RIGHT [OUTER] JOIN table2 ON table1.column_name = table2.column_name;The keyword OUTER is optional.
Using the same customers and orders tables:
1SELECT c.customer_id, c.customer_name, o.order_id, o.amount
2FROM customers c
3RIGHT JOIN orders o ON c.customer_id = o.customer_id;Result:
1| customer_id | customer_name | order_id | amount |
2|-------------|---------------|----------|--------|
3| 1 | John Smith | 101 | 150.00 |
4| 1 | John Smith | 102 | 75.50 |
5| 2 | Jane Doe | 103 | 200.00 |
6| NULL | NULL | 104 | 125.75 |Note that:
Similar to using LEFT JOIN to find records with no matches, RIGHT JOIN can find records in the right table that have no matching records in the left table:
1SELECT o.order_id, o.customer_id
2FROM customers c
3RIGHT JOIN orders o ON c.customer_id = o.customer_id
4WHERE c.customer_id IS NULL;This returns orders with no matching customer:
1| order_id | customer_id |
2|----------|-------------|
3| 104 | 4 |Note that LEFT JOIN and RIGHT JOIN are functionally equivalent - you can always rewrite one as the other by swapping the order of the tables:
1-- These queries produce the same result
2SELECT columns FROM table1 LEFT JOIN table2 ON table1.col = table2.col;
3SELECT columns FROM table2 RIGHT JOIN table1 ON table2.col = table1.col;Most SQL developers prefer to consistently use LEFT JOIN rather than mixing LEFT and RIGHT JOINs, as it makes queries easier to understand.
A FULL OUTER JOIN returns all rows from both tables, matching them where possible. Where there's no match, NULL values are used for the missing side's columns.
1SELECT columns
2FROM table1
3FULL [OUTER] JOIN table2 ON table1.column_name = table2.column_name;The keyword OUTER is optional.
Using the same customers and orders tables:
1SELECT c.customer_id, c.customer_name, o.order_id, o.amount
2FROM customers c
3FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;Result:
1| customer_id | customer_name | order_id | amount |
2|-------------|---------------|----------|--------|
3| 1 | John Smith | 101 | 150.00 |
4| 1 | John Smith | 102 | 75.50 |
5| 2 | Jane Doe | 103 | 200.00 |
6| 3 | Bob Johnson | NULL | NULL |
7| NULL | NULL | 104 | 125.75 |Note that:
FULL OUTER JOIN can be used to find all records in either table that have no match in the other table:
1SELECT c.customer_id, c.customer_name, o.order_id, o.customer_id AS order_customer_id
2FROM customers c
3FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
4WHERE c.customer_id IS NULL OR o.customer_id IS NULL;Result:
1| customer_id | customer_name | order_id | order_customer_id |
2|-------------|---------------|----------|-------------------|
3| 3 | Bob Johnson | NULL | NULL |
4| NULL | NULL | 104 | 4 |Not all database systems support FULL OUTER JOIN. In MySQL, for example, you can simulate a FULL OUTER JOIN using a combination of LEFT JOIN, UNION, and RIGHT JOIN:
1-- MySQL FULL OUTER JOIN equivalent
2SELECT c.customer_id, c.customer_name, o.order_id, o.amount
3FROM customers c
4LEFT JOIN orders o ON c.customer_id = o.customer_id
5UNION ALL
6SELECT c.customer_id, c.customer_name, o.order_id, o.amount
7FROM customers c
8RIGHT JOIN orders o ON c.customer_id = o.customer_id
9WHERE c.customer_id IS NULL;A CROSS JOIN returns the Cartesian product of two tables, meaning it combines each row from the first table with each row from the second table. Unlike other joins, a CROSS JOIN doesn't require an ON clause.
1SELECT columns
2FROM table1
3CROSS JOIN table2;Alternatively, you can use implicit syntax:
1SELECT columns
2FROM table1, table2;Consider these small tables:
colors
1| color_id | color_name |
2|----------|------------|
3| 1 | Red |
4| 2 | Blue |
5| 3 | Green |sizes
1| size_id | size_name |
2|---------|-----------|
3| 1 | Small |
4| 2 | Medium |
5| 3 | Large |A CROSS JOIN would create every possible combination:
1SELECT c.color_name, s.size_name
2FROM colors c
3CROSS JOIN sizes s;Result:
1| color_name | size_name |
2|------------|-----------|
3| Red | Small |
4| Red | Medium |
5| Red | Large |
6| Blue | Small |
7| Blue | Medium |
8| Blue | Large |
9| Green | Small |
10| Green | Medium |
11| Green | Large |CROSS JOINs are useful for:
Be careful with CROSS JOINs on large tables! The result set size is the product of the row counts of both tables. If table1 has 1,000 rows and table2 has 1,000 rows, the result will have 1,000,000 rows.
A SELF JOIN is not a distinct type of join but rather a regular join (INNER, LEFT, etc.) where a table is joined with itself. This is useful for querying hierarchical or self-referential data.
1SELECT columns
2FROM table1 t1
3JOIN table1 t2 ON t1.column_name = t2.column_name;Notice that we use different aliases (t1 and t2) to distinguish between the two instances of the same table.
Consider an employees table with a self-referential relationship:
1| employee_id | employee_name | manager_id |
2|-------------|---------------|------------|
3| 1 | John (CEO) | NULL |
4| 2 | Lisa | 1 |
5| 3 | Bob | 1 |
6| 4 | Sarah | 2 |
7| 5 | Mike | 2 |
8| 6 | David | 3 |To find each employee with their manager's name:
1SELECT e.employee_id, e.employee_name, m.employee_name AS manager_name
2FROM employees e
3LEFT JOIN employees m ON e.manager_id = m.employee_id;Result:
1| employee_id | employee_name | manager_name |
2|-------------|---------------|--------------|
3| 1 | John (CEO) | NULL |
4| 2 | Lisa | John (CEO) |
5| 3 | Bob | John (CEO) |
6| 4 | Sarah | Lisa |
7| 5 | Mike | Lisa |
8| 6 | David | Bob |SELF JOINs can be used to find pairs of records that meet certain criteria. For instance, to find employees who work in the same department:
1SELECT e1.employee_name, e2.employee_name
2FROM employees e1
3JOIN employees e2 ON e1.department_id = e2.department_id AND e1.employee_id < e2.employee_id;The e1.employee_id < e2.employee_id condition ensures that each pair is only returned once and that an employee isn't paired with themselves.
Many real-world queries require joining more than two tables. You can chain multiple JOIN clauses to combine data from several tables.
Consider these tables:
orders: Contains order header informationorder_items: Contains individual items in each orderproducts: Contains product information1SELECT
2 o.order_id,
3 c.customer_name,
4 p.product_name,
5 oi.quantity,
6 oi.price AS unit_price,
7 (oi.quantity * oi.price) AS total_price
8FROM orders o
9JOIN customers c ON o.customer_id = c.customer_id
10JOIN order_items oi ON o.order_id = oi.order_id
11JOIN products p ON oi.product_id = p.product_id
12WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
13ORDER BY o.order_id, p.product_name;This query:
The logical order of operations in the query is:
However, the database's query optimizer determines the actual execution plan based on statistics, indexes, and other factors. Most modern database systems are sophisticated enough to determine the optimal join order regardless of how you write the query.
When working with multi-table joins:
JOINs are often combined with other SQL clauses to create sophisticated queries.
1SELECT c.customer_name, o.order_id, o.order_date, o.amount
2FROM customers c
3JOIN orders o ON c.customer_id = o.customer_id
4WHERE o.order_date >= '2023-01-01'
5 AND c.customer_name LIKE 'J%';This finds all orders from customers whose names start with 'J', placed from January 2023 onward.
1SELECT
2 c.customer_id,
3 c.customer_name,
4 COUNT(o.order_id) AS order_count,
5 SUM(o.amount) AS total_spent
6FROM customers c
7LEFT JOIN orders o ON c.customer_id = o.customer_id
8GROUP BY c.customer_id, c.customer_name
9ORDER BY total_spent DESC;This summarizes how many orders each customer has placed and how much they've spent in total.
1SELECT
2 p.category,
3 COUNT(oi.order_item_id) AS times_ordered,
4 SUM(oi.quantity) AS total_quantity
5FROM products p
6JOIN order_items oi ON p.product_id = oi.product_id
7GROUP BY p.category
8HAVING COUNT(oi.order_item_id) > 100
9ORDER BY total_quantity DESC;This finds product categories that have been ordered more than 100 times, showing the total quantity sold for each.
1SELECT c.customer_name, c.email, recent_order.order_date
2FROM customers c
3JOIN (
4 SELECT customer_id, MAX(order_date) AS order_date
5 FROM orders
6 GROUP BY customer_id
7) recent_order ON c.customer_id = recent_order.customer_id
8ORDER BY recent_order.order_date DESC;This query finds each customer's most recent order date.
When working with JOINs, be aware of these common issues and follow these best practices:
1 -- Ambiguous column reference
2 SELECT id, name FROM customers JOIN orders ON customer_id = customer_id;
3
4 -- Clear column references
5 SELECT c.id, c.name FROM customers c JOIN orders o ON c.id = o.customer_id;1 SELECT c.customer_id, c.name, o.order_id, o.order_date
2 FROM customers c
3 JOIN orders o ON c.customer_id = o.customer_id;1 -- Good: clear, descriptive aliases
2 SELECT cust.name, ord.date
3 FROM customers cust
4 JOIN orders ord ON cust.id = ord.customer_id;
5
6 -- Avoid: cryptic single-letter aliases for complex queries
7 SELECT a.x, b.y
8 FROM really_long_table_name a
9 JOIN another_long_table_name b ON a.id = b.fk;1 -- Instead of this
2 SELECT * FROM orders o
3 JOIN order_items oi ON o.order_id = oi.order_id;
4
5 -- You can use this (in databases that support it)
6 SELECT * FROM orders
7 JOIN order_items USING (order_id);Practice using different JOIN operations to create a comprehensive view of customer orders and product information.
1<!DOCTYPE html>
2<html lang="en">
3<head>
4 <meta charset="UTF-8">
5 <meta name="viewport" content="width=device-width, initial-scale=1.0">
6 <title>SQL Joins - Customer Order Dashboard</title>
7 <style>
8 body {
9 font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
10 line-height: 1.6;
11 color: #333;
12 max-width: 1200px;
13 margin: 0 auto;
14 padding: 20px;
15 background-color: #f8f9fa;
16 }
17
18 h1, h2, h3 {
19 color: #2c3e50;
20 }
21
22 h1 {
23 border-bottom: 2px solid #3498db;
24 padding-bottom: 10px;
25 margin-bottom: 30px;
26 }
27
28 .dashboard {
29 display: grid;
30 grid-template-columns: 2fr 1fr;
31 gap: 20px;
32 }
33
34 @media (max-width: 900px) {
35 .dashboard {
36 grid-template-columns: 1fr;
37 }
38 }
39
40 .card {
41 background-color: #fff;
42 border-radius: 8px;
43 box-shadow: 0 2px 10px rgba(0, 0, 0, 0.1);
44 padding: 20px;
45 margin-bottom: 20px;
46 overflow: hidden;
47 }
48
49 .card h2 {
50 margin-top: 0;
51 font-size: 1.5rem;
52 color: #3498db;
53 border-bottom: 1px solid #eee;
54 padding-bottom: 10px;
55 }
56
57 .full-width {
58 grid-column: 1 / -1;
59 }
60
61 .query-box {
62 background-color: #2c3e50;
63 color: #ecf0f1;
64 padding: 15px;
65 border-radius: 6px;
66 font-family: 'Courier New', monospace;
67 font-size: 0.9rem;
68 overflow-x: auto;
69 margin: 15px 0;
70 }
71
72 .query-box .keyword {
73 color: #3498db;
74 font-weight: bold;
75 }
76
77 .query-box .function {
78 color: #2ecc71;
79 }
80
81 .query-box .comment {
82 color: #7f8c8d;
83 font-style: italic;
84 }
85
86 .query-box .string {
87 color: #e74c3c;
88 }
89
90 .query-box .number {
91 color: #f1c40f;
92 }
93
94 .query-tabs {
95 display: flex;
96 border-bottom: 1px solid #ddd;
97 margin-bottom: 15px;
98 overflow-x: auto;
99 white-space: nowrap;
100 }
101
102 .query-tab {
103 padding: 10px 15px;
104 background: #f8f9fa;
105 border: 1px solid #ddd;
106 border-bottom: none;
107 border-radius: 6px 6px 0 0;
108 margin-right: 5px;
109 cursor: pointer;
110 font-size: 0.9rem;
111 position: relative;
112 top: 1px;
113 }
114
115 .query-tab.active {
116 background: #fff;
117 border-bottom: 1px solid #fff;
118 color: #3498db;
119 }
120
121 .table-container {
122 overflow-x: auto;
123 }
124
125 table {
126 width: 100%;
127 border-collapse: collapse;
128 margin: 15px 0;
129 font-size: 0.9rem;
130 }
131
132 th {
133 background-color: #3498db;
134 color: #fff;
135 text-align: left;
136 padding: 12px;
137 font-weight: 600;
138 }
139
140 td {
141 padding: 10px 12px;
142 border-top: 1px solid #eee;
143 }
144
145 tr:nth-child(even) {
146 background-color: #f2f2f2;
147 }
148
149 tr:hover {
150 background-color: #e9e9e9;
151 }
152
153 .null-value {
154 color: #999;
155 font-style: italic;
156 }
157
158 .hint-box {
159 background-color: #d4edda;
160 border-left: 4px solid #28a745;
161 padding: 15px;
162 margin: 20px 0;
163 }
164
165 .warning-box {
166 background-color: #fff3cd;
167 border-left: 4px solid #ffc107;
168 padding: 15px;
169 margin: 20px 0;
170 }
171
172 .schema-box {
173 background-color: #e9ecef;
174 border-radius: 6px;
175 padding: 15px;
176 margin: 15px 0;
177 }
178
179 .schema-table {
180 margin-bottom: 15px;
181 }
182
183 .schema-table h3 {
184 margin-top: 0;
185 margin-bottom: 5px;
186 font-size: 1.1rem;
187 color: #2c3e50;
188 }
189
190 .schema-field {
191 font-family: monospace;
192 padding: 2px 5px;
193 margin-right: 5px;
194 }
195
196 .primary-key {
197 font-weight: bold;
198 text-decoration: underline;
199 }
200
201 .foreign-key {
202 font-style: italic;
203 color: #3498db;
204 }
205
206 .query-results {
207 margin-top: 20px;
208 }
209
210 .btn {
211 background-color: #3498db;
212 color: white;
213 border: none;
214 padding: 10px 15px;
215 border-radius: 4px;
216 cursor: pointer;
217 font-size: 0.9rem;
218 transition: background-color 0.2s;
219 }
220
221 .btn:hover {
222 background-color: #2980b9;
223 }
224
225 .btn:focus {
226 outline: none;
227 }
228 </style>
229</head>
230<body>
231 <h1>SQL Joins - Customer Order Dashboard</h1>
232
233 <div class="card full-width">
234 <h2>Database Schema</h2>
235 <div class="schema-box">
236 <div class="schema-table">
237 <h3>customers</h3>
238 <div>
239 <span class="schema-field primary-key">customer_id</span>
240 <span class="schema-field">first_name</span>
241 <span class="schema-field">last_name</span>
242 <span class="schema-field">email</span>
243 <span class="schema-field">phone</span>
244 <span class="schema-field">city</span>
245 <span class="schema-field">country</span>
246 </div>
247 </div>
248 <div class="schema-table">
249 <h3>orders</h3>
250 <div>
251 <span class="schema-field primary-key">order_id</span>
252 <span class="schema-field foreign-key">customer_id</span>
253 <span class="schema-field">order_date</span>
254 <span class="schema-field">status</span>
255 <span class="schema-field">total_amount</span>
256 </div>
257 </div>
258 <div class="schema-table">
259 <h3>order_items</h3>
260 <div>
261 <span class="schema-field primary-key">item_id</span>
262 <span class="schema-field foreign-key">order_id</span>
263 <span class="schema-field foreign-key">product_id</span>
264 <span class="schema-field">quantity</span>
265 <span class="schema-field">unit_price</span>
266 </div>
267 </div>
268 <div class="schema-table">
269 <h3>products</h3>
270 <div>
271 <span class="schema-field primary-key">product_id</span>
272 <span class="schema-field">product_name</span>
273 <span class="schema-field">category</span>
274 <span class="schema-field">description</span>
275 <span class="schema-field">price</span>
276 <span class="schema-field foreign-key">supplier_id</span>
277 </div>
278 </div>
279 <div class="schema-table">
280 <h3>suppliers</h3>
281 <div>
282 <span class="schema-field primary-key">supplier_id</span>
283 <span class="schema-field">supplier_name</span>
284 <span class="schema-field">contact_name</span>
285 <span class="schema-field">email</span>
286 <span class="schema-field">phone</span>
287 </div>
288 </div>
289 </div>
290 </div>
291
292 <div class="dashboard">
293 <div class="card">
294 <h2>JOIN Explorer</h2>
295 <div class="query-tabs">
296 <div class="query-tab active" data-query="inner">INNER JOIN</div>
297 <div class="query-tab" data-query="left">LEFT JOIN</div>
298 <div class="query-tab" data-query="right">RIGHT JOIN</div>
299 <div class="query-tab" data-query="full">FULL OUTER JOIN</div>
300 <div class="query-tab" data-query="multi">Multi-Table JOIN</div>
301 </div>
302
303 <div class="query-content" id="inner-join-content">
304 <p>The INNER JOIN returns only the rows with matching values in both tables:</p>
305 <div class="query-box" id="inner-join-query">
306 <span class="keyword">SELECT</span> o.order_id, c.customer_id, c.first_name, c.last_name, o.order_date, o.total_amount<br>
307 <span class="keyword">FROM</span> orders o<br>
308 <span class="keyword">INNER JOIN</span> customers c <span class="keyword">ON</span> o.customer_id = c.customer_id<br>
309 <span class="keyword">ORDER BY</span> o.order_date <span class="keyword">DESC</span>
310 <span class="keyword">LIMIT</span> 10;
311 </div>
312 <div class="query-results">
313 <h3>Results</h3>
314 <div class="table-container" id="inner-join-results">
315 <table>
316 <thead>
317 <tr>
318 <th>order_id</th>
319 <th>customer_id</th>
320 <th>first_name</th>
321 <th>last_name</th>
322 <th>order_date</th>
323 <th>total_amount</th>
324 </tr>
325 </thead>
326 <tbody>
327 <!-- Results will be inserted here by JavaScript -->
328 </tbody>
329 </table>
330 </div>
331 </div>
332 <div class="hint-box">
333 <p><strong>Key Point:</strong> INNER JOIN only returns orders that have a matching customer. If an order has a customer_id that doesn't exist in the customers table, it won't appear in the results.</p>
334 </div>
335 </div>
336
337 <div class="query-content" id="left-join-content" style="display:none;">
338 <p>The LEFT JOIN returns all rows from the left table (customers) and matching rows from the right table (orders):</p>
339 <div class="query-box" id="left-join-query">
340 <span class="keyword">SELECT</span> c.customer_id, c.first_name, c.last_name, o.order_id, o.order_date, o.total_amount<br>
341 <span class="keyword">FROM</span> customers c<br>
342 <span class="keyword">LEFT JOIN</span> orders o <span class="keyword">ON</span> c.customer_id = o.customer_id<br>
343 <span class="keyword">ORDER BY</span> c.last_name, c.first_name, o.order_date <span class="keyword">DESC</span>
344 <span class="keyword">LIMIT</span> 10;
345 </div>
346 <div class="query-results">
347 <h3>Results</h3>
348 <div class="table-container" id="left-join-results">
349 <!-- Results will be inserted here by JavaScript -->
350 </div>
351 </div>
352 <div class="hint-box">
353 <p><strong>Key Point:</strong> LEFT JOIN returns all customers, even those who have never placed an order. For customers without orders, the order columns will show NULL values.</p>
354 </div>
355 <div class="query-box">
356 <span class="comment">-- Find customers who have never placed an order:</span><br>
357 <span class="keyword">SELECT</span> c.customer_id, c.first_name, c.last_name, c.email<br>
358 <span class="keyword">FROM</span> customers c<br>
359 <span class="keyword">LEFT JOIN</span> orders o <span class="keyword">ON</span> c.customer_id = o.customer_id<br>
360 <span class="keyword">WHERE</span> o.order_id <span class="keyword">IS NULL</span>;
361 </div>
362 </div>
363
364 <div class="query-content" id="right-join-content" style="display:none;">
365 <p>The RIGHT JOIN returns all rows from the right table (orders) and matching rows from the left table (customers):</p>
366 <div class="query-box" id="right-join-query">
367 <span class="keyword">SELECT</span> o.order_id, o.order_date, o.customer_id <span class="keyword">AS</span> order_customer_id,
368 c.customer_id, c.first_name, c.last_name<br>
369 <span class="keyword">FROM</span> customers c<br>
370 <span class="keyword">RIGHT JOIN</span> orders o <span class="keyword">ON</span> c.customer_id = o.customer_id<br>
371 <span class="keyword">ORDER BY</span> o.order_date <span class="keyword">DESC</span>
372 <span class="keyword">LIMIT</span> 10;
373 </div>
374 <div class="query-results">
375 <h3>Results</h3>
376 <div class="table-container" id="right-join-results">
377 <!-- Results will be inserted here by JavaScript -->
378 </div>
379 </div>
380 <div class="hint-box">
381 <p><strong>Key Point:</strong> RIGHT JOIN returns all orders, even those with a customer_id that doesn't exist in the customers table. For orders with no matching customer, the customer columns will show NULL values.</p>
382 </div>
383 <div class="query-box">
384 <span class="comment">-- Find orders with no matching customer (possible data integrity issue):</span><br>
385 <span class="keyword">SELECT</span> o.order_id, o.order_date, o.customer_id<br>
386 <span class="keyword">FROM</span> customers c<br>
387 <span class="keyword">RIGHT JOIN</span> orders o <span class="keyword">ON</span> c.customer_id = o.customer_id<br>
388 <span class="keyword">WHERE</span> c.customer_id <span class="keyword">IS NULL</span>;
389 </div>
390 </div>
391
392 <div class="query-content" id="full-join-content" style="display:none;">
393 <p>The FULL OUTER JOIN returns all rows from both tables, matching them where possible:</p>
394 <div class="query-box" id="full-join-query">
395 <span class="keyword">SELECT</span> c.customer_id, c.first_name, c.last_name,
396 o.order_id, o.order_date, o.customer_id <span class="keyword">AS</span> order_customer_id<br>
397 <span class="keyword">FROM</span> customers c<br>
398 <span class="keyword">FULL OUTER JOIN</span> orders o <span class="keyword">ON</span> c.customer_id = o.customer_id<br>
399 <span class="keyword">ORDER BY</span> c.customer_id, o.order_id
400 <span class="keyword">LIMIT</span> 10;
401 </div>
402 <div class="query-results">
403 <h3>Results</h3>
404 <div class="table-container" id="full-join-results">
405 <!-- Results will be inserted here by JavaScript -->
406 </div>
407 </div>
408 <div class="hint-box">
409 <p><strong>Key Point:</strong> FULL OUTER JOIN returns all rows from both tables. If there's no match, NULL values are returned for the columns of the table without a match.</p>
410 </div>
411 <div class="warning-box">
412 <p><strong>Note:</strong> Not all database systems support FULL OUTER JOIN. In MySQL, you can simulate it with a combination of LEFT JOIN, UNION, and RIGHT JOIN.</p>
413 </div>
414 </div>
415
416 <div class="query-content" id="multi-join-content" style="display:none;">
417 <p>Multi-table joins combine data from several tables:</p>
418 <div class="query-box" id="multi-join-query">
419 <span class="keyword">SELECT</span> o.order_id, c.first_name, c.last_name, p.product_name, oi.quantity,<br>
420 oi.unit_price, (oi.quantity * oi.unit_price) <span class="keyword">AS</span> item_total,<br>
421 s.supplier_name<br>
422 <span class="keyword">FROM</span> orders o<br>
423 <span class="keyword">JOIN</span> customers c <span class="keyword">ON</span> o.customer_id = c.customer_id<br>
424 <span class="keyword">JOIN</span> order_items oi <span class="keyword">ON</span> o.order_id = oi.order_id<br>
425 <span class="keyword">JOIN</span> products p <span class="keyword">ON</span> oi.product_id = p.product_id<br>
426 <span class="keyword">JOIN</span> suppliers s <span class="keyword">ON</span> p.supplier_id = s.supplier_id<br>
427 <span class="keyword">ORDER BY</span> o.order_id, p.product_name
428 <span class="keyword">LIMIT</span> 10;
429 </div>
430 <div class="query-results">
431 <h3>Results</h3>
432 <div class="table-container" id="multi-join-results">
433 <!-- Results will be inserted here by JavaScript -->
434 </div>
435 </div>
436 <div class="hint-box">
437 <p><strong>Key Point:</strong> This query joins five tables to create a comprehensive view of order details, including customer, product, and supplier information.</p>
438 </div>
439 </div>
440 </div>
441
442 <div>
443 <div class="card">
444 <h2>Customer Summary</h2>
445 <div class="query-box">
446 <span class="keyword">SELECT</span> c.customer_id, c.first_name, c.last_name,<br>
447 <span class="function">COUNT</span>(o.order_id) <span class="keyword">AS</span> total_orders,<br>
448 <span class="function">SUM</span>(o.total_amount) <span class="keyword">AS</span> total_spent<br>
449 <span class="keyword">FROM</span> customers c<br>
450 <span class="keyword">LEFT JOIN</span> orders o <span class="keyword">ON</span> c.customer_id = o.customer_id<br>
451 <span class="keyword">GROUP BY</span> c.customer_id, c.first_name, c.last_name<br>
452 <span class="keyword">ORDER BY</span> total_spent <span class="keyword">DESC NULLS LAST</span>
453 <span class="keyword">LIMIT</span> 5;
454 </div>
455 <div class="query-results">
456 <h3>Top Customers</h3>
457 <div class="table-container" id="customer-summary-results">
458 <!-- Results will be inserted here by JavaScript -->
459 </div>
460 </div>
461 </div>
462
463 <div class="card">
464 <h2>Product Analysis</h2>
465 <div class="query-box">
466 <span class="keyword">SELECT</span> p.category, <br>
467 <span class="function">COUNT</span>(<span class="keyword">DISTINCT</span> p.product_id) <span class="keyword">AS</span> product_count,<br>
468 <span class="function">SUM</span>(oi.quantity) <span class="keyword">AS</span> units_sold,<br>
469 <span class="function">SUM</span>(oi.quantity * oi.unit_price) <span class="keyword">AS</span> total_sales<br>
470 <span class="keyword">FROM</span> products p<br>
471 <span class="keyword">LEFT JOIN</span> order_items oi <span class="keyword">ON</span> p.product_id = oi.product_id<br>
472 <span class="keyword">GROUP BY</span> p.category<br>
473 <span class="keyword">ORDER BY</span> total_sales <span class="keyword">DESC NULLS LAST</span>;
474 </div>
475 <div class="query-results">
476 <h3>Sales by Category</h3>
477 <div class="table-container" id="product-analysis-results">
478 <!-- Results will be inserted here by JavaScript -->
479 </div>
480 </div>
481 </div>
482 </div>
483 </div>
484
485 <div class="card full-width">
486 <h2>Practice Challenge</h2>
487 <p>Try writing a query that answers this business question:</p>
488 <blockquote>
489 <p>"For each supplier, show the total number of products they supply and the total value of orders placed for those products."</p>
490 </blockquote>
491
492 <div class="hint-box">
493 <p><strong>Hint:</strong> You'll need to JOIN the suppliers, products, and order_items tables, then use GROUP BY with aggregate functions.</p>
494 </div>
495
496 <div class="query-box">
497 <span class="comment">-- Write your solution here:</span><br>
498 <span class="keyword">SELECT</span> s.supplier_id, s.supplier_name,<br>
499 <span class="function">COUNT</span>(<span class="keyword">DISTINCT</span> p.product_id) <span class="keyword">AS</span> product_count,<br>
500 <span class="function">SUM</span>(oi.quantity * oi.unit_price) <span class="keyword">AS</span> total_order_value<br>
501 <span class="keyword">FROM</span> suppliers s<br>
502 <span class="keyword">JOIN</span> products p <span class="keyword">ON</span> s.supplier_id = p.supplier_id<br>
503 <span class="keyword">LEFT JOIN</span> order_items oi <span class="keyword">ON</span> p.product_id = oi.product_id<br>
504 <span class="keyword">GROUP BY</span> s.supplier_id, s.supplier_name<br>
505 <span class="keyword">ORDER BY</span> total_order_value <span class="keyword">DESC NULLS LAST</span>;
506 </div>
507 </div>
508
509 <script>
510 // Sample data for our simulation
511 const customers = [
512 { customer_id: 1, first_name: "Emma", last_name: "Johnson", email: "[email protected]", phone: "555-123-4567", city: "New York", country: "USA" },
513 { customer_id: 2, first_name: "Liam", last_name: "Smith", email: "[email protected]", phone: "555-234-5678", city: "London", country: "UK" },
514 { customer_id: 3, first_name: "Olivia", last_name: "Brown", email: "[email protected]", phone: "555-345-6789", city: "Sydney", country: "Australia" },
515 { customer_id: 4, first_name: "Noah", last_name: "Garcia", email: "[email protected]", phone: "555-456-7890", city: "Toronto", country: "Canada" },
516 { customer_id: 5, first_name: "Sophia", last_name: "Miller", email: "[email protected]", phone: "555-567-8901", city: "Paris", country: "France" },
517 { customer_id: 6, first_name: "William", last_name: "Davis", email: "[email protected]", phone: "555-678-9012", city: "Berlin", country: "Germany" },
518 { customer_id: 7, first_name: "Ava", last_name: "Wilson", email: "[email protected]", phone: "555-789-0123", city: "Tokyo", country: "Japan" }
519 ];
520
521 const orders = [
522 { order_id: 1001, customer_id: 1, order_date: "2023-05-10", status: "Delivered", total_amount: 125.50 },
523 { order_id: 1002, customer_id: 3, order_date: "2023-05-15", status: "Delivered", total_amount: 89.99 },
524 { order_id: 1003, customer_id: 2, order_date: "2023-05-20", status: "Shipped", total_amount: 212.75 },
525 { order_id: 1004, customer_id: 1, order_date: "2023-06-01", status: "Delivered", total_amount: 56.25 },
526 { order_id: 1005, customer_id: 5, order_date: "2023-06-05", status: "Processing", total_amount: 175.00 },
527 { order_id: 1006, customer_id: 4, order_date: "2023-06-10", status: "Shipped", total_amount: 149.99 },
528 { order_id: 1007, customer_id: 9, order_date: "2023-06-15", status: "Delivered", total_amount: 88.50 },
529 { order_id: 1008, customer_id: 3, order_date: "2023-06-20", status: "Processing", total_amount: 325.75 },
530 { order_id: 1009, customer_id: 5, order_date: "2023-07-01", status: "Delivered", total_amount: 62.30 }
531 ];
532
533 const products = [
534 { product_id: 101, product_name: "Laptop Pro", category: "Electronics", description: "High-performance laptop for professionals", price: 1299.99, supplier_id: 201 },
535 { product_id: 102, product_name: "Smartphone X", category: "Electronics", description: "Latest smartphone with advanced features", price: 899.99, supplier_id: 201 },
536 { product_id: 103, product_name: "Coffee Maker", category: "Home & Kitchen", description: "Premium coffee maker with timer", price: 89.99, supplier_id: 202 },
537 { product_id: 104, product_name: "Running Shoes", category: "Sports", description: "Lightweight running shoes for athletes", price: 129.99, supplier_id: 203 },
538 { product_id: 105, product_name: "Wireless Headphones", category: "Electronics", description: "Noise-canceling wireless headphones", price: 199.99, supplier_id: 201 },
539 { product_id: 106, product_name: "Blender", category: "Home & Kitchen", description: "High-speed blender for smoothies and more", price: 79.99, supplier_id: 202 },
540 { product_id: 107, product_name: "Yoga Mat", category: "Sports", description: "Non-slip yoga mat for all levels", price: 45.99, supplier_id: 203 },
541 { product_id: 108, product_name: "Smart Watch", category: "Electronics", description: "Fitness tracker and smartwatch", price: 249.99, supplier_id: 204 },
542 { product_id: 109, product_name: "Water Bottle", category: "Sports", description: "Insulated water bottle for sports", price: 24.99, supplier_id: 203 }
543 ];
544
545 const order_items = [
546 { item_id: 2001, order_id: 1001, product_id: 101, quantity: 1, unit_price: 1199.99 },
547 { item_id: 2002, order_id: 1001, product_id: 105, quantity: 1, unit_price: 179.99 },
548 { item_id: 2003, order_id: 1002, product_id: 103, quantity: 1, unit_price: 89.99 },
549 { item_id: 2004, order_id: 1003, product_id: 102, quantity: 1, unit_price: 849.99 },
550 { item_id: 2005, order_id: 1003, product_id: 105, quantity: 1, unit_price: 179.99 },
551 { item_id: 2006, order_id: 1004, product_id: 107, quantity: 1, unit_price: 39.99 },
552 { item_id: 2007, order_id: 1004, product_id: 109, quantity: 1, unit_price: 24.99 },
553 { item_id: 2008, order_id: 1005, product_id: 108, quantity: 1, unit_price: 229.99 },
554 { item_id: 2009, order_id: 1006, product_id: 104, quantity: 1, unit_price: 119.99 },
555 { item_id: 2010, order_id: 1006, product_id: 107, quantity: 1, unit_price: 39.99 },
556 { item_id: 2011, order_id: 1007, product_id: 106, quantity: 1, unit_price: 69.99 },
557 { item_id: 2012, order_id: 1007, product_id: 103, quantity: 1, unit_price: 89.99 },
558 { item_id: 2013, order_id: 1008, product_id: 101, quantity: 1, unit_price: 1199.99 },
559 { item_id: 2014, order_id: 1008, product_id: 105, quantity: 1, unit_price: 179.99 },
560 { item_id: 2015, order_id: 1009, product_id: 109, quantity: 2, unit_price: 22.99 },
561 { item_id: 2016, order_id: 1009, product_id: 107, quantity: 1, unit_price: 39.99 }
562 ];
563
564 const suppliers = [
565 { supplier_id: 201, supplier_name: "Tech Innovations", contact_name: "James Wilson", email: "[email protected]", phone: "555-111-2222" },
566 { supplier_id: 202, supplier_name: "Home Essentials", contact_name: "Sarah Davis", email: "[email protected]", phone: "555-222-3333" },
567 { supplier_id: 203, supplier_name: "Sports World", contact_name: "Michael Brown", email: "[email protected]", phone: "555-333-4444" },
568 { supplier_id: 204, supplier_name: "Smart Devices", contact_name: "Lisa Johnson", email: "[email protected]", phone: "555-444-5555" },
569 { supplier_id: 205, supplier_name: "Global Gadgets", contact_name: "Robert Smith", email: "[email protected]", phone: "555-555-6666" }
570 ];
571
572 // Helper function for SQL-like INNER JOIN
573 function innerJoin(left, right, leftKey, rightKey) {
574 const result = [];
575 left.forEach(leftRow => {
576 right.forEach(rightRow => {
577 if (leftRow[leftKey] === rightRow[rightKey]) {
578 result.push({ ...leftRow, ...rightRow });
579 }
580 });
581 });
582 return result;
583 }
584
585 // Helper function for SQL-like LEFT JOIN
586 function leftJoin(left, right, leftKey, rightKey) {
587 const result = [];
588 left.forEach(leftRow => {
589 let found = false;
590 right.forEach(rightRow => {
591 if (leftRow[leftKey] === rightRow[rightKey]) {
592 found = true;
593 result.push({ ...leftRow, ...rightRow });
594 }
595 });
596 if (!found) {
597 // Add right table columns with NULL values
598 const rightNull = {};
599 const sampleRightRow = right[0] || {};
600 Object.keys(sampleRightRow).forEach(key => {
601 rightNull[key] = null;
602 });
603 result.push({ ...leftRow, ...rightNull });
604 }
605 });
606 return result;
607 }
608
609 // Helper function for SQL-like RIGHT JOIN
610 function rightJoin(left, right, leftKey, rightKey) {
611 return leftJoin(right, left, rightKey, leftKey);
612 }
613
614 // Helper function for SQL-like FULL OUTER JOIN
615 function fullOuterJoin(left, right, leftKey, rightKey) {
616 // Start with left join
617 const leftJoinResult = leftJoin(left, right, leftKey, rightKey);
618
619 // Find right rows with no match in left
620 const rightOnlyRows = [];
621 right.forEach(rightRow => {
622 let found = false;
623 left.forEach(leftRow => {
624 if (leftRow[leftKey] === rightRow[rightKey]) {
625 found = true;
626 }
627 });
628 if (!found) {
629 const leftNull = {};
630 const sampleLeftRow = left[0] || {};
631 Object.keys(sampleLeftRow).forEach(key => {
632 leftNull[key] = null;
633 });
634 rightOnlyRows.push({ ...leftNull, ...rightRow });
635 }
636 });
637
638 // Combine results
639 return [...leftJoinResult, ...rightOnlyRows];
640 }
641
642 // Helper function to display results in a table
643 function displayResults(elementId, data, columns) {
644 const container = document.getElementById(elementId);
645
646 if (!data || data.length === 0) {
647 container.innerHTML = '<p>No results found.</p>';
648 return;
649 }
650
651 // Create table if columns specified
652 if (columns) {
653 let tableHTML = '<table><thead><tr>';
654 columns.forEach(col => {
655 tableHTML += `<th>$Your progress is saved in this browser, on this device only.