Joining Data In SQL takeaways

I just finished Joining Data In SQL on DataCamp.

Here are my takeaways.

JOINS (Combines columns)

1. JOIN

• INNER JOIN - joins matching records where key fields is in both tables
• SELF JOIN - join table with itself

2. OUTER JOIN

• LEFT JOIN - keeps all records on left, matching records on the right
• RIGHT JOIN - keeps all records on right, matching records on the left
• FULL JOIN - combines left and right join result (keeps all records from both tables)
• CROSS JOIN - all possible combinations (no ON or USING clause)

3. FILTER JOIN

• SEMI JOIN - filter (with WHERE clause) first table for observations that match second
• ANTI JOIN - opposite of semi join (diagnosing problems of joins with fewer results that expected)

Self join, semi join, and anti don't have built-in SQL syntax.

SET THEORY (combines rows)

• UNION - stacks records and removes duplicates
• UNION ALL - stacks everything
• INTERSECT - return only records appearing on both tables
• EXCEPT - records in one that aren't in another

Types of Subqueries

• inside WHERE (most frequent)
• inside SELECT (must have alias for temporary table)
• inside FROM (must have alias for temporary table)
• inside ON (rare)

Example of subquery from the class

• In this subquery, we're looking at the maximum inflation rate for each continent in the year 2015.
• the inner subquery in the FROM clause runs first, then the one in the IN clause, then the main query.

Next course: Intermediate SQL

Resources

• Here is the code from the course
• Here's a nifty cheatsheet for SQL joins

Here's an awesome visual explanation on SQL joins

View my certificate

Thank you!