blob: 5b3cf9621f33d29b23daeacadb1139da4bfd6113 (
plain)
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
|
# Schema
| BUSINESSES | | COMPUTERS | | EVENTS | |
| ------------------------ | ------------ | ----------- | ------- | ----------- | ------------ |
| id | int | id | int | id | int |
| name | varchar(255) | active | tinyint | computer_id | int |
| business_relationship_id | int | business_id | int | occurred_at | datetime |
| | | | | type | varchar(255) |
| | | | | data | text |
# Lessons
Links:
* https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
`WHERE` is applied before `GROUP BY`, `HAVING` is applied after and can filter on aggregates.
## Inner join
Produces a set of records that match in both Table A and Table B.
```mysql
SELECT * FROM table_a
INNER JOIN table_b
ON table_a.name = table_b.name;
```
```text
A | | B
===========
( | X | )
===========
```
## Full Outer Join
Produces the set of results from both table A and table B, with matching records from both sides where available.
If there is no match the missing side will contain null.
```mysql
SELECT * FROM table_a
FULL OUTER JOIN table_b
ON table_a.name = table_b.name;
```
```text
A | | B
===========
( X | X | X )
===========
```
## LEFT OUTER JOIN
Produces a complete set of records from table A, with the matching records (where available) in table B.
If there is no match, the right side will contain a null.
To produce a set of results found in table_a but not table_b.
```mysql
SELECT *
FROM table_a
LEFT OUTER JOIN table_b
ON table_a.name table_b.name
where table_b.id IS NULL;
```
```text
A | | B
===========
( X | | )
===========
```
To produce a set of results unique to table_a and table_b.
```mysql
SELECT *
FROM table_a
FULL OUTER JOIN table_b
ON table_a.name = table_b.name
WHERE table_a.id IS NULL
OR table_b.id IS NULL;
```
```text
A | | B
===========
( X | | X )
===========
```
# Questions
1. Which businesses have more than N computers?
2. Which computer had the most events in a single day?
3. What are the top 10 most active computers (day, week, month)? .i.e emits the most events.
4. Which businesses have at least one computer?
5. Which businesses have zero computers?
6. Do we have any computers that belong to a business that doesn't exist?
|
