Repository navigation
Expand file tree
/
Copy path05_JOINs.sql
More file actions
182 lines (162 loc) · 4.72 KB
/
Copy path05_JOINs.sql
File metadata and controls
182 lines (162 loc) · 4.72 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
/* ==============================================================================
SQL Joins
---------------------------------------------------------------------------------
This document provides an overview of SQL joins, which allow combining data
from multiple tables to retrieve meaningful insights.
Table of Contents:
1. BASIC JOINS
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
2. ADVANCED JOINS
- LEFT ANTI JOIN
- RIGHT ANTI JOIN
- ALTERNATIVE INNER JOIN
- FULL ANTI JOIN
- CROSS JOIN
3. MULTIPLE TABLE JOINS (4 TABLES)
=================================================================================
*/
-- Run this line first: the queries in this script use MyDatabase (section 3 uses SalesDB)
USE MyDatabase;
/* ==============================================================================
1. BASIC JOINS
============================================================================== */
-- No Join
/* Retrieve all data from customers and orders as separate results */
SELECT * FROM customers;
SELECT * FROM orders;
-- INNER JOIN
/* Get all customers along with their orders,
but only for customers who have placed an order */
SELECT
c.id,
c.first_name,
o.order_id,
o.sales
FROM customers AS c
INNER JOIN orders AS o
ON c.id = o.customer_id
-- LEFT JOIN
/* Get all customers along with their orders,
including those without orders */
SELECT
c.id,
c.first_name,
o.order_id,
o.sales
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
-- RIGHT JOIN
/* Get all customers along with their orders,
including orders without matching customers */
SELECT
c.id,
c.first_name,
o.order_id,
o.customer_id,
o.sales
FROM customers AS c
RIGHT JOIN orders AS o
ON c.id = o.customer_id
-- Alternative to RIGHT JOIN using LEFT JOIN
/* Get all customers along with their orders,
including orders without matching customers */
SELECT
c.id,
c.first_name,
o.order_id,
o.sales
FROM orders AS o
LEFT JOIN customers AS c
ON c.id = o.customer_id
-- FULL JOIN
/* Get all customers and all orders, even if there’s no match */
SELECT
c.id,
c.first_name,
o.order_id,
o.customer_id,
o.sales
FROM customers AS c
FULL JOIN orders AS o
ON c.id = o.customer_id
/* ==============================================================================
2. ADVANCED JOINS
============================================================================== */
-- LEFT ANTI JOIN
/* Get all customers who haven't placed any order */
SELECT *
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
WHERE o.customer_id IS NULL
-- RIGHT ANTI JOIN
/* Get all orders without matching customers */
SELECT *
FROM customers AS c
RIGHT JOIN orders AS o
ON c.id = o.customer_id
WHERE c.id IS NULL
-- Alternative to RIGHT ANTI JOIN using LEFT JOIN
/* Get all orders without matching customers */
SELECT *
FROM orders AS o
LEFT JOIN customers AS c
ON c.id = o.customer_id
WHERE c.id IS NULL
-- Alternative to INNER JOIN using LEFT JOIN
/* Get all customers along with their orders,
but only for customers who have placed an order */
SELECT *
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
WHERE o.customer_id IS NOT NULL
-- FULL ANTI JOIN
/* Find customers without orders and orders without customers */
SELECT
c.id,
c.first_name,
o.order_id,
o.customer_id,
o.sales
FROM customers AS c
FULL JOIN orders AS o
ON c.id = o.customer_id
WHERE o.customer_id IS NULL OR c.id IS NULL
-- CROSS JOIN
/* Generate all possible combinations of customers and orders */
SELECT *
FROM customers
CROSS JOIN orders
/* ==============================================================================
3. MULTIPLE TABLE JOINS (4 TABLES)
============================================================================== */
/* TASK: Using SalesDB, Retrieve a list of all orders, along with the related customer, product,
and employee details. For each order, display:
- Order ID
- Customer's name
- Product name
- Sales amount
- Product price
- Salesperson's name */
USE SalesDB
SELECT
o.OrderID,
o.Sales,
c.FirstName AS CustomerFirstName,
c.LastName AS CustomerLastName,
p.Product AS ProductName,
p.Price,
e.FirstName AS EmployeeFirstName,
e.LastName AS EmployeeLastName
FROM Sales.Orders AS o
LEFT JOIN Sales.Customers AS c
ON o.CustomerID = c.CustomerID
LEFT JOIN Sales.Products AS p
ON o.ProductID = p.ProductID
LEFT JOIN Sales.Employees AS e
ON o.SalesPersonID = e.EmployeeID