Skip to main content

0504 | SQL Joins

SQL joins​

  • helpful when you need information from two different tables in a database
  • SQL needs to know which column we're referring to --> writing the name of the table first, then a period, and then the name of a column
    • employees.employee_id and machines.employee_id
  • NULL represents a missing value due to any reason

INNER JOIN​

  • returns rows matching on a specified column that exists in more than one table
  • example | SELECT username, office, operating_system FROM employees INNER JOIN machines ON employees.employee_id = machines.employee_id;
  • only returning records that share a value in specify columns

Types of joins​

  • in some situations, we might need all of the entries from one or both of our tables --> outer joins
  • same syntax as inner join | <JOIN-X> table ON employees.employee_id = machines.employee_id;

Types of outer joins​

  • LEFT JOIN | LEFT JOIN table ON ...
  • RIGHT JOIN
  • FULL OUTER JOIN

LEFT JOIN​

  • returns all of the records of the first table, but only returns rows of the second table that match on a specified column
  • rest is filled with NULL --> Records from the employees table that didn't match but were returned through the LEFT JOIN contain NULL values in columns that came from the machines table

RIGHT JOIN​

  • returns all of the records of the second table, but only returns rows from the first table that match on a specified column

FULL OUTER JOIN​

  • returns all recods from both tables
  • if a row doesn't have a value for a particular column, it returns NULL

Continous learning in SQL​

Aggregate functions​

  • functions that perform a calcualtion over multiple data points and return the result of the calculation
  • the actual data in not returned
  • COUNT | returns a single number that represents the number of rows returned from your query
  • AVG | returns a single number that represents the average of the numerical data in a column
  • SUM | returns a single number that represents the sum of the numerical data in a column | will return the total number of records, excluding NULL values

Aggregate function syntax​

  • after the SELECT keyword, and then in parentheses, indicate the column you want to perform the calculation on
  • example | SELECT COUNT(firstname) FROM customers;
    • result is a table with one column titled COUNT(firstname) and one row that indicates the count
  • example | SELECT COUNT(firstname) FROM customers WHERE country = 'USA';