SQL Advanced Joins

导读：

我们用FULL OUTER JOIN, LEFT JOIN, RIGHT JOIN
可以看到每一个account有个sales rep 和每个sales rep有一个account
我们也可以用他们看到每一个account没有一个sales_rep和每一个sales rep没有一个account
ENGLISH:
each account who has a sales rep and each sales rep that has an account (all of the columns in these returned rows will be full)
but also each account that does not have a sales rep and each sales rep that does not have an account (some of the columns in these returned rows will be empty)

Full Outer Join

Definition

returns all records when there is a match in left (table1) or right (table2) table records.

Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

Matched Rows

SELECT *
FROM accounts
FULL JOIN sales_reps ON accounts.sales_rep_id = sales_reps.id

Unmatched Rows

JOINs with Comparison Operators

Inequality Joins

1. write a query that left joins the accounts table and the sales_reps tables on each sale rep’s ID number and joins it using the < comparison operator on accounts.primary_poc and sales_reps.name

SELECT accounts.name as account_name,
       accounts.primary_poc as poc_name,
       sales_reps.name as sales_rep_name
FROM accounts
LEFT JOIN sales_reps
ON accounts.sales_rep_id = sales_reps.id
AND accounts.primary_poc < sales_reps.name

Self Joins

Example:

SELECT o1.id AS o1_id,
       o1.account_id AS o1_account_id,
       o1.occurred_at AS o1_occurred_at,
       o2.id AS o2_id,
       o2.account_id AS o2_account_id,
       o2.occurred_at AS o2_occurred_at
FROM orders o1
LEFT JOIN orders o2
ON o1.account_id = o2.account_id
AND o2.occurred_at > o1.occurred_at
AND o2.occurred_at <= o1.occurred_at + INTERVAL '28 days'
ORDER BY o1.account_id, o1.occurred_at

SELECT we1.id AS we_id,
       we1.account_id AS we1_account_id,
       we1.occurred_at AS we1_occurred_at,
       we1.channel AS we1_channel,
       we2.id AS we2_id,
       we2.account_id AS we2_account_id,
       we2.occurred_at AS we2_occurred_at,
       we2.channel AS we2_channel
FROM web_events we1 
LEFT JOIN web_events we2
ON we1.account_id = we2.account_id
AND we1.occurred_at > we2.occurred_at
AND we1.occurred_at <= we2.occurred_at + INTERVAL '1 day'
ORDER BY we1.account_id, we2.occurred_at

UNION

UNION removes duplicate rows.
UNION ALL does not remove duplicate rows.

1. Without rewriting and running the query, how many results would be returned if you used UNION instead of UNION ALL in the above query

SELECT *
    FROM accounts
UNION ALL
SELECT *
  FROM accounts

SELECT *
    FROM accounts
UNION
SELECT *
  FROM accounts;

2. Add a WHERE clause to each of the tables that you unioned in the query above, filtering the first table where name equals Walmart and filtering the second table where name equals Disney. Inspect the results then answer the subsequent quiz.

SELECT *
    FROM accounts
    WHERE name = 'Walmart'
UNION ALL
SELECT *
  FROM accounts
  WHERE name = 'Disney';

SELECT *
    FROM accounts
    WHERE name = 'Walmart' OR name = 'Disney';

3. Perform the union in your first query (under the Appending Data via UNION header) in a common table expression and name it double_accounts. Then do a COUNT the number of times a name appears in the double_accounts table. If you do this correctly, your query results should have a count of 2 for each name.

WITH double_accounts AS (SELECT * FROM accounts
    					 UNION ALL
    					 SELECT * FROM accounts)
SELECT name,
       COUNT(*) AS name_count
FROM double_accounts 
GROUP BY 1
ORDER BY 2 DESC;