Skip to content
Joining Data in SQL
  • AI Chat
  • Code
  • Report
  • 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 only include records in which key is in both tables
      • INNER JOIN keeps all of the fields in both tables
      • FROM table on left ON to the table right
      • Multiple joins in a single query e.g:
        • 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;
      • add multiple joining conditions using AND. (prevents incorrect pairing if fields need to be matched on multiple values) see below:
    • tables with the same key can be joined with the USING ()
    • SELF JOINS (using INNER JOIN) are used to compare values in a fields to other values in the same fields from the same table.
    • AND can be used to add multiple condition to prevent same values from matching to them selves.
      • Extend the ON in your query to include only those records where the p1.year (2010) matches with p2.year - 5 (2015 - 5 = 2010). This will omit the three entries per country_code that you aren't interested in.

    CASE statements to catagorize data: CASE WHEN field THEN '' WHEN field THEN '' ELSE '' END

    Create table using INTO command

    Unknown integration
    DataFrameavailable as
    df
    variable
    -- Corrected code
    CREATE TABLE world.pop_plus AS
    SELECT country_code, size,
        CASE WHEN size > 50000000 THEN 'large'
            WHEN size > 1000000 THEN 'medium'
            ELSE 'small' END
            AS popsize_group
    FROM world.populations
    WHERE year = 2015;
    
    -- Select all columns of pop_plus
    SELECT *
    FROM world.pop_plus;
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

    OUTER JOINS LEFT JOIN- Keeps all the records from the left table and marks the values as missing in the right table that do not have a match.

    • Duplicate rows can be shown if there are multiple values on the right table that match the key in the left (joining) table RIGHT JOIN sames as LEFT JOIN but in reverse
    • In SQL the right table appears after RIGHT JOIN and the left table appears after FROM. FULL JOINS-all records from both the left and the right table and keep track of the missing values accordingly.

    SEE BELOW-- Convert this code to use RIGHT JOINs instead of LEFT JOINs /*

    • 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;