Intro of SQL | #2 JOIN

이지수·2022년 2월 28일
0

SQL

목록 보기
3/3

1. JOIN Keyword

KEYWORD - JOIN DESCRIPTION
INNER JOINUsing only matched key field (duplicated column between X & Y)
FROM X
INNER JOIN Y
SELF JOINJoining 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 JOINReturns 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 JOINCan always write a right join as a left join
FULL OUTER JOINReturns all records
CROSS JOINMake 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
INNER JOIN
LEFT JOIN
LEFT JOIN
FULL JOIN
FULL JOIN
CROSS 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
UNIONIncludes every record in both tables but DOES NOT double count those that are in both tables
UNION ALLDOES double count
INTERSECTInclude 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)
EXCEPTReturn the data in Table A but not in Table B
CASE WHEN THEN ELSE END ASCASE 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
UNION ALL
UNION ALL
INTERSECT
INTERSECT
EXCEPT
EXCEPT


0개의 댓글