SQL Table Joins

Overview

The SQL JOIN allows you to run a single SELECT statement that references multiple tables. This can be used for more advanced filtering, as well as combining data from multiple tables in a single result set. The process of joining two tables involves stating both tables in the query, and then specifying that a column from one table relates to another in some way.

Joins may look imposing at first, but they are simply SELECT statements that utilize columns from multiple tables.

The JOIN keyword works in conjunction with a SELECT statement. However, there are some key concepts that must be addressed when attempting to use the JOIN keyword.

Specifying Each Column

When listing column in a statement that uses the JOIN keyword, you must denote which table each column is being retrieved from. You can do this by using an Alias, or with the fully qualified column name. This prevents ambiguous columns in the context of the query, and makes it easier for you to use other keywords in the statement: i.e., adding WHERE clauses that apply to multiple tables.

Pseudocode - Fully Qualified Column Name

table_name.column_name

Declare the Relation

After the JOIN keyword, you must state which columns from each table relate to each other. This is accomplished by stating the name of the table, using the ON keyword, and then stating that a column on the first table is equal to a column on the second table. The columns specified are typically primary keys for their respective tables.

In the example below, we're stating that the values in some_column on tableA should be associated with matching values in some_other_column on tableB.

Pseudocode - Using the Join Keyword

FROM
    TableA
JOIN tableB ON tableA.some_column = tableB.some_other_column

The order you present the columns after the ON clause does not matter: A = B is equivalent to saying B = A, so we could switch the columns listed with no distinguishable impact on the resulting query

Combining the Concepts

Altogether, a basic JOIN looks like the following:

Pseudocode - Join Syntax

SELECT
    tableA.column,
    tableB.column
FROM
    tableA
JOIN tableB ON tableA.identity_column = tableB.identity_column

Joins with Three or More Tables

Joins can even be done between three or more tables. The syntax is similar, with each new table relation declared below the first.

Pseudocode - Join Syntax

SELECT
    tableA.column,
    tableB.column,
    tableC.column
FROM
    tableA
JOIN tableB ON tableA.identity_column = tableB.identity_column
JOIN tableC ON tableA.identity_column = tableC.identity_column

Join

The standard JOIN, also referred to as INNER JOIN, will only return rows where the joined columns contain matching values. If one of the joined columns contains a value that is not present in the in the other, then the row is not represented in the result set. You would use JOIN when you only want results that are represented in both tables.

This section will demonstrate the various uses of the JOIN keyword. For the sake of clarity, the queries will run against tables that look like the following:

Products Table

id	product_name
1	Apples
2	Oranges
3	Grapes
4	Plums

Inventory Table

id	product_id	quantity	product_vendor
1	1	15	Apple Corp
2	2	25	Orange Ya-Glad
3	3	56	Grape Escape
4	5	45	Banana Solutions

Join in Action

In this demonstration, only rows that pertain to Apples, Oranges, and Grapes are being returned. We're using a JOIN between products.id and inventory.product_id, so our results will only contain rows that have matching values from both of those columns. Our result set does not contain any information on products with products.id values of 4 or inventory.product_id values of 5, because those values are not present in both of the joined columns.

SQL - Joining Products and Inventory

SELECT
    products.id
    ,inventory.product_id
    ,products.product_name
    ,inventory.product_vendor
    ,inventory.quantity
FROM
    products
JOIN inventory ON products.id = inventory.product_id

Example Results

id	product_id	product_name	product_vendor	quantity
1	1	Apples	Apple Corp	15
2	2	Orange	Orange Ya-Glad	25
3	3	Grapes	Grape Escape	56

Left Join

Return all rows from the left-most table (table A in the diagram), even if there are no matches on the right-most table (table B). If there isn't a matching record in the right table, then NULL values are returned.

Left Join in Action

Here we see all rows returned from our products table (since it is the left-most table in our query). In row 4, columns that are being populated via the inventory table (product_id, product_vendor, and quantity) contain NULL values, because there isn't a row on the inventory table that matches with a product_id value of 4. The query must return something in this case, so it returns NULL for these columns.

SQL - Left Joining Products and Inventory

SELECT
    products.id
    ,inventory.product_id
    ,products.product_name
    ,inventory.product_vendor
    ,inventory.quantity
FROM
    products
LEFT JOIN inventory ON products.id = inventory.product_id

Example Results

id	product_id	product_name	product_vendor	quantity
1	1	Apples	Apple Corp	15
2	2	Orange	Orange Ya-Glad	25
3	3	Grapes	Grape Escape	56
4	NULL	Plums	NULL	NULL

Right Join

Return all rows from the right-most table (table B), even if there are no matches on the left-most table (table A). If there isn't a matching record on the left table, then NULL values are returned.

Right Join in Action

When using a RIGHT JOIN, all rows will be returned from the inventory table. The products table does not have a row that contains an id value of 5, so the id and product_name columns will show NULL values in our result set.

SQL - Right Joining Products and Inventory

SELECT
    products.id
    ,inventory.product_id
    ,products.product_name
    ,inventory.product_vendor
    ,inventory.quantity
FROM
    products
RIGHT JOIN inventory ON products.id = inventory.product_id

Example Results

id	product_id	product_name	product_vendor	quantity
1	1	Apples	Apple Corp	15
2	2	Orange	Orange Ya-Glad	25
3	3	Grapes	Grape Escape	56
NULL	5	NULL	Banana Solutions	45

Full Join

The FULL JOIN returns all rows from both tables, regardless if there are matching values in the joined columns. You would use a FULL JOIN in cases where you want to show all applicable records from both tables, and synchronize the data across both tables via the joining columns where possible.

Full Join in Action

Note that we're using the same query as the standard JOIN, but we've prepended "FULL" to the last line of the query. Note the NULL values returned in cases where our product ID is not fully represented on both tables.

SQL - Full Joining Products and Inventory

SELECT
    products.id
    ,inventory.product_id
    ,products.product_name
    ,inventory.product_vendor
    ,inventory.quantity
FROM
    products
FULL JOIN inventory ON products.id = inventory.product_id

Example Results

id	product_id	product_name	product_vendor	quantity
1	1	Apples	Apple Corp	15
2	2	Orange	Orange Ya-Glad	25
3	3	Grapes	Grape Escape	56
4	NULL	Plums	NULL	NULL
NULL	5	NULL	Banana Solutions	45

Full Joins in MySQL

MySQL does not have an equivalent FULL JOIN. However, you can emulate one by utilizing a LEFT JOIN, RIGHT JOIN, and the UNION keyword. For the sake of simplicity, we will return all columns in the following example, but you would still want to specify individual columns in both SELECT query.

SQL - Full Join in MySQL using Left Join, Right Join, and Union Keyword

SELECT * FROM products
LEFT JOIN inventory ON products.id = inventory.product_id
 
UNION ALL
 
SELECT * FROM products
RIGHT JOIN inventory ON products.id = inventory.product_id
WHERE products.id IS NULL