Intro of SQL | #2 JOIN

1. JOIN Keyword

KEYWORD - JOIN	DESCRIPTION
INNER JOIN	Using only matched key field (duplicated column between X & Y) FROM X INNER JOIN Y
SELF JOIN	Joining same table If want to make no duplicates b/w same key field, use ON a.x = b.x AND a.x <> b.x
LEFT OUTER JOIN	Returns all records from the left table, and the matching records from the right table Null is listed in the column of the right table that does not overlap with the left table FROM standard_table LEFT JOIN sub_table
RIGHT OUTER JOIN	Can always write a right join as a left join
FULL OUTER JOIN	Returns all records
CROSS JOIN	Make all combination of tables Join all rows in one table and all rows in the other table.
ON =
USING ()	When the key field want to join on is the same name in both tables, can use a USING clause instead of the ON

-- Using ON
SELECT c.code, c.name, c.region,
        p.year, p.fertility_rate
FROM countries AS c
INNER JOIN populations AS p
ON c.code = p.country_code
-- Last code works same as USING (code)


-- Multiple Inner Join
SELECT *
FROM left_table
  INNER JOIN right_table
    ON left_table.id = right_table.id
  INNER JOIN another_table
    ON left_table.id = another_table.id;

-- CASE WHEN THEN ELSE END
SELECT country_code, size, # SHOULD PUT INTO ,
CASE WHEN size > 50000000 THEN 'large'
    WHEN size > 1000000 THEN 'medium'
    ELSE 'small' END
    AS popsize_group
FROM populations
WHERE year = 2015;

-- LEFT JOIN vs RIGHT JOIN
-- 1) LEFT JOIN
SELECT cities.name AS city, urbanarea_pop, countries.name AS country,
       indep_year, languages.name AS language, percent
FROM cities
  LEFT JOIN countries
    ON cities.country_code = countries.code
  LEFT JOIN languages
    ON countries.code = languages.code
ORDER BY city, language;

-- 2) RIGHT JOIN 
SELECT cities.name AS city, urbanarea_pop, countries.name AS country,
       indep_year, languages.name AS language, percent
FROM languages
  RIGHT JOIN countries
    ON languages.code = countries.code
  RIGHT JOIN cities
    ON countries.code = cities.country_code
ORDER BY city, language;

-- CROSS JOIN does not need ON or USING
SELECT c.name AS city,
      l.name AS language
FROM cities AS c
  CROSS JOIN languages AS l
WHERE c.name LIKE 'Hyder%'

INNER JOIN vs LEFT JOIN : Number of outcome is different

INNER JOIN

LEFT JOIN

FULL JOIN

FULL JOIN

2. UNION Keyword

UNION and UNION ALL clauses do not do the lookup step that JOINs do. They simply stack records on top of each other from one table to the next

UNION	DESCRIPTION
UNION	Includes every record in both tables but DOES NOT double count those that are in both tables
UNION ALL	DOES double count
INTERSECT	Include ONLY Common Record between 2 tables Looks for records in common, not individual key fields like what a JOIN does to match(Important distinction b/w JOIN)
EXCEPT	Return the data in Table A but not in Table B
CASE WHEN THEN ELSE END AS	CASE multiple if-then-else statement

SELECT cities.country_code AS country_code
  FROM cities
    UNION
SELECT currencies.code
  FROM currencies
ORDER BY country_code
-- only country_code column show

-- UNION ALL 

SELECT economies.code, economies.year
  FROM economies
    UNION ALL
SELECT populations.country_code, populations.year    
  FROM populations
ORDER BY code, year

SELECT e.code, e.year
FROM economies AS e
INTERSECT
SELECT p.country_code, p.year
FROM populations AS p
ORDER BY code, year
-- e.code, e.year make column code & year, 
-- so just mention code, year

-- names of cities that are not 
-- listed in the capital cities table

SELECT c1.name
  FROM cities AS c1
  EXCEPT
SELECT c2.capital
  FROM countries AS c2
ORDER BY name

UNION

UNION ALL

INTERSECT

EXCEPT