[GA] Google Analytics Certificate Program (GA Day) Course 5 Data Aggregation- SQL JOIN 사용

1. Join 

 

SELECT

employees.name AS employee_name,

employees.role AS employee_role,

departments.name AS department_name

FROM

onyx-smoke-380601.employee_data.employees

INNER JOIN

-- in the other table we are using

employee_data.departments ON

employees.department_id=departments.department_id

SELECT

employees.name AS employee_name,

employees.role AS employee_role,

departments.name AS department_name

FROM

onyx-smoke-380601.employee_data.employees

RIGHT JOIN

-- in the other table we are using

employee_data.departments ON

employees.department_id=departments.department_id

 

SELECT

employees.name AS employee_name,

employees.role AS employee_role,

departments.name AS department_name

FROM

onyx-smoke-380601.employee_data.employees

FuLL OUTER JOIN

-- in the other table we are using

employee_data.departments ON

employees.department_id=departments.department_id

 

-- Let's say table_1 has 100 rows and table_2 has 10 rows.

-- They share 10 keys in common.

-- Using INNER JOIN --> We get 10 rows in our results.

SELECT

COUNT(*)

FROM

table_1

INNER JOIN

table_2

ON table_1.key = table_2.key;

-- Using LEFT JOIN --> We get 100 rows in our results.

SELECT

COUNT(*)

FROM

table_1

LEFT JOIN

table_2

ON table_1.key = table_2.key;

2. "how many?"

- COUNT DISTINCT - only returns the distinct value / no repeating values 

SELECT

orders.*,

warehouse.warehouse_alias,

warehouse.state

 

FROM `onyx-smoke-380601.warehouse_orders.orders` as orders

JOIN

`onyx-smoke-380601.warehouse_orders.warehouse` as warehouse

ON orders.warehouse_id= warehouse.warehouse_id