Sign in Subscribe
Data Analitics Study Notes

Explore how SQL JOINs work

Aleksandr Kosenko

Aleksandr Kosenko

— 4 min read
Explore how SQL JOINs work

Common JOINs

  • INNER JOIN: a function that returns records with matching values in both tables
  • LEFT JOIN: a function that returns all the records from the left table (first mentioned) and only the matching records from the right table (second mentioned)
  • RIGHT JOIN: a function that returns all records from the right table (second mentioned) and only the matching records from the left table (first mentioned).
  • OUTER JOIN: a function that combines the RIGHT JOIN and LEFT JOIN to return all matching records in both tables.

Example 1: INNER JOIN

SELECT
	employees.name AS employee_name,
	employees.role AS employee_role,
	departments.name AS department_name
FROM
	employee_data.employees AS employees
INNER JOIN
	employee_data.departments AS departments
	ON employees.department_id = departments.department_id

Example 2: LEFT JOIN

SELECT
	employees.name AS employee_name,
	employees.role AS employee_role,
	departments.name AS department_name
FROM
	employee_data.employees AS employees 
LEFT JOIN
	employee_data.departments AS departments
    ON employees.department_id = departments.department_id

Example 3: RIGHT JOIN

SELECT
	employees.name AS employee_name,
	employees.role AS employee_role,
	departments.name AS department_name
FROM
	[your-project-id].employee_data.employees AS employees 
RIGHT JOIN
	[your-project-id].employee_data.departments AS departments
    ON employees.department_id = departments.department_id

Example 4: FULL OUTER JOIN

Now, you’ll create a new query that uses FULL OUTER JOIN.

  1. Using the query window tabs to navigate between queries, copy and paste the query from Example 3 into the new query window.
  2. In line 7, replace RIGHT JOIN with FULL OUTER JOIN.
  3. Run the query by selecting the Run button.

If you prefer, you can copy and paste the following query into the query window in BigQuery.

SELECT
	employees.name AS employee_name,
	employees.role AS employee_role,
	departments.name AS department_name
FROM
	[your-project-id].employee_data.employees AS employees 
OUTER JOIN
	[your-project-id].employee_data.departments AS departments
      ON employees.department_id = departments.department_id

Type of JOINs

img by Google

For more information

JOINs are going to be useful for working with relational databases and SQL—and you will have plenty of opportunities to practice them on your own. Here are a few other resources that can give you more information about JOINs and how to use them:

  • SQL JOINs: This is a good basic explanation of JOINs with examples. If you need a quick reminder of what the different JOINs do, this is a great resource to bookmark and come back to later.
  • Database JOINs - Introduction to JOIN Types and Concepts: This is a really thorough introduction to JOINs. Not only does this article explain what JOINs are and how to use them, but it also explains the various scenarios in more detail of when and why you would use the different JOINs. This is a great resource if you are interested in learning more about the logic behind JOINing.
  • SQL JOIN Types Explained in Visuals: This resource has a visual representation of the different JOINs. This is a really useful way to think about JOINs if you are a visual learner, and it can be a really useful way to remember the different JOINs.
  • SQL JOINs: Bringing Data Together One Join at a Time: Not only does this resource have a detailed explanation of JOINs with examples, but it also provides example data that you can use to follow along with their step-by-step guide. This is a useful way to practice JOINs with some real data.
  • SQL JOIN: This is another resource that provides a clear explanation of JOINs and uses examples to demonstrate how they work. The examples also combine JOINs with aliasing. This is a great opportunity to see how JOINs can be combined with other SQL concepts that you have been learning about in this course.

Key takeaways

  • JOIN is a SQL clause used to combine rows from two or more tables based on a related column.
  • There are four common JOINs used by data analysts: inner, left, right, and outer.
  • An inner JOIN returns records with matching values in both tables.
  • A left JOIN returns all records from the left table and the matching records from the right table.
  • A right JOIN does the opposite, returning all records from the right table and the matching records from the left table.
  • An outer JOIN combines right and left JOINs to return all matching records in both tables.
  • JOINs can make working with multiple data sources easier and can clarify relationships between tables.

P.S.

Basic syntax for aliasing

Aliasing is the process of using aliases. In SQL queries, aliases are implemented by making use of the AS command. The basic syntax for the AS command can be seen in the following query for aliasing a table:

SELECT column_name(s)
FROM table_name AS alias_name;

For more information

If you are interested in learning more about aliasing, here are some resources to help you get started:

  • SQL Aliases: This tutorial on aliasing is a really useful resource to have when you start practicing writing queries and aliasing tables on your own. It also demonstrates how aliasing works with real tables.
  • SQL Alias: This detailed introduction to aliasing includes multiple examples. This is another great resource to reference if you need more examples.
  • Using Column Aliasing: This is a guide that focuses on column aliasing specifically. Generally, you will be aliasing entire tables, but if you find yourself needing to alias just a column, this is a great resource to have bookmarked.

Read more