Joining Data with SQL

Here you can access every table used in the course. To access each table, you will need to specify the world schema in your queries (e.g., world.countries for the countries table, and world.languages for the languages table).

Take Notes

Add notes about the concepts you've learned and SQL cells with queries you want to keep.

Add your notes here

INNER JOIN ORDER OF EXECUTION

• FROM
• INNER JOIN
• ON
• SELECT

WE can use ON or USING keyword for Joining.

-- Select name fields (with alias) and region SELECT cities.name AS city, countries.name AS country, countries.region AS region FROM cities INNER JOIN countries ON cities.country_code = countries.code;

JOINS:

1. INNER JOIN
2. OUTER JOIN:
   1. LEFT JOIN / LEFT OUTER JOIN
   2. RIGHT JOIN / RIGHT OUTER JOIN
   3. FULL JOIN / FULL OUTER JOIN--> combination of LEFT JOIN & RIGHT JOIN.
3. CROSS JOIN LEFT JOIN can be written as LEFT OUTER JOIN in SQL.

ALL JOIN WITH SAME RESULT: SELECT c.name AS country, region, life_expectancy AS life_exp FROM countries AS c -- Join to populations (alias as p) using an appropriate join LEFT JOIN populations as p ON c.code = p.country_code -- Filter for only results in the year 2010 WHERE year = 2010 -- Sort by life_exp ORDER BY life_exp -- Limit to five records LIMIT 5;

SET THEORY

• UNION(without duplicates)
• UNION ALL(with duplicates)
• INTERSECT (ONLY record common to both tables but not duplicates) V/s INNER JOIN (will return common from both the table but duplicates)
• EXCEPT (Allows to identify the records that are present in one table but not in the other table)

SUB QUERING WITH SEMI JOINS AND ANTI JOINS SEMI JOIN SELECT DISTINCT name FROM languages -- Add syntax to use bracketed subquery below as a filter Where code IN (SELECT code FROM countries WHERE region = 'Middle East') ORDER BY name;

ANTI JOIN SELECT code, name FROM countries WHERE continent = 'Oceania' -- Filter for countries not included in the bracketed subquery AND code NOT IN (SELECT code FROM currencies);

SUBQUERY INSIDE WHERE CLAUSE:

1. -- Select average life_expectancy from the populations table SELECT AVG(life_expectancy) FROM populations -- Filter for the year 2015 Where year = 2015;

2. SELECT * FROM populations -- Filter for only those populations where life expectancy is 1.15 times higher than average WHERE life_expectancy > 1.15 * (SELECT AVG(life_expectancy) FROM populations WHERE year = 2015) AND year = 2015;

3. -- Select relevant fields from cities table SELECT name, country_code, urbanarea_pop FROM cities -- Filter using a subquery on the countries table Where name IN (SELECT capital FROM countries) ORDER BY urbanarea_pop DESC;

Subquery inside SELECT

1. -- Find top nine countries with the most cities SELECT countries.name AS country, COUNT(cities.name) AS cities_num FROM countries LEFT JOIN cities ON countries.code = cities.country_code -- Order by count of cities as cities_num GROUP BY countries.name ORDER BY cities_num DESC, country ASC LIMIT 9;

2. SELECT countries.name AS country, -- Subquery that provides the count of cities (SELECT COUNT(cities.name) FROM cities WHERE countries.code = cities.country_code) AS cities_num FROM countries ORDER BY cities_num DESC, country LIMIT 9;

Subquery inside FROM

-- Select code, and language count as lang_num SELECT languages.code AS code, COUNT(languages.name) AS lang_num FROM languages GROUP BY languages.code;

-- Select local_name and lang_num from appropriate tables SELECT local_name, sub.lang_num FROM countries, (SELECT code, COUNT(*) AS lang_num FROM languages GROUP BY code) AS sub -- Where codes match WHERE countries.code = sub.code ORDER BY lang_num DESC;

-- Select relevant fields SELECT code, inflation_rate, unemployment_rate FROM economies WHERE year = 2015 AND code NOT IN -- Subquery returning country codes filtered on gov_form (SELECT code FROM countries WHERE gov_form LIKE '%Republic%' OR gov_form LIKE '%Monarchy%') ORDER BY inflation_rate;

-- Select fields from cities SELECT name, country_code, city_proper_pop, metroarea_pop, ((city_proper_pop/metroarea_pop)*100) AS city_perc FROM cities -- Use subquery to filter city name WHERE name IN (SELECT capital FROM countries WHERE continent = 'Europe' OR continent LIKE '%America%') -- Add filter condition such that metroarea_pop does not have null values AND metroarea_pop IS NOT NULL -- Sort and limit the result ORDER BY city_perc DESC limit 10;